Final Figure by Week Ending Date

[JakeMann]

Hi All

I'm looking for a formula that will find the final closing figure of stock holding per week.

On the example attached I have a sheet that looks at goods in and out of storage by date and weekend date. I am looking for a formula that will look at the last entry for the weekending date and the figure in column G.

Would somebody be able to offer some advise?

Thanks

[Bo_Ry]

Not sure if I get this right
Please try at Goods in Stock Weekending D3
=IFERROR(LOOKUP(9^9,'Goods into Storage'!$G$4:$G$230/('Goods into Storage'!$B$4:$B$230=B3)),)

[modytrane]

See attached file.

I added a helper column in first sheet to indicate where the week ending occurs.

Put this in D3 [Goods in Stock Weekending] and drag it down.

You can adjust the range if you know the data size in Goods into Storage sheet instead of using the entire columns.

[JakeMann]

Hello, thank you that seems to have worked.

Can you explain how the formula in column K is working?

[davsth]

the formula in column k compares weekending dates on one row and the row below (column B), if they are not the same they are the last value in that week, eg the closing value

Subsequent formulas
=SUMIFS('Goods into Storage'!G:G,'Goods into Storage'!B:B,B3,'Goods into Storage'!K:K,1)

match the date in column B to the weekending of interest, when column K is also a 1, so just return the last value in column G