I am trying to create a report from another report(source sheet). :) The source sheet updates daily automatically by inserting new rows with progress on sales on top of the rows completed a day before:

The "Units sold" column is cumulative meaning that a newer record on a certain product will show a greater or equal value to a previous record on that specific product. New products appear in the source sheet when entering the company and they disappear from it when they are sold out, pretty much randomly. (e.g. product "F" that showed up and sold out in the same day) In the first column in the source report i already found a formula that concatenates date and product and is used by another reports.

To solve this, in the results report i made on column T the same concat of date and product. Then, in my new report, in the results column, i used the following formula: =vlookup(T2,Source!$A2:$C$10000,3,0)-vlookup(T2,Source!$A3:$C$10000,3,0) with the intention to obtain the difference between the amount of products sold in the last day vs the amount of products sold in the day before it, or, better said, the amount of each of the products sold on a specific date. Finally, by using a column of =year() and one of =month() applied on date column and a couple of pivot tables i was able to obtain the value of the daily increment for each and/or year.

The problem i couldn't find a solution for is that when the source sheet updates, the new rows added with the freshest data move down the cell references from the vlookup formula i used in the results sheet. Please help me find a way to "pin down" the cell references in the vlookup formula without being affected by the new rows insertions.

Thank you!

CodePudding user response：

to "pin down" the references you can use INDIRECT

example:

A1:A >>> INDIRECT("A1:A")