An 'absolute cell reference' in a VLOOKUP formula is modified when new .csv data is loaded

[jim285]

My problem occurs as a result of loading data from a .csv file into a ‘data’ sheet of my workbook.
As best as I can describe the error, an absolute cell reference in a VLOOKUP formula is changed to point to the cell just after the end of the loaded .csv data, that is, if there are 12568 entries in the .csv file, then after loading into the Data sheet, the absolute cell reference is changed from, say $A$1:$C$100000 to $A$12569:$C$100000.

For about six months, I have diligently searched for any clue to explain and/or fix this problem w/o success. Thus far, I have only found one post similar to my problem, but no solution was generated. The link to the referenced post is: Macro to import CSV file stopped working right - Ubuntu OS -LibreCalc 7.4

A simplified version of my spreadsheet – I own a portfolio of stocks. My workbook consists of two spreadsheets, FOLIO and Data. The first is a compilation of my stock symbols in the A column and the previous day’s closing price in column B. In the C column, I have a VLOOKUP formula which ‘looks up’ today’s price of each stock in column A using the new data in the Data sheet. There is a macro incorporated in the workbook which is intended to import the (external) TodayPrices.csv file into the second spreadsheet named “Data”. The VLOOKUP() function in column C of the FOLIO sheet then updates prices in column C. The VLOOKUP formulation, starting in Cell C1 on the main sheet is [ =VLOOKUP(A1,Data.$A$1:$C$1000,1,0) ]. The problem is that the (macro ?) is not supposed to modify the VLOOKUP function but, in loading the csv “data” file, the VL function is changed so that the search on the FOLIO sheet starts at the last loaded cell in the “Data” sheet instead of cell $A$1, viz., if the original csv file contained 900 names, then the VL function is changed to [ =VLOOKUP(A1,Data.$A$901:$C$1000,1,0) ]. And the main sheet then displays #N/A in all relevant cells. I realized this is an extremely long post, but I have searched the net for some clue/hint for what the cause of the apparent bug. If anyone can suggest an insight or viable debug approach, I say Thanks in Advance.

[Zizi64]

Try to use a Named Range in the vlookup function.
And modify the .csv Import macro.
Add a new feature to it: “Adjust the assigned range of the Named Range” by the macro code.

[Wanderer]

@jim285

I realized this is an extremely long post, but

you didn’t include the macro or a sample sheet. So most answers have to rely on guesswork…

[Zizi64]

There are similar topics in the AOO/LO Forum with code snippets:

https://forum.openoffice.org/en/forum/viewtopic.php?t=76308
https://forum.openoffice.org/en/forum/viewtopic.php?t=77941
…

[mikekaganski]

@jim285

My workbook consists of two spreadsheets, FOLIO and Data. The first is a compilation of my stock symbols in the A column and the previous day’s closing price in column B. In the C column, I have a VLOOKUP formula which ‘looks up’ today’s price of each stock in column A using the new data in the Data sheet. There is a macro incorporated in the workbook which is intended to import the (external) TodayPrices.csv file into the second spreadsheet named “Data”. The VLOOKUP() function in column C of the FOLIO sheet then updates prices in column C. The VLOOKUP formulation, starting in Cell C1 on the main sheet is [ =VLOOKUP(A1,Data.$A$1:$C$1000,1,0) ]. The problem is that the (macro ?) is not supposed to modify the VLOOKUP function

You described everything, except the most important piece: the exact macro code. Because it is very important to understand, what the macro does.

E.g., if the macro deletes some rows, before inserting others, it would naturally seemingly “change” the references to the range (absolute or relative) - because e.g. the absolute references reference the physical cells, which got deleted, not “names”.

It may be a bug, or it may be a bad macro. There may be workarounds or proper ways to do that. But only having a sample spreadsheet with the macro, and a sample CSV to play with, would enable anyone to try to come with a proper explanation / solution / advise.