Unknown Sheets

[Guest]

Hi, I am trying to find out if there is a function I can use in a summary
sheet that will pull data from an unknown sheet name. In other words I would
like to have the summary sheet in place and as I add new worksheets it will
pull data from them without having to go back and redo the formula.

 

[Peo Sjoblom]

Create 2 dummy sheets then insert any new sheets in-between these 2 sheets

=SUM(First:Last!A3)

 

[Harlan Grove]

Hi, I am trying to find out if there is a function I can use in a summary
sheet that will pull data from an unknown sheet name. In other words I
would
like to have the summary sheet in place and as I add new worksheets it will
pull data from them without having to go back and redo the formula.

Not enough details.

Do you mean pull data from a single arbitrary worksheet? If so, and if the
worksheet name were entered in a cell named Other, and you wanted to pull
the X99 value in that worksheet, try

=INDIRECT("'"&Other&"'!X99")

If you mean you want to sum the X99 values in all other worksheets among
with you'd be adding (and possibly removing) arbitrarily many, the standard
approach is to bracket your data-filled worksheets between BLANK worksheets
(I use alpha and omega as the names for the blank worksheets), then sum all
worksheets between the blank worksheets,

=SUM(alphamega!X99)

Just be sure to add or remove worksheets between alpha and omega.

 

[Guest]

To explain better, I have a macro written that imports new sheets to my
active workbook. I do not know the names of the new sheets until the files
are imported.
I want to move the data with a simple ="sheet1!A1" function howver since I
do not know the sheet name I am unsure how to do this.

 

[Harlan Grove]

To explain better, I have a macro written that imports new sheets to my
active workbook. I do not know the names of the new sheets until the files
are imported.
I want to move the data with a simple ="sheet1!A1" function howver since I
do not know the sheet name I am unsure how to do this.

....

Couldn't the macro that imports these worksheets also enter formulas? When
you import a worksheet, it becomes the ActiveSheet in that workbook. You
could get its name using

wksname = ActiveSheet.Name

then copy template formulas and replace the template worksheet name with the
new worksheet's name.

 

[Guest]

I am sorry I am not very good at this macro thing. I did write one that will
pull all the active sheet names into one workbook. I don't understand how to
use those sheet names now to extract data from specific cells.

 

[Gord Dibben]

Once you have the sheet names in a list on a worksheet in column A, say A1:A10

Use the INDIRECT function to address those sheets and a cell address.

=INDIRECT(A1 & "!E3") entered in B1 and copied down.


Gord Dibben MS Excel MVP