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
|
|||
|
|||
Using a Text Cell to Reference a Worksheet Name
I have one summary worksheet and approximately 45 data worksheets. Each row
on the summary page needs 8 - 10 cells from each data worksheet. The data worksheets are identical in format. I add about 4 new data worksheets a month. I am tired of the tedious formula entry for each new worksheet, and would like to have a way where i can enter the name of the new worksheet on the summary page and all of the formulas on that row use the entered text name to reference the appropriate worksheet and cell. Alas -- I have been unable to easily do this. I am still a relatively junior excel user. Any ideas? |
#2
|
|||
|
|||
Using a Text Cell to Reference a Worksheet Name
.. a way where i can enter the name of the new worksheet
on the summary page and all of the formulas on that row use the entered text name to reference the appropriate worksheet and cell... One way would be via using INDIRECT Perhaps a simple example to lead us in here In Sheet1, Suppose we list sheetnames in B1:C1, eg:Sheet2, Sheet3 and we have the cell refs listed in A2:A3, eg: B2, E2 Then, if we put in B2: =INDIRECT("'" & B$1 & "'!" & $A2) and copy B2 across & down to C3 .. B2:C2 will return the same as the link formulas: =Sheet2!B2, =Sheet3!B2 B3:C3 will return the same as the link formulas: =Sheet2!E2, =Sheet3!E2 INDIRECT will resolve the concatenation of the sheetname and cell ref text strings to return the results from the particular sheet and cell listed in B1:C1, and in A2:A3. So we could define / change the text strings to suit the purpose. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Jay L" Jay wrote in message ... I have one summary worksheet and approximately 45 data worksheets. Each row on the summary page needs 8 - 10 cells from each data worksheet. The data worksheets are identical in format. I add about 4 new data worksheets a month. I am tired of the tedious formula entry for each new worksheet, and would like to have a way where i can enter the name of the new worksheet on the summary page and all of the formulas on that row use the entered text name to reference the appropriate worksheet and cell. Alas -- I have been unable to easily do this. I am still a relatively junior excel user. Any ideas? |
#4
|
|||
|
|||
Using a Text Cell to Reference a Worksheet Name
I don't have a lot of time right now,but the indirect formula is what you are looking for. You can concatenate the cell reference together so that it will do just what you are looking for. I've gotta go watch 24 now - I'll check back later on - Chad -- cvolkert ------------------------------------------------------------------------ cvolkert's Profile: http://www.excelforum.com/member.php...o&userid=24380 View this thread: http://www.excelforum.com/showthread...hreadid=517078 |
#5
|
|||
|
|||
Using a Text Cell to Reference a Worksheet Name
Your reply to Jay really helped me out, but I have an 'add on' to his
original question: How can I use the INDIRECT formula to return the sum of specific cells in a data worksheet. My summary worksheet has a cell that requires the sum of the same 4 cells in each corresponding data worksheet. Any tips on modifying to make it work? Thanks much, M. Moncrief "CLR" wrote: As an Example.....assume your data goes in columns B.....K in cell A1 enter a sheet name In B1 put this =INDIRECT($A1&"!a1") in C1 put this =INDIRECT($A1&"!B1")...etc etc to get all 10 cells across Row 1 then in A2 put a new sheet name, and just copy and paste all 10 formulas down to row 2 hth Vaya con Dios, Chuck, CABGx3 "Jay L" Jay wrote in message ... I have one summary worksheet and approximately 45 data worksheets. Each row on the summary page needs 8 - 10 cells from each data worksheet. The data worksheets are identical in format. I add about 4 new data worksheets a month. I am tired of the tedious formula entry for each new worksheet, and would like to have a way where i can enter the name of the new worksheet on the summary page and all of the formulas on that row use the entered text name to reference the appropriate worksheet and cell. Alas -- I have been unable to easily do this. I am still a relatively junior excel user. Any ideas? |
#6
|
|||
|
|||
Using a Text Cell to Reference a Worksheet Name
In your summary sheet,
Assuming the sheetnames are listed in A2 down, eg: Sheet2, Sheet3, etc, with the ranges to be summed listed in B1 across, eg: A2:A10, B2:B100, etc you could place this in B2: =SUM(INDIRECT("'"&$A2&"'!"&B$1)) then simply copy across and fill down to populate the table -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "M.Moncrief" wrote: How can I use the INDIRECT formula to return the sum of specific cells in a data worksheet. My summary worksheet has a cell that requires the sum of the same 4 cells in each corresponding data worksheet. Any tips on modifying to make it work? |
#7
|
|||
|
|||
Using a Text Cell to Reference a Worksheet Name
Max,
can you (also) help me out on this one: I also have a summary sheet, called: Graphs, but consisting of graphs. For a (particular) graph, the [chart data range] is: ='Sheet100'!$F$6:$F$10 The value: "Sheet100" I would like to choose/vary; - preferably by selecting a cell with the name of the sheet I want the graph(s) made from (e.g. A1=Sheet100; A2=Sheet200 aso. By seecting Cell A2 (on sheet: Graphs) the graphs will be build using the data on Sheet200 . . . , or - by typing the name of a worksheet in a (fixed) cell (e.g. typing: Sheet200 in Cell A1) Note: all the worksheets have the same columns (but may vary in number of rows, although this could be also made the same). Hope you understand my question ! Regards, Paul "Max" wrote: In your summary sheet, Assuming the sheetnames are listed in A2 down, eg: Sheet2, Sheet3, etc, with the ranges to be summed listed in B1 across, eg: A2:A10, B2:B100, etc you could place this in B2: =SUM(INDIRECT("'"&$A2&"'!"&B$1)) then simply copy across and fill down to populate the table -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "M.Moncrief" wrote: How can I use the INDIRECT formula to return the sum of specific cells in a data worksheet. My summary worksheet has a cell that requires the sum of the same 4 cells in each corresponding data worksheet. Any tips on modifying to make it work? |
#8
|
|||
|
|||
Using a Text Cell to Reference a Worksheet Name
Hi
One way With a list of your sheet names in A1:A20 of sheet Graphs Create a named range called GraphRange with a value of =INDIRECT(INDEX(Graphs!$A$1:$A$20,Graphs!$B$1)&"!$ F$6:$F$10") Use GraphRange as the source for your graph data. Enter the row number of the sheet you want in cell B1, and the source data for the graph will change accordingly -- Regards Roger Govier PvZ wrote: Max, can you (also) help me out on this one: I also have a summary sheet, called: Graphs, but consisting of graphs. For a (particular) graph, the [chart data range] is: ='Sheet100'!$F$6:$F$10 The value: "Sheet100" I would like to choose/vary; - preferably by selecting a cell with the name of the sheet I want the graph(s) made from (e.g. A1=Sheet100; A2=Sheet200 aso. By seecting Cell A2 (on sheet: Graphs) the graphs will be build using the data on Sheet200 . . . , or - by typing the name of a worksheet in a (fixed) cell (e.g. typing: Sheet200 in Cell A1) Note: all the worksheets have the same columns (but may vary in number of rows, although this could be also made the same). Hope you understand my question ! Regards, Paul "Max" wrote: In your summary sheet, Assuming the sheetnames are listed in A2 down, eg: Sheet2, Sheet3, etc, with the ranges to be summed listed in B1 across, eg: A2:A10, B2:B100, etc you could place this in B2: =SUM(INDIRECT("'"&$A2&"'!"&B$1)) then simply copy across and fill down to populate the table -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "M.Moncrief" wrote: How can I use the INDIRECT formula to return the sum of specific cells in a data worksheet. My summary worksheet has a cell that requires the sum of the same 4 cells in each corresponding data worksheet. Any tips on modifying to make it work? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Edit/Add record in form from cmdButton | doodle | General Discussion | 3 | December 28th, 2005 03:06 AM |
Newbie Looking for Help | Little Penny | Using Forms | 6 | December 27th, 2005 08:33 PM |
Is Access even the right idea? | BMB | New Users | 19 | November 21st, 2005 08:01 PM |
Change font of part of text | John | Powerpoint | 7 | March 15th, 2005 10:10 AM |
Access reports with a horizontal line after each record??? | Bill via AccessMonster.com | Setting Up & Running Reports | 6 | March 9th, 2005 04:51 PM |