Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet Referencing - autofilling sheet names
Is there a way to easily reference sheet names.
Here is my problem: i have several sheets named 01,02,03,04... and so on, I have a summary sheet that i would like to reference all the individual sheets. I start at the top and refernce sheet 01, then i would like to drag down so that the next row is referencing sheet 02 and so on. This does not happen when i drag the cell down. Is there any way to achieve this. I tried typing out the cell names in another column and referencing that cell inside the ' ', but that didn't seem to work. Thanks, Pat |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet Referencing - autofilling sheet names
Say your sheet name list starts in A2:
01 02 03 etc... And you want to return the contents of D1 from each sheet. Enter this in B2, and copy down: =INDIRECT("'"&A2&"'!D1") *NOTE* - Your list in Column A should be TEXT, *NOT* numbers formatted to display the leading zero! -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Pat" wrote in message ... Is there a way to easily reference sheet names. Here is my problem: i have several sheets named 01,02,03,04... and so on, I have a summary sheet that i would like to reference all the individual sheets. I start at the top and refernce sheet 01, then i would like to drag down so that the next row is referencing sheet 02 and so on. This does not happen when i drag the cell down. Is there any way to achieve this. I tried typing out the cell names in another column and referencing that cell inside the ' ', but that didn't seem to work. Thanks, Pat |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet Referencing - autofilling sheet names
Hi,
Try this. Press Ctrl+F3 and click on New. In the name box, type wkst_names and in the refers to box, type =GET.WORKBOOK(1)&T(NOW()). Now in cell C4, type =MID(INDEX(wkst_names,ROW()-ROW($C$3)+1),SEARCH("]",INDEX(wkst_names,ROW()-ROW($C$3)+1),1)+1,10). you may now copy this formula down In the formula, please change the reference of the row function to one row less than the cell in which you are pasting the formula. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Pat" wrote in message ... Is there a way to easily reference sheet names. Here is my problem: i have several sheets named 01,02,03,04... and so on, I have a summary sheet that i would like to reference all the individual sheets. I start at the top and refernce sheet 01, then i would like to drag down so that the next row is referencing sheet 02 and so on. This does not happen when i drag the cell down. Is there any way to achieve this. I tried typing out the cell names in another column and referencing that cell inside the ' ', but that didn't seem to work. Thanks, Pat |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofilling Sheet Name in Formula | Excel Discussion (Misc queries) | |||
formula referencing a column - autofilling in a row ??? | Excel Discussion (Misc queries) | |||
Referencing Sheet Names | New Users to Excel | |||
copy formula referencing sheet name to another sheet | Excel Worksheet Functions | |||
referencing a sheet named in a cell then using data from that sheet | Excel Worksheet Functions |