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  

Formula using worksheet names



 
 
Thread Tools Display Modes
  #1  
Old July 31st, 2008, 09:54 AM posted to microsoft.public.excel.worksheet.functions
E
external usenet poster
 
Posts: 80
Default Formula using worksheet names

I have several sheets, called say Code 1, Code 2 . . . Code 10, each with a
table culminating in a total budget figure, which is in the same cell on each
sheet, say B24. On a separate sheet I have a table with one row per code, and
I want to show each code's budget figure next to the code numer, ie, Code 1:
£10000, Code 2: £5444 etc.
A way to do this is for each code to say ='Code x'!B24, where x is the code
number. This involves me typing this in or clicking to link for each code,
which is tedious for up to 100 codes. I want to write a formula on the lines
of ='Code "A5"'!B24 where A5 is the code number, ie, "look at cell B24 in the
sheet denoted by the code on this row".
I'm sorry, this isn't very easy to explain.
  #2  
Old July 31st, 2008, 10:41 AM posted to microsoft.public.excel.worksheet.functions
Pete_UK
external usenet poster
 
Posts: 8,780
Default Formula using worksheet names

Put this in B5:

=INDIRECT("'Code "&A5&"'!B24")

Then just copy down as required.

Hope this helps.

Pete

On Jul 31, 9:54*am, E wrote:
I have several sheets, called say Code 1, Code 2 . . . Code 10, each with a
table culminating in a total budget figure, which is in the same cell on each
sheet, say B24. On a separate sheet I have a table with one row per code, and
I want to show each code's budget figure next to the code numer, ie, Code 1:
£10000, Code 2: £5444 etc.
A way to do this is for each code to say ='Code x'!B24, where x is the code
number. This involves me typing this in or clicking to link for each code,
which is tedious for up to 100 codes. I want to write a formula on the lines
of ='Code "A5"'!B24 where A5 is the code number, ie, "look at cell B24 in the
sheet denoted by the code on this row".
I'm sorry, this isn't very easy to explain.


  #3  
Old July 31st, 2008, 10:43 AM posted to microsoft.public.excel.worksheet.functions
Gary''s Student
external usenet poster
 
Posts: 7,584
Default Formula using worksheet names

In Cell A1 of another sheet enter:
=INDIRECT("'Code " & ROW() & "'!" & "B24") and copy down
or
=INDIRECT("'Code " & COLUMN() & "'!" & "B24") and copy across

be careful of the single and double quotes.

--
Gary''s Student - gsnu200797


"E" wrote:

I have several sheets, called say Code 1, Code 2 . . . Code 10, each with a
table culminating in a total budget figure, which is in the same cell on each
sheet, say B24. On a separate sheet I have a table with one row per code, and
I want to show each code's budget figure next to the code numer, ie, Code 1:
£10000, Code 2: £5444 etc.
A way to do this is for each code to say ='Code x'!B24, where x is the code
number. This involves me typing this in or clicking to link for each code,
which is tedious for up to 100 codes. I want to write a formula on the lines
of ='Code "A5"'!B24 where A5 is the code number, ie, "look at cell B24 in the
sheet denoted by the code on this row".
I'm sorry, this isn't very easy to explain.

  #4  
Old July 31st, 2008, 11:08 AM posted to microsoft.public.excel.worksheet.functions
E
external usenet poster
 
Posts: 80
Default Formula using worksheet names

Thank you very much, but I found the other person's method worked better for
me.

"Gary''s Student" wrote:

In Cell A1 of another sheet enter:
=INDIRECT("'Code " & ROW() & "'!" & "B24") and copy down
or
=INDIRECT("'Code " & COLUMN() & "'!" & "B24") and copy across

be careful of the single and double quotes.

--
Gary''s Student - gsnu200797


"E" wrote:

I have several sheets, called say Code 1, Code 2 . . . Code 10, each with a
table culminating in a total budget figure, which is in the same cell on each
sheet, say B24. On a separate sheet I have a table with one row per code, and
I want to show each code's budget figure next to the code numer, ie, Code 1:
£10000, Code 2: £5444 etc.
A way to do this is for each code to say ='Code x'!B24, where x is the code
number. This involves me typing this in or clicking to link for each code,
which is tedious for up to 100 codes. I want to write a formula on the lines
of ='Code "A5"'!B24 where A5 is the code number, ie, "look at cell B24 in the
sheet denoted by the code on this row".
I'm sorry, this isn't very easy to explain.

  #5  
Old July 31st, 2008, 11:09 AM posted to microsoft.public.excel.worksheet.functions
E
external usenet poster
 
Posts: 80
Default Formula using worksheet names

That seems to work fine. I have never used INDIRECT before. Many thanks for
your help.
However, when I have a zero in B24 sometimes it returns 0 and sometimes
REF#! - do you have any idea why?

"Pete_UK" wrote:

Put this in B5:

=INDIRECT("'Code "&A5&"'!B24")

Then just copy down as required.

Hope this helps.

Pete

