If statements using a sheet name....

[jcalvacca]

So in this line....

=IF('-700'!$A$10="Ebco",'-700'!$B$10," ") tests something just on a sheet named '-700'

I would like this line to be dynamic to look at the same cell on several different sheets....for example:

=IF('-700'!$A$10="Ebco",'-700'!$B$10," ")
=IF('-70'!$A$10="Ebco",'-70'!$B$10," ")
=IF('-71'!A11="Ebco",'-71'!B11," ")
etc....

If I bring in the "-700", "-71","-72", etc into cell A1, can I reference that cell to control which sheet these lines will reference??

Thanks!

 

[eibi]

Yep.

Use the Indirect(). Keep in mind, you are titling your sheets with numerical values, so in the reference cell where you type the name of the target spreadsheet, you'll have to key two apostrophes for it to work...and don't substitute a quotation mark:

''-700' (apostrophe-apostrophe-minus-seven-zero-zero-apostrophe)

Be aware that Indirect() is volatile, so if you use it too many times in a file, your processing time may become problematic.

See attached.

 

[PaulF]

=IF(INDIRECT("'"&F4&"'!"&"$H$3")="","VOID",INDIRECT("'"&F4&"'!"&"$H$3"))

I use this all of the time and have a spread sheet with 800 Work orders/worksheets in numeric order and pull data from all of them