View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
T. Valko T. Valko is offline
external usenet poster
 
Posts: 15,768
Default referencing a different tab by referencing a list in the current s

Try this:

=INDIRECT("'"&A1&"'!J49")

Copy down as needed

--
Biff
Microsoft Excel MVP


"Kevin" wrote in message
...
One workbook, many different sheets/tabs.
Lets say I have a 'summary' sheet and then the names of the sheets after
that start with 'one', 'two', 'three', and so on. Cell J49 in every sheet
has a certain piece of data I need to reference in the 'summary' sheet.
Back
in the 'summary' sheet there is a list of all the other sheet names,
column
A. In column B of the 'summary' sheet I need to reference J49 from the
appropriate sheet next to the list of all the sheet names in column A.
Right now, I have in column B one!$J$49, two!$J$49 and so on written in
every cell.
I was trying to see if I could put in something like A1!$J$49 in the cell
to
reference column A as the sheet name instead of typing it in but that
doesn't
work. I want to do this so that when I put my cursor on the bottom right
of
the cell and drag it down the sheet name will be automatically entered in
the
columnB cell instead of having to type in each name manually.
I know this is confusing. I have a fair bit of excel experience and I'm
stuck on this one.

thanks