Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jay L
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default 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?



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR
 
Posts: n/a
Default Using a Text Cell to Reference a Worksheet Name

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?



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
cvolkert
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
PvZ PvZ is offline
external usenet poster
 
Posts: 6
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 376
Default 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?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding a row to worksheet does not update cell references in another. blausen Excel Worksheet Functions 5 February 25th 06 10:14 PM
Reference another worksheet using a cell Lynxbci3 Excel Discussion (Misc queries) 1 November 2nd 05 02:08 PM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM
How to reference a text cell that changes weekly in Header or Foo. duane Excel Discussion (Misc queries) 2 March 20th 05 03:31 PM
Using a cell reference to refernce worksheet in another work book [email protected] Excel Worksheet Functions 5 January 6th 05 07:26 PM


All times are GMT +1. The time now is 08:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"