On Jul 31, 9:54 am, E wrote:
I have several sheets, called say Code 1, Code 2 . . . Code 10, each with a
table culminating in a total budget figure, which is in the same cell on each
sheet, say B24. On a separate sheet I have a table with one row per code, and
I want to show each code's budget figure next to the code numer, ie, Code 1:
£10000, Code 2: £5444 etc.
A way to do this is for each code to say ='Code x'!B24, where x is the code
number. This involves me typing this in or clicking to link for each code,
which is tedious for up to 100 codes. I want to write a formula on the lines
of ='Code "A5"'!B24 where A5 is the code number, ie, "look at cell B24 in the
sheet denoted by the code on this row".
I'm sorry, this isn't very easy to explain.



  #6  
Old July 31st, 2008, 11:13 AM posted to microsoft.public.excel.worksheet.functions
E
external usenet poster
 
Posts: 80
Default Formula using worksheet names

Sorry, I fixed my second problem. :-)

"E" wrote:

That seems to work fine. I have never used INDIRECT before. Many thanks for
your help.
However, when I have a zero in B24 sometimes it returns 0 and sometimes
REF#! - do you have any idea why?

"Pete_UK" wrote:

Put this in B5:

=INDIRECT("'Code "&A5&"'!B24")

Then just copy down as required.

Hope this helps.

Pete

On Jul 31, 9:54 am, E wrote:
I have several sheets, called say Code 1, Code 2 . . . Code 10, each with a
table culminating in a total budget figure, which is in the same cell on each
sheet, say B24. On a separate sheet I have a table with one row per code, and
I want to show each code's budget figure next to the code numer, ie, Code 1:
£10000, Code 2: £5444 etc.
A way to do this is for each code to say ='Code x'!B24, where x is the code
number. This involves me typing this in or clicking to link for each code,
which is tedious for up to 100 codes. I want to write a formula on the lines
of ='Code "A5"'!B24 where A5 is the code number, ie, "look at cell B24 in the
sheet denoted by the code on this row".
I'm sorry, this isn't very easy to explain.



  #7  
Old July 31st, 2008, 11:38 AM posted to microsoft.public.excel.worksheet.functions
Pete_UK
external usenet poster
 
Posts: 8,780
Default Formula using worksheet names

OK, glad to hear it, and thanks for feeding back.

Pete

On Jul 31, 11:13*am, E wrote:
Sorry, I fixed my second problem. :-)



"E" wrote:
That seems to work fine. I have never used INDIRECT before. Many thanks for
your help.
However, when I have a zero in B24 sometimes it returns 0 and sometimes
REF#! - do you have any idea why?


  #8  
Old August 5th, 2008, 02:35 PM posted to microsoft.public.excel.worksheet.functions
E
external usenet poster
 
Posts: 80
Default Formula using worksheet names

Unfortunately I am now stuck on a similar problem, which I thought would be
easier. I now have sheets called Person a, Person b, . . . with figures I
want to pull through to a summary sheet. This time I have a table with Person
a, Person b, . . . in a column, so I just want to say ='sheet(A3)' cell(B10)
where A3 is 'Person a' and B10 is the cell figure I want to pull through. I'm
getting tangled up with inverted commas. Please help!

"E" wrote:

I have several sheets, called say Code 1, Code 2 . . . Code 10, each with a
table culminating in a total budget figure, which is in the same cell on each
sheet, say B24. On a separate sheet I have a table with one row per code, and
I want to show each code's budget figure next to the code numer, ie, Code 1:
£10000, Code 2: £5444 etc.
A way to do this is for each code to say ='Code x'!B24, where x is the code
number. This involves me typing this in or clicking to link for each code,
which is tedious for up to 100 codes. I want to write a formula on the lines
of ='Code "A5"'!B24 where A5 is the code number, ie, "look at cell B24 in the
sheet denoted by the code on this row".
I'm sorry, this isn't very easy to explain.

  #9  
Old August 5th, 2008, 02:54 PM posted to microsoft.public.excel.worksheet.functions
David Biddulph
external usenet poster
 
Posts: 8,714
Default Formula using worksheet names

What's the problem with =INDIRECT("'"&A3&"'!"&B10) ?
--
David Biddulph

"E" wrote in message
...
Unfortunately I am now stuck on a similar problem, which I thought would
be
easier. I now have sheets called Person a, Person b, . . . with figures I
want to pull through to a summary sheet. This time I have a table with
Person
a, Person b, . . . in a column, so I just want to say ='sheet(A3)'
cell(B10)
where A3 is 'Person a' and B10 is the cell figure I want to pull through.
I'm
getting tangled up with inverted commas. Please help!

"E" wrote:

I have several sheets, called say Code 1, Code 2 . . . Code 10, each with
a
table culminating in a total budget figure, which is in the same cell on
each
sheet, say B24. On a separate sheet I have a table with one row per code,
and
I want to show each code's budget figure next to the code numer, ie, Code
1:
£10000, Code 2: £5444 etc.
A way to do this is for each code to say ='Code x'!B24, where x is the
code
number. This involves me typing this in or clicking to link for each
code,
which is tedious for up to 100 codes. I want to write a formula on the
lines
of ='Code "A5"'!B24 where A5 is the code number, ie, "look at cell B24 in
the
sheet denoted by the code on this row".
I'm sorry, this isn't very easy to explain.



 




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


All times are GMT +1. The time now is 10:17 AM.


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