1

I'm trying to deduct sales on each date from the stock. That means if I sold 10 units on 7th July from the stock of 100 units, I'd have 90 units left, and If I sell 20 units on 8th July the calculation would be 90-20=70 and so on as I sell units every day. The problem is I'm using getpivot is not changing dates as per the cells so I cannot copy the formula to the rest of the cells. is there any workaround? Check the screenshot to get an idea of what I'm trying to accomplish.

Here's the formula

AF4-GETPIVOTDATA("QTY.",$Z$3,"DATE",DATE(2022,6,6),"ORDER TYPE","Sell","BARODE/PRODUCT",639114570040)

Screenshot

Improve this question
1
  • As Engineer Toast suggested, you can change the data value to be the cell reference, but if you change the number of rows of the original data, the cell position will be changed, and this formula will fail.
    – Emily
    Commented Jun 15, 2022 at 9:04

1 Answer 1

Reset to default
1

Replace DATE(2022,6,6) with a reference to a cell with a date value. Say the cell A2 has the date 7th July and cell A3 has 8th July. The formula in, say, B2 would look like this:

=AF4-GETPIVOTDATA("QTY.",$Z$3,"DATE",A2,"ORDER TYPE","Sell","BARODE/PRODUCT",639114570040)

When you copy it down to B3, it'll look like this:

=AF4-GETPIVOTDATA("QTY.",$Z$3,"DATE",A3,"ORDER TYPE","Sell","BARODE/PRODUCT",639114570040)

I do this fairly often. I'll click in the pivot table to get the GETPIVOTDATA() function written for me then replace terms to be variable based on cells.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .