Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COPYING FORMULA CONTAINING NAMES/RELATIVE RANGE NAMES | Excel Discussion (Misc queries) | |||
Trying to list tab/worksheet names in a summary worksheet | Excel Discussion (Misc queries) | |||
Adding worksheet tab names to the first worksheet | Excel Discussion (Misc queries) | |||
help keeping the worksheet names within formula | Excel Worksheet Functions | |||
How to link Excel worksheet tab names to dates in each worksheet? | Excel Worksheet Functions |