Monday, January 20, 2014

yogi_Given A List of Strings In Sheet1 Compute Sheet Number In Which The String Is Housed In Column A of Sheet 2 3 or 4

                                          Google Spreadsheet   Post  #1490
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-17-2014
question by u hallmann (http://productforums.google.com/forum/?zx=mhqrxt960fmr&usp=sheets_web#!category-topic/docs/spreadsheets/FJ6UweibwwQ)
Find a sheet name containing a string
Hello,

I have been trying and searching now for quite a bit of time. Seems to be time to ask you folks for a hint on my following challenge:

Say I have 4 sheets:
- sheet 1: the summary sheet, containing a list of strings
- sheet 2, sheet 3, sheet 4: Each containing a partial list of the strings from the summary sheet in A1:A10

Sheet 2,3,4 will not contain duplicate strings in A1:A10.

I am looking for a function that queries for a string and returns the sheet name it resides on.

EXAMPLE:

The following shall be given:

Sheet 1 
alpha
beta
charlie
delta
echo
foxtrott
golf

Sheet 2
alpha
beta
charlie

Sheet 3
delta
echo

Sheet 4
foxtrott
golf

Result shall be added to sheet 1, just like an index in two columns:

alpha - sheet 2
beta - sheet 2
charlie - .....
...
golf - sheet 4

Who could shed some light into this?

Thanks a lot!!!

U1f

-----------------------------------------------------------------------------------------------