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
|
|||
|
|||
Formula using worksheet names 2
My original question dates from 31st July, and was helpfully answered.
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. |
#2
|
|||
|
|||
Formula using worksheet names 2
On your summary sheet, assuming A2 through whatever is Person A, Person B, etc.
B2: =INDIRECT("'"&A2&"'!B10") inside the parentheses, enter as follows " ' " & A2 " ' ! B10 " Hope this helps. -- John C "E" wrote: My original question dates from 31st July, and was helpfully answered. 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. |
#3
|
|||
|
|||
Formula using worksheet names 2
Fab, thank you. Another question, though - I want to copy the formula down so
that from the sheet it gives B10, B11, . . . Normally copying down would do this automatically, but using INDIRECT I get B10, B10, . . . etc. "John C" wrote: On your summary sheet, assuming A2 through whatever is Person A, Person B, etc. B2: =INDIRECT("'"&A2&"'!B10") inside the parentheses, enter as follows " ' " & A2 " ' ! B10 " Hope this helps. -- John C "E" wrote: My original question dates from 31st July, and was helpfully answered. 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. |
#4
|
|||
|
|||
Formula using worksheet names 2
That's because the B10 is within quotes, so is treated as text. You
can put it outside the quotes like this: =INDIRECT("'"&A2&"'!B"&ROW(A10)) Hope this helps. Pete On Aug 5, 3:24*pm, E wrote: Fab, thank you. Another question, though - I want to copy the formula down so that from the sheet it gives B10, B11, *. . . Normally copying down would do this automatically, but using INDIRECT I get B10, B10, . . . etc. "John C" wrote: On your summary sheet, assuming A2 through whatever is Person A, Person B, etc. B2: =INDIRECT("'"&A2&"'!B10") inside the parentheses, enter as follows " ' " & A2 " ' ! B10 " Hope this helps. -- John C "E" wrote: My original question dates from 31st July, and was helpfully answered.. 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.- Hide quoted text - - Show quoted text - |
#5
|
|||
|
|||
Formula using worksheet names 2
Thank you, that works really well.
"Pete_UK" wrote: That's because the B10 is within quotes, so is treated as text. You can put it outside the quotes like this: =INDIRECT("'"&A2&"'!B"&ROW(A10)) Hope this helps. Pete On Aug 5, 3:24 pm, E wrote: Fab, thank you. Another question, though - I want to copy the formula down so that from the sheet it gives B10, B11, . . . Normally copying down would do this automatically, but using INDIRECT I get B10, B10, . . . etc. "John C" wrote: On your summary sheet, assuming A2 through whatever is Person A, Person B, etc. B2: =INDIRECT("'"&A2&"'!B10") inside the parentheses, enter as follows " ' " & A2 " ' ! B10 " Hope this helps. -- John C "E" wrote: My original question dates from 31st July, and was helpfully answered.. 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.- Hide quoted text - - Show quoted text - |
#6
|
|||
|
|||
Formula using worksheet names 2
You're welcome - thanks for feeding back.
Pete On Aug 5, 3:42*pm, E wrote: Thank you, that works really well. |
Thread Tools | |
Display Modes | |
|
|