Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 6th 07, 06:56 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 504
Default referencing a different tab by referencing a list in the current s

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

  #2   Report Post  
Old July 6th 07, 07:29 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
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



  #3   Report Post  
Old July 6th 07, 07:44 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 504
Default referencing a different tab by referencing a list in the curre

T. Valko is a genius, but do remember to put dollar signs in front of the J
and 49 if you want those to stay constant.

"T. Valko" wrote:

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




  #4   Report Post  
Old July 6th 07, 07:57 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default referencing a different tab by referencing a list in the curre

=INDIRECT("'"&A1&"'!J49")
remember to put dollar signs in front of the J
and 49 if you want those to stay constant.


$ are not need on J49: $J$49

Because the cell reference is actually a TEXT string when enclosed in
quotes: "'!J49", it will not change when copied.

--
Biff
Microsoft Excel MVP


"Kevin" wrote in message
...
T. Valko is a genius, but do remember to put dollar signs in front of the
J
and 49 if you want those to stay constant.

"T. Valko" wrote:

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








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Referencing a Drop-Down List Rubble Excel Discussion (Misc queries) 8 May 31st 07 06:39 PM
Referencing a data validation list [email protected] Excel Discussion (Misc queries) 5 December 19th 06 11:55 PM
If referencing drop-down list Nicole Seibert Excel Worksheet Functions 2 July 21st 06 02:34 PM
Drop List Referencing Boony Excel Worksheet Functions 2 November 11th 04 12:42 PM
Drop List Referencing Boony Excel Worksheet Functions 0 November 10th 04 07:56 PM


All times are GMT +1. The time now is 07:01 AM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017