Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unknown Sheets
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. -- Jake |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unknown Sheets
Create 2 dummy sheets then insert any new sheets in-between these 2 sheets
=SUM(First:Last!A3) -- Regards, Peo Sjoblom "JakeShipley2008" wrote in message ... 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. -- Jake |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unknown Sheets
"JakeShipley2008" wrote...
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(alpha:omega!X99) Just be sure to add or remove worksheets between alpha and omega. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unknown Sheets
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. -- Jake "Harlan Grove" wrote: "JakeShipley2008" wrote... 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(alpha:omega!X99) Just be sure to add or remove worksheets between alpha and omega. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unknown Sheets
"JakeShipley2008" wrote...
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unknown Sheets
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. -- Jake "Harlan Grove" wrote: "JakeShipley2008" wrote... 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unknown Sheets
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 On Mon, 25 Jun 2007 12:41:07 -0700, JakeShipley2008 wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unknown Formula | Excel Discussion (Misc queries) | |||
Unknown Rows | Excel Discussion (Misc queries) | |||
Unknown Error | Excel Discussion (Misc queries) | |||
Unknown Characters | Excel Discussion (Misc queries) | |||
Unknown Object | Charts and Charting in Excel |