January 1, 2021
I am hoping you can guide me with a way to insert a new row near the top of a tab and NOT increment the absolute cell reference from a second tab within the same sheet.
I have a simple workbook, source tab, called TestTab, comprised of 10 columns along with 3 rows of "static" information. When I insert a new row it occurs on and becomes ROW 4.
One second tab, SpillRanges, I have a few cells where I need the value from TestTab!H4 to build the proper spill ranges for further use in the spreadsheet.
My formula for one of the Spill Ranges, other spill ranges only differ by the column I want data returned from, is:
--> Formula basically filters all the rows by column H and only returns those that are equal to H4, then returns the data from column F sorted as long as it is not blank or an error. Think of this as ColumnH is customer names, ColumnF is ship locations for that customer.
***The above works great, EXCEPT for when I insert a new row and it becomes ROW4. When I go to insert a new row, the reference in the formula above TestTab!$H$4 becomes TestTab$H$5. This of course happens with or without the "$".
I researched and learned about the use of INDIRECT and tried the following formula:
**Orange text only that changed
This formula works great, with the exception of the performance degradation within EXCEL when using INDIRECT.
I would like a way to accomplish the same end goal, but without INDIRECT, as it causes me issues on my system. Specifically it causes severe delay in processing within spreadsheets. I get that is to be expected and makes sense within the workbook INDIRECT is used. However, for me, having the workbook with INDIRECT open and using any other completely seperate, non-linked, etc, workbooks causes severe issues within them! Clearly I can close the INDIRECT workbook and ease the pain, but, since this is just for me I would like to come up with a new solution without using INDIRECT. T
Thanks in advance for any guidance or suggestions.
November 1, 2018