Lookin up within a cell

  • Thread starter Thread starter dvinds
  • Start date Start date
D

dvinds

Hi,
Hpefully you may be able to help. I can't think of what to call what I
want to do....hence can't search the help.
Question: I have cells in a page that refer to certain cells on another
sheet, however I want to be able to use the same sheet (as a template)
for different quaters, without have to go to each cell and change the
reference.
Example (cell b2) Formula: ='Oct ''03'!HF6, the ('Oct "03'!) is used in
several cells.
On each sheet has 3 months. EG: Oct, Nov, Dec. What I'd like to be able
to do is put ('Oct "03'!) in cell a1, ('Nov "03'!) in cell a2 and
('Dec "03'!) in cell a3. and have the relevant cells look up a1, 2 or 3
for the sheet needed and display the required information from that
sheet.
Looking something like this: (cell b2) =a1;HF6 (this didn't work
ofcourse)

I'm doing quarterly summary sheets for each year.

Hope someone can help
 
Think INDIRECT() might work for you ..

An example perhaps to illustrate?

Say you have monthly sheets named: Oct2003, Nov2003, etc

In a sheet: Summary (say):

You have a table with row and column headers:

Col headers listed across
in cells A1, B1, C1 etc are: 2003, A1, B1 ..
(A1 and B1 are the target cell refs
in the monthly sheets for extract)

Row headers listed in A2 down
are the months: Oct, Nov, etc

Table will look like:

2003__A1__B1__etc
Oct
Nov
etc

Put in B2: =INDIRECT("'"&$A2&$A$1&"'!"&B$1)

This is equivalent to the link formula : =Oct2003!A1
which extracts in B2 the value from cell A1 in sheet: Oct2003

Fill B2 across, then down

With the set-up above, you could easily change
the row and column headers to suit the target cells
for extract from the monthly sheets, the year etc.
 
Thanks Guys,
It didn't do what I wanted it to do....
will have to work on it.....Done something similar before, blowed if
can remember what I did :
 
Sorry to hear that .. albeit IMHO, the key descripts in
your original post did seem to point to use of INDIRECT
(even on the 3rd reread <g>)

Hang around your post awhile.
Perhaps others would offer their insights.
 
Indirect did work.....thanks for that.
It wasn't what I was first thinking of, however,
It worked by putting the main reference sheet in a1,a2,a3 & a4 and th
second constant reference in cells on the same page.
=INDIRECT("'" &$A$1& $B$2)
eg:
a1 = 'BAS 1 ''02-''03'! (page name) Changeable
b1 = $j$3 (cell reference)
b2, b3 and so on and so on.
This is probably what you meant in your replies, I just couldn't quit
see it at first.
Thanks again, I really appreciate it
 
Back
Top