Formula using worksheet names

E

E

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.
 
P

Pete_UK

Put this in B5:

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

Then just copy down as required.

Hope this helps.

Pete
 
G

Gary''s Student

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.
 
E

E

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?
 
E

E

Sorry, I fixed my second problem. :)

E said:
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?
Click to expand...
 
E

E

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Hide columns based on row values 2
formula to look up ref in one sheet and offset in another 1
how do I count a rota with no dates and using 24 hours clock 2
Use Formula from another worksheet 3
Locating a Worksheet from another Workbook 12
Drop down or Combo Box Help with VBA code and formula 4
Two-Column Lookup 2
EXCEL FORMULA 4

Top