A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Using a Text Cell to Reference a Worksheet Name



 
 
Thread Tools Display Modes
  #1  
Old February 28th, 2006, 12:56 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
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?
Ads
  #2  
Old February 28th, 2006, 01:55 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
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  
Old February 28th, 2006, 01:57 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
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  
Old February 28th, 2006, 01:59 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
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  
Old March 8th, 2007, 11:16 PM posted to microsoft.public.excel.worksheet.functions
M.Moncrief
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  
Old March 11th, 2007, 08:45 AM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 8,574
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  
Old May 31st, 2010, 12:06 PM posted to microsoft.public.excel.worksheet.functions
PvZ
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  
Old May 31st, 2010, 12:26 PM posted to microsoft.public.excel.worksheet.functions
Roger Govier[_8_]
external usenet poster
 
Posts: 338
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?

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 05:27 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 OfficeFrustration.
The comments are property of their posters.