Hello,
I am having a really odd issue with an Excel report I have created, and the vlookup function. Some background:
I have pulled in multiple live reports from an online source through 'queries and connections', and they are all pulling in alright, refreshing fine, all that.
To bring all this data together, one of the reports that is being pulled through the queries is only pulling one column, a list of values. These live values are being used to conduct a vlookup on the rest of the sheets of live reports, so that in the end, I have a single visible sheet, with one live column updating all the rest of the columns. This works beautifully, and all of the information in the columns is updating correctly when refresh all is hit.
That is, until new values are added to column A. I have the entire sheet set up to accommodate up to 1600 values in column A, with the formulas in all the empty rows just chilling until a value appears in column A. The problem is that when a new value DOES appear, instead of leaving my formulas as they are, Excel has decided to change the very last row that previously had information displayed correctly. Instead of referencing the cell directly next to it, it jumps the vlookup reference cell down, the exact number of lines that have been added, so that the row shows errors. The rest of the rows below then follow this new number sequence, all referencing empty cells.
Now I think it is worth noting that I have already made some major changes trying to fix this issue. Originally, I had all of the live reports pulling in through queries, separate from this main sheet, and I was using an equals function to bring in Column A’s values. The same type of issue was occurring though; when new values were added to the live reports, the equals function in column A would break at the last line, and cause errors.
Anyone who has any notion of what I can do is welcome to help; I have already tried: Locking/protecting the sheet (which then prevents refreshes from happening until the sheet is unprotected and makes no difference), absoluting the reference cell with ‘$’s (which doesn’t make a difference either, and insultingly enough, excel changes my formula AND keeps the ‘$’ sign, as if that’s what it always said), and Googling it (which told me how to protect locked cells, which I already knew, and would only be helpful if another user was making these changes)
Please and thank you!!!
