Return Value for Current Month

[HangMan]

I have a sheet made up of 'Estimates' and 'Actuals' (attached). In column AC when the 8th working day of the current month is reached I want to return the 'Actual Value' for the 'Previous Month'.

M1 = April, M2 = May and so on. I've been playing around with a combination of 'INT(COLUMN($C18:$Z18)-1)/2)' and '(MOD(COLUMN($C18:$Z18),2)=0)' to try and return the correct value along with

Please Login or Register  to view this content.

but have so far failed to get this to work becasue the layout (which is fixed) means I need the value in every alternate cell needs to be returned. AC needs to update to the 'Actual Value' for the prior month on the 8th working day of each current month.

Can anyone help me to solve this...

Many thanks

[6StringJazzer]

Your data layout is not clear. Where do I find the actual value for the 8th working day of any given month?

[JohnTopley]

See the attached:

I have used date fields in row 15 and would advise you do the same as it is much easier to use formula with dates than text.

Formula in AB18 retrieves the data and I put a date in AB13 for testing: change to TODAY() in formula.

=INDEX($C$18:$Z$21,,MATCH(EOMONTH($AB$13,-2)+1,$C$15:$Z$15,0)+1)

Add your test for workday 8

=IF( today() = Workday(......), Index(.............),"")

=IF($AB$13=WORKDAY(EOMONTH(TODAY(),-1)+1,8),INDEX($C$18:$Z$21,,MATCH(EOMONTH($AB$13,-2)+1,$C$15:$Z$15,0)+1),"")

Added updated file.

[HangMan]

Hi Jeff,

Apologies, of course it made sense to me but I didn't explain it very well. So in the table, the actual value for the prior month only becomes available on the 8th working day of the current month and so will be populated using a formula (not shown on the sheet to keep it simple). Looking at the sample sheet you can see that 'actual' values for months 1 to 4 have been populated because we are past the 8th working day of month 5. The value for August (M5) will be available and will be populated on the 8th working day of September (M6).

What I'm looking to do in column AC is automatically update the 'actual' value for the prior month on the 8th working day of the current month, but the value needs to remain present until the 8th working day of the following month (i.e. it shouldn't show the value 'only' on the 8th working day). So on the 8th working day of September, column AC will show the 'actual' value for M5 and that will remain there until the 8th working day of October when the value in column AC needs to automatically update to the value that will be entered for September and so on.

Not sure if that makes it any clearer or if I've made it more confusing?

[HangMan]

Hi John,

Thanks for this. I'm very restricted in terms of what I can add to the sheet and adding date fields (whilst obviously practical) isn't possible because the table (on my actual sheet) already has column headers and the sheet layout conforms to strict brand guidelines and we can't add to these.

After a bit of playing around, I adapted your formula to this, which I think will do the same thing without the addition of dates:

Please Login or Register  to view this content.

Unless you can see any errors with this approach?

Many thanks