trying use Isblank & Vlookup when date has passed

[trial&error]

Hi

My original formula is:

=IF(ISBLANK(VLOOKUP($E39,'Players Scores'!$A$4:$AV$700,'Players Scores'!AA$3+6,FALSE)) = FALSE,VLOOKUP($E39,'Players Scores'!$A$4:$AV$700,'Players Scores'!AA$3+6,$E39),"")

I am using the above formula but would like it to get the value when the date has passed (when date has been inputed in another cell)

I have added an attachment, The formula begins at J44 to AY44 but when date has entered in the red (D44:D67)section I would like to retreive values for blue section(Q44 and onwards) and not the yellow section(J44 to P44)

This is so when players make a transfer it doesnt include the weeks before that date!!

I hope I have explained it well and the title.

Thankyou
trial&error

[NBVC]

is the "blue" section constant or date dependent... i can't see the date dependency, so I think maybe you want something like:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[trial&error]

Hi NBVC

Thankyou for your reply!!

I made a slight mistake, I attached the wrong file, not much different but still the wrong one!!

The blue section is a constant, it pulls the score from another sheet when the week number has been inputed on P3.

I tried adding the formula that you was good enough to provide to my sheet and its not working to what I need, Hopefully this attachment that I added will explain a bit easier.

Once again thankyou !!

[NBVC]

Does this work in J44, copied across?

Please Login or Register  to view this content.

[trial&error]

Hi NBVC

Im afraid its not working!

I have put certain dates above J44:AY44 into J43:AY43 to see if this makes it any easier maybe to use D44 with the dates in J43 and across.

Regards
Robert

[NBVC]

I didn't notice the dates because in my sheet it showed up as "##"....

Try:

Please Login or Register  to view this content.

Edit: Added OR statement to return blank if column D is blank....

[trial&error]

Again not quite working!

I copied the formula and dragged across, it comes up blank from J44 to X44 then at Y44 it comes up with #value and from Z44 to AY44 it gives the same value from where I dont know....
when it should retreive the value's between Q43(the transfer made) to AD43(upto week 21).
Regards
Robert

[NBVC]

Can you re-attach the workbook with the Players Scores sheet that you are looking up?.. I can't test for accuracy without it.

[NBVC]

I just noticed an error? in your original formula that I inadvertently copied over to my formula... the second Vlookup has a cell reference in the last argument instead of a FALSE....

Try:

Please Login or Register  to view this content.

If still no go, then attach new workbook.

[trial&error]

Still no go.

I have added the scoresheets to the workbook.

KR
Robert

[NBVC]

Okay, another formula reference issue... see, it's better when you attach all data necessary to test...

Try now:

Please Login or Register  to view this content.

[trial&error]

Hi NBVC

Thankyou, the problem is now solved, I will have to remember to add all sheets that count next time...

I have added to your reputation.

I am now very happy as this took me ages to try and sort out!!

Kind Regards
Robert

[NBVC]

Great! Thanks.

Can you please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save