Is it possible to have formula change depending on Day of Week

Hi,

I currently have a formula "=VLOOKUP(B2,0410!B:C,2,FALSE)"

0410 = Todays date in DDMM format

Each day a new sheet is created and the formula looks at the previous day.

EG
0410 = 4th October and the formula within this sheet will be "=VLOOKUP(B2,0310!B:C,2,FALSE)"

0510 = 5th October and the formula within this sheet will be "=VLOOKUP(B2,0410!B:C,2,FALSE)"

The tricky part is when it is a Monday i want the formula to look 3days back.

So for this week it looks like this:
0310 = and the formula within this sheet will be 3rd October (But looking at data for 30th September) "=VLOOKUP(B2,3009!B:C,2,FALSE)"

I have created a templated and replaced the date needed with "template". What i am wanting is a vb code that will change "template in the formula below with the required date".

"=VLOOKUP(B2,template!B:C,2,FALSE)"

Any suggestions?

You could make that formula "dynamic" to always give you the right sheet referring to the previous working day, i.e.

=VLOOKUP(B2,INDIRECT(TEXT(WORKDAY(TODAY(),-1),"ddmm")&"!B:C"),2,FALSE)

Hello batchy - they both work for me (although I had to change "mmdd" in Andrew's to "ddmm").

See attached where I used my suggestion in Template C2:C3 and a modified version of Andrew's in D2:D3.

Both will also work on a Monday to get the previous Friday (you can test that by changing TODAY() in the formula to a cell reference and putting various dates in that cell to see what happens......of course you'll need the relevant sheets for any test dates)

My suggestion with WORKDAY can also be used to exclude holiday dates if you wish (you need a list of holidays), see WORKDAY help for more....

Many thanks for the speedy responce.

However still having issues..

I have input the formula provided on the 5th so in theory it should be pulling the data from the 4th but i get #REF error...

EG.
If you look at worksheet "0510" and cell C2. The formula within this cell is pulling data from 0410 but I have to manually input 0410 and need to change this everytime I create a new day. I have inputted the formulas provided and getting #REF.

You can see this error from the attachment also provided by Daddy.

That's because today is the 6th, so it evalutes TODAY()-1 to 0511 and looks up on that sheet, which is the same sheet.

Do you actually want it to work on the date from the sheet title and not today's date?

Edited to add: If so I'm going to have to concede to Daddylonglegs and use the WORKDAY function...

=VLOOKUP(B2,INDIRECT(TEXT(WORKDAY(DATE(YEAR(TODAY()),VALUE(RIGHT(CELL("FileName",A1),2)),VALUE(LEFT(RIGHT(CELL("Filename",A1),4),2))),-1),"ddmm") & "!B:C"),2,FALSE)

WORKDAY function allows you to exclude holidays too. The standard function

=WORKDAY(A1,B1,Holidays)

Which formula are you using?

In my original you'd put holidays in like this

=VLOOKUP(B2,INDIRECT(TEXT(WORKDAY(TODAY(),-1,holiday_range),"ddmm")&"!B:C"),2,FALSE)

Where holiday_range is a named range where you list all holiday dates