Cell change detection

Question

QUESTION: Good afternoon Jan

I have a tab (called LIST) that contains a list of product (column A) and their respective price (column B) and vendor (C). In an another tab (called DATA), I keep all my records regarding my sales in this respective order Product (column A), SalesType (B), Region (C), Price (D) and Vendor (E). I have more than 15000 records in that tab. My question is: When I'm modifying an info in tab LIST, I would like thatdetect automatically which info was modified/changes and replace automatically immediately all pertaining info in the tab DATA. To be clearer, if I modify cell C8 (vendor for product in row 8), my macro needs to find ALL records (there are many) in tab DATA for that combination and replace it with the new value assign to that cell. Ouf! Hope that is clear enough. Could you help?

ANSWER: Why not use formulas in DATA to look up the needed information from sheet LIST? That way, no macros are needed.

For example, this returns the data from column A pertaining to the matched entry in cell C8, looked up in column C of your list:

=INDEX(LIST!$A$1:$A$15000,MATCH(C8,LIST!$C$1:$C$15000,0))

---------- FOLLOW-UP ----------

QUESTION: I used to have the a multitude of Vlookup function in the tab Data to "import" changes in the tab LIST and it was working fine. The only problem was that the recalculation was too long (>2 minutes) each time the 'Enter" was pressed! With your proposal, I assume that they'll be a lot of index(...) formulas, which will slow down tremendously the application. This is why I wanted to have a macro detecting a cell change and process that change in the other tab. could it be done and how?

Answer

I prefer a formula, so your undo keeps working.

VLOOKUP and MATCH are very slow when run against unsorted lists. If however you can sort your list on the column in which we're looking up you can speed up calculation by a factor of 100 or more.

Sort the list in column with index numbers. Add an additional column on the other sheet with just an index to the other worksheet, so this index only calculates once for every row:

=IF(MATCH(C8,LIST!$C$1:$C$15000,1)=C8,MATCH(C8,LIST!$C$1:$C$15000,1),"")

Because match with argument 1 returns the closest match, we test to see if the match is exact, if not, we return a blank.

In the other columns, we point to this index number and use the INDEX function to return the proper row of data (suppose the indices are in column X and we're on row 8):

=INDEX(LIST!$A$1:$A$15000,X8)

I tested this and the previous INDEX/MATCH I posted calculated like this:

- List unsorted: 11.7 sec - List sorted: 10.2 sec

The NEW method (list sorted) took no more than 0.06 sec...