syntax: linking cells to sheet names

[al]

Hopefully I have this in the right group. Sorry in
advance if it's not.

I am **trying** to do a complex link in excel, and I'm
afraid that excel might not enable me to do it. Or more
likely unable to do it the way I am thinking.

I have 2 cells that are the same pulldown menu of a list.
The list items are all text strings that match the names
of the sheets in the workbook. I want to be able to pass
the text string value from the cell (pulldown list) to
excel functions (ie IF, SEARCH) by linking/pointing to the
cell with the pulldown list. But it keeps taking the cell
location as a file name, instead of grabbing the text
string that the cell location points to. I am obviously
have syntax issues here.

Example:
Pull down list locations: sheet:Index, cell: A3, A4
Pull down list: various fruit types (ie apples, oranges,
etc)
Worksheet names: apples, oranges, etc

if it wasn't for the pull down menu thing I would put for
example the following:
=IF(apples!A1=oranges!A1,"same","different")

I tried to put in the following without luck:
=IF($A$3!A1=$A$4!A1,"same","different")
=IF('$A$3'!A1='$A$4'!A1,"same","different")
=IF("$A$3"!A1="$A$4"!A1,"same","different")
=IF(($A$3)!A1=($A$4)!A1,"same","different")

The purpose of this nightmare is to have a summary sheet
with 2 pulldown menus (for customer ease of use and to
limit their ability to screw things up) in cells listing
all the other sheets in the workbook. The customer picks
the two sheet to be compared (ex apples and oranges), and
below it displays whether the fields are the same or
different.

Thanks MUCH in advance,
Al

 

[Jim]

Enter this in an empty cell on Sheet1:

=IF(AND(A1="Apples",Sheet1!A2="Apples"),"Same","Different")

 

[al]

let me reword it ... that wasn't what I was looking for.

when passing file/sheet/cell (ie [file1.xls]sheet1!A1)
data to excel functions, does it all have to be 'static'?
Or can one of the components (ie "sheet1!") be a
variable? Or in my case, the specific text string value
of a cell, which actually is a pulldown menu of a list,
changeable by the end-user.


thx again,
Al

 

[Harlan Grove]

when passing file/sheet/cell (ie [file1.xls]sheet1!A1)
data to excel functions, does it all have to be 'static'?
Or can one of the components (ie "sheet1!") be a
variable? Or in my case, the specific text string value
of a cell, which actually is a pulldown menu of a list,
changeable by the end-user.

....

As long as the referenced range is in a file that's open, you can use
INDIRECT. For example, if A1 held the worksheet name and the file FOO.XLS
were open,

=INDIRECT("'[FOO.XLS]"&A1&"'!X99")

would return a range reference to cell X99 in FOO.XLS's worksheet named in
A1.