I have a workbook with several sheets, each sequentally numbered: 001, 002, 003, etc
I have another series of sheets called 001report, 002report, 003report,… that refers to cells on it’s “parent”. I have the name of each parent in a cell on each report sheet, formatted as a number, and want to be able to refer to that cell to obtain the name of the parent in a formula.
I can’t make this work. As of now I have to edit each formula by hand (or global search and replace) to refer to the correct parent sheet.
Is there some way to include a relative reference to a sheet name in a formula?
I need to run this sheet on excel on a PC, Mac, and in OpenOffice. I can’t use Macros due to security rules.
indirect("’" & [cell with sheet name] & “’!” & [cell on sheet name you are referring to and/or formula])
if cell A1 on sheet 001report is text format and contains 001 for the sheet name, and you want the value of cell Z1 on sheet 001, you would write effectively
I would add to Bobalude that you could also incorporate the address function with the row and column functions so that you can have formulas that you can slide around and what not.
For example, if you put 001 formatted as text in cell A1 on the report001 sheet, you could the put following forumla into cell C5:
This will give you the number that’s in C5 on 001, and you can slide it down and across to autofill the cells next to it so that they refer to D5 or C6 or whatever on 001.
Obviously, you can also incorporate things like offsets, averageifs, sums, or whatever elese into the format to create whatever you need.
Also, when doing a lot of Find and Replace - especially when moving all of the formulas on one sheet to another - or, even more so, from one file to another - it is often really helpful to first find and replace all = with &&. This will break all of your formulas, letting you copy and move them and whatever without them trying to reference other cells or do weird stuff. You can then use the F&R on the broken cells to swap out whatever references you want to, and then use F&R to replace all of the && with = and recreate the formulas, all nice and clean like.
I use && because that really should never be a functioning part of a formula, if it is for some reason then just use some stranger string like &*^%^ or whatever.
Be warned, however, that if you screwed up a reference to another file and put it into, say 2,500 cells - you will be clicking OK 2,500 times as Excel looks for and fails to find the file that doesn’t exist. So, maybe just change 1 && back to a = first, and then the other 2,499
