Anyone have any suggestion on how to trim down the amount of work my formulae are doing? Any macro triggered or cell change made in my workbook is causing it to process for about 30 seconds.
The culprit is a vlookup array and I have no idea how to trim it down and still accomplish the same task. The formula is in 324 cells (9 columns 36 rows) and references over half a million cells. Here is the formula:
So essentially, based on a serial number in cell D3 it references entries in the ActionLog sheet. Column A in the ActionLog sheet houses the serial number for each entry and Columns B:J house the info I'm listing based on a varying number of entries of that serial number, up to 36.
Is there a more efficient way to list every entry of a serial number and all of its associated data (columns B:J) simply by typing in a serial number is cell D3?
I have 1 logical solution but not sure how to implement it, hopefully someone can figure this out for me. If I limit the range of my vlookup array to only be 36 rows (instead of the entire column), it will easily process that data. To make this a viable alternative I would need a way for my workbook to sort my ActionLog sheet based on the serial number inserted into cell D3. If by simply inserting it I can sort the data to put all my rows that serial number at the top of my ActionLog sheet, my vlookup array can work.
Possible? If so, will this sorting process take up similar memory and cause the same problem anyway?
Bookmarks