If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Linking to other worksheets
I have 12 worksheets (Jan - Dec) each containing data for each month. I also
have a summary sheet that i would like to link to the monthly data. On the summary sheet my months are listed vertically A5 (Jan) - A16 (Dec) with sales data to go in B5-B16. Formula for B5 is =Jan!$B$35, B6 is =Feb!$B$35 and so on. How can I set it up so i don't have to manually type in the worksheet reference every time? For example it won't allow to reference the worksheet name from a cell: =A5!$B$35 |
#2
|
|||
|
|||
Linking to other worksheets
If the worksheet names are in cell A5 and below, then you can do this:
=INDIRECT("'"&A5&"'!B"&ROW(A35)) Then you can copy down and the ROW(A35) bit will become ROW(A36), ROW(A37) etc, returning 35, 36, 37 and so on in turn. Hope this helps. Pete On Apr 18, 1:26*am, Cav wrote: I have 12 worksheets (Jan - Dec) each containing data for each month. I also have a summary sheet that i would like to link to the monthly data. On the summary sheet my months are listed vertically A5 (Jan) - A16 (Dec) with sales data to go in B5-B16. Formula for B5 is =Jan!$B$35, B6 is =Feb!$B$35 and so on. How can I set it up so i don't have to manually type in the worksheet reference every time? For example it won't allow to reference the worksheet name from a cell: *=A5!$B$35 |
#3
|
|||
|
|||
Linking to other worksheets
How can I set it up so i don't have to manually type in the worksheet
reference every time? Why are typing the Worksheet name manually? Use you mouse pointer and let Excel do the typing and fill down. You could use INDIRECT to reference the sheet name in a cell, BUT then all your references will be Volatile. -- Regards Dave Hawley www.ozgrid.com "Cav" wrote in message ... I have 12 worksheets (Jan - Dec) each containing data for each month. I also have a summary sheet that i would like to link to the monthly data. On the summary sheet my months are listed vertically A5 (Jan) - A16 (Dec) with sales data to go in B5-B16. Formula for B5 is =Jan!$B$35, B6 is =Feb!$B$35 and so on. How can I set it up so i don't have to manually type in the worksheet reference every time? For example it won't allow to reference the worksheet name from a cell: =A5!$B$35 |
#4
|
|||
|
|||
Linking to other worksheets
Hi,
Try this in cell B5 and copy down till B16 =indirect(A5&"!$B$35") -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Cav" wrote in message ... I have 12 worksheets (Jan - Dec) each containing data for each month. I also have a summary sheet that i would like to link to the monthly data. On the summary sheet my months are listed vertically A5 (Jan) - A16 (Dec) with sales data to go in B5-B16. Formula for B5 is =Jan!$B$35, B6 is =Feb!$B$35 and so on. How can I set it up so i don't have to manually type in the worksheet reference every time? For example it won't allow to reference the worksheet name from a cell: =A5!$B$35 |
Thread Tools | |
Display Modes | |
|
|