Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I Edit Fill Down a Cell Reference to Multiple Worksheets
I have one main worksheet that summarizes the calculations on many other worksheets. I want to have a column that will Edit, Fill, Down like the following example (z1 always stays the same no matter what the worksheet): =a!z1 =b!z1 =c!z1 But all I get when I do an Edit, Fill, Down is: =a!z1 =a!z2 =a!z3 I went through the reference help and 3D help, but nothing seemed to work. If I must have a hidden column with the worksheet names, and somehow reference the name in the formula, that is OK. I couldn't figure that one out either. -- langba ------------------------------------------------------------------------ langba's Profile: http://www.excelforum.com/member.php...fo&userid=4144 View this thread: http://www.excelforum.com/showthread...hreadid=474093 |
#2
|
|||
|
|||
Hi!
If your sheet names really are A, B, C etc. =INDIRECT(CHAR(ROW(A97))&"!Z1") Copy down as needed. You can use this for up to 26 sheets as the alphabet runs out after: =INDIRECT(CHAR(ROW(A122))&"!Z1") If your sheet names are something else then you would need to list those names in a range, say, A1:An, then: =INDIRECT(A1&"!Z1") Copy down as needed. Biff "langba" wrote in message ... I have one main worksheet that summarizes the calculations on many other worksheets. I want to have a column that will Edit, Fill, Down like the following example (z1 always stays the same no matter what the worksheet): =a!z1 =b!z1 =c!z1 But all I get when I do an Edit, Fill, Down is: =a!z1 =a!z2 =a!z3 I went through the reference help and 3D help, but nothing seemed to work. If I must have a hidden column with the worksheet names, and somehow reference the name in the formula, that is OK. I couldn't figure that one out either. -- langba ------------------------------------------------------------------------ langba's Profile: http://www.excelforum.com/member.php...fo&userid=4144 View this thread: http://www.excelforum.com/showthread...hreadid=474093 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Problem with formulas changing cell reference | Excel Discussion (Misc queries) | |||
how to create a variable column in cell reference | Excel Worksheet Functions | |||
dynamic external cell reference | Excel Worksheet Functions | |||
Cell Reference Math | Excel Worksheet Functions |