Home |
Search |
Today's Posts |
|
#1
![]() |
|||
|
|||
![]() I am trying to create a spreadsheet that collects data from a number of similarly formatted worksheets. Since I dont want to have to manually enter each sheet name into the main collection worksheet formulas I would like the formulas in each column to reference data from the worksheet bearing the name of that formulas column header. For instance, in a column labeled 03 I want to reference a worksheet named 03 in this formula but if I copy this formula across to multiple rows I need to edit each row and change the 03 to 02, 01 etc I know I can edit and replace but my main worksheet collects data from many worksheets. =SUMPRODUCT(--(MONTH('03'!$D$6:$D$76)=1),'03'!$J$6:$J$76) -- JimDandy ------------------------------------------------------------------------ JimDandy's Profile: http://www.excelforum.com/member.php...o&userid=16578 View this thread: http://www.excelforum.com/showthread...hreadid=314401 |
#2
![]() |
|||
|
|||
![]()
Hi
have a look at the INDIRECT function for this "JimDandy" wrote: I am trying to create a spreadsheet that collects data from a number of similarly formatted worksheets. Since I dont want to have to manually enter each sheet name into the main collection worksheet formulas I would like the formulas in each column to reference data from the worksheet bearing the name of that formulas column header. For instance, in a column labeled 03 I want to reference a worksheet named 03 in this formula but if I copy this formula across to multiple rows I need to edit each row and change the 03 to 02, 01 etc¦I know I can edit and replace but my main worksheet collects data from many worksheets. =SUMPRODUCT(--(MONTH('03'!$D$6:$D$76)=1),'03'!$J$6:$J$76) -- JimDandy ------------------------------------------------------------------------ JimDandy's Profile: http://www.excelforum.com/member.php...o&userid=16578 View this thread: http://www.excelforum.com/showthread...hreadid=314401 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Insert rows based on specific value | Excel Worksheet Functions | |||
incrementing formula reference by 7 | Excel Discussion (Misc queries) | |||
copying a formula, the reference adjusts, but the result does not | Excel Discussion (Misc queries) | |||
what formula do i put for column m = column k minus column l in e. | Excel Discussion (Misc queries) | |||
I there an easy way to find out if any formula reference a cell? | New Users to Excel |