select a cell value based on current month & future months not having a value

Hi,

I am trying to create a stock valuation sheet where year to date (YTD)total = the current month total.
To this end I have created my 12 months Jan - Dec with a column for closing stock in each month.
The opening stock value in YTD is fixed in the YTD closing stock I need the current months closing value to be displayed.
I have tried nesting IF functions, the formula errors with too many functions.
any ideas how I can create a formula which selects the last available amount?

Hi Khalos1208

I am having difficulty in visualising what you are trying to achieve. Perhaps if you were to upload a spreadsheet with what you want, it will help everyone understand.

Regards
Alastair

Hi, i have attached a file as requested.
in the example I need the latest month's Close Value in this case Column Q for May to populate Column G for YTD. When June gets a value it must replace May etc.

Hi Khalos1208

I am still not sure what you are trying to achieve. However, I have a way of doing as you ask. If you put the Column reference in E1 and G1, it will pull the data from that column. I hope this is what you want.

I have taken the opportunity to show another formula in F5

Regards
Alastair

Hi Alistair,
The Formula in column F is excellent thanks. That's really useful.
I'll try to explain the problem a little better.
The YTD open stock will always be the same as the open from March. This is fixed.
The Purchase in YTD will be the sum of all the purchases from each month ( great formula Column F)
The Closing Stock in YTD must be the last closing stock value available. ie at the end of April 125, End of May YTD changes to 150, End of June YTD changes to 175, End of July changes to 190. ETC.
This allows us to calculate a GP for the YTD & also for the current month.
Hope this makes sense
Khaos.