Thursday, January 12, 2012

yogi_Create A Comma Separated String Of Address of Cells That Contain A Specified String

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
user blaze867 said:
So, my brain feels like it's about to implode upon itself...hoping someone can help me to create the formula I need. I have spent hours trying to do & and looking on forums about this with nothing but errors. 
What i need to do is have basically a "user search" ability to locate text match to their query. I would like to have it so that a user types in the name (let's say olive) of an item they are looking for in cell B1, and after they hit enter, to have cell D1 display the cell address where they can find "olive". All the items they need to search are horizontal (row 4)
              A             B             C            D          E 
1    Enter Name Here >>>  olive   Find It Here >>>   C4,E4
4           lime          teal      dark olive      forest    light olive 

rows 2 & 3 are used for other things. I have tried using different formulas from off the forums and tweaking them out to my cell ranges (the total cell range to look is A4 to DQ4 so far). I know this is not excel, and that there are limitations. Is there a way to do this??? Or even a gadget I can plug-in at the top of the sheet. This has to be very simple for my users to utilize...some may not have even seen a spreadsheet before LOL. Any ideas what formulas I can try in my D1 cell??? I know i'm going to feel pretty stupid about it, it may be something i have overlooked.
following is a solution to the problem