Isolating the "=cell("contents") formula to apply only to cells in one column?

[dougmarkham]

Hi Folks,

I am working on a spreadsheet to log sales. I have two columns (customers and products) that I've created searchable drop-down lists for (using this method as outlined in youtube videos:
https://www.youtube.com/watch?v=vkPoViUhkxU).
https://www.youtube.com/watch?v=0QrQT9D25Xk&t=5s

Briefly the method uses the following formulas in combination with data validation:
=IF(ISNUMBER(SEARCH($D$2,G2)),MAX($F$1:F1)+1,0)
=OFFSET($I$2,,,COUNTIF($I$2:$I$36,"?*"))
=COUNTIF(I2:I36,"?*")
=IFERROR(VLOOKUP(ROWS($I$2:I2),$F$2:$G$36,2,0),"")
=CELL("contents")

An example of this spreadsheet for creating searchable drop-downs is as follows:

When you type a search word into the cells in Yellow, the =CELL("contents") function in Cell D2 takes that search word (from where ever it was typed) and runs a search against the database of names in column G.

Now, in my sales log spreadsheet, I have two database columns: Customers (Name/Tel/Email in column C) and Product (in column F). Therefore, I have two search cells (C5 and F5) in which the formula =CELL("contents") reside. The drop-down cells in columns C and F have different validation lists, referring to two separate tables (customers and products). I've also attached an image of the customer database so that you can see the formulas involved.

If I put a search term into column C (C6 downwards), because there is also a =CELL("contents") in Cell F5, the formula in C5 [=CELL("contents")] needs refreshing (F9). This takes about 20 seconds, and is far too long to make the searchable drop-down functional for my office colleagues.

At the moment, the searchable drop-down lists work, but each time one swaps between a customer search and product search, the =CELL("contents") has to be refreshed. I am hoping that it is possible to alter the =CELL("contents") formula so that it refers only to a specific range of cells in columns C and F of my sales log.
My question is:
1) Is this possible, and what formula would achieve this?]
2) Would isolating the cell range over which =CELL("contents") works solve my problem?
*(and if the answer is no, what might other options to solve the problem be?)

Kind regards,

Doug.

 

[dougmarkham]

Sorry, the 3 links didn't work: here they are again...

1) example spreadsheet: https://drive.google.com/open?id=0B090ECBRtpTuZnpObExXdkJ4bEk
2) my sales log: https://drive.google.com/open?id=0B090ECBRtpTuM2hjWDhROGlyN2s
3) customer table: https://drive.google.com/open?id=0B090ECBRtpTuRkkwRUpTNEtvM1k