how to combine these two formulas?

[sam94]

Hi Guys, i'm looking for a way to combine these two formulas I have worked on.

One was created yesterday using help off this forum which I am grateful for - =IF(BX9="","",MAX(DATE(BW9,1,1),DATE(BW9,1,1)-WEEKDAY(DATE(BW9,1,1),2)+(BX9-1)*7+1))

and the second is a vlookup - =VLOOKUP(B9,'I:Desktop\[MAD File.xlsx]Feuil1'!$A:$K,11,0)

The first formula uses the result from the vlookup to create the date, however is there a way where I can combine the two so I can just put them into one cell and copy this down?

Thanks,

Sam

[Bob Phillips]

Which field?

If BW9

=IF(BX9="","",MAX(DATE(VLOOKUP(B9,'I:Desktop\[MAD File.xlsx]Feuil1'!$A:$K,11,0),1,1),DATE(VLOOKUP(B9,'I:Desktop\[MAD File.xlsx]Feuil1'!$A:$K,11,0),1,1)-WEEKDAY(DATE(VLOOKUP(B9,'I:Desktop\[MAD File.xlsx]Feuil1'!$A:$K,11,0),1,1),2)+(BX9-1)*7+1))

[daddylonglegs]

You should be able to shorten your original formula so that it only needs the VLOOKUP twice, i.e. this version

=IF(BX9="","",DATE(BW9,1,MAX(0,BX9*7-WEEKDAY(DATE(BW9,1,7)))-5)

....so you can then have the two BW9 references replaced with your VLOOKUP, i.e.

=IF(BX9="","",DATE(VLOOKUP(B9,'I:Desktop\[MAD File.xlsx]Feuil1'!$A:$K,11,0),1,MAX(0,BX9*7-WEEKDAY(DATE(VLOOKUP(B9,'I:Desktop\[MAD File.xlsx]Feuil1'!$A:$K,11,0),1,7)))-5)