Excel help (I KNOW ITS A FOOTBALL FORUM, BUT I KNOW SOMEONE HAS BRAINS ON HERE)

Hi,

I could do with some help, i am wanting to do a formula to do the following.

in sheet 1, cell b2, i have the word "monday", b3 "tuesday" etc, but on sheet 2 i have in cell g8, "06/08/12" cell g9, 07/08/12 etc.

what i want to do is input some information in sheet 2 at the side of the dates (cells h8,h9) , then say for 06/08/12 which was monday i want the information i have put at the side of that date to appear at the side of the day.

obviously though when the next day comes i want the monday stuff to stay at the side of the 06/08/12, and then tuesdays to appear at side of tuesdays.

also the week later i want mondays to appear with the present mondays info and not the previous weeks.

if this needs moving fair enough move it or ask me to delete it and i will.

up the reds... see you all at Rochdale tomorrow

 

OK, I can help. One question - I assume you've typed in Monday, Tuesday, Wednesday etc. If it's Tuesday the 7th, do you want the entry above for Monday to show the Monday just gone (6th) or the next Monday (13th)?

 

Look up (chuckle) the VLOOKUP() function.

 

i would like it to show the 6th, but then once the week is complete for the info to go blank and then start for the 13th,14th etc

yes i have typed in the days monday tuesday etc.

thanks for your help, very new to all these vlookups etc, but picking them up really easy

 

Set your cell (B2) to the date, and then use "Format Cells" and custom format "dddd". It will store the date (6/8/12, etc) but display it as a day (Monday, etc).

 

what happens the week later though when the date is the 13th, it will still says 6th, these fields i dont want to be editing what so ever.

 

Let me know if this works, drag down from C2.

Code:

=IF($B2="Monday",VLOOKUP(TODAY()+(1-WEEKDAY(TODAY(),2)),Sheet2!$G$8:$H$26,2,0),IF($B2="Tuesday",VLOOKUP(TODAY()+(2-WEEKDAY(TODAY(),2)),Sheet2!$G$8:$H$26,2,0),IF($B2="Wednesday",VLOOKUP(TODAY()+(3-WEEKDAY(TODAY(),2)),Sheet2!$G$8:$H$26,2,0),IF($B2="Thursday",VLOOKUP(TODAY()+(4-WEEKDAY(TODAY(),2)),Sheet2!$G$8:$H$26,2,0),IF($B2="Friday",VLOOKUP(TODAY()+(5-WEEKDAY(TODAY(),2)),Sheet2!$G$8:$H$26,2,0),IF($B2="Saturday",VLOOKUP(TODAY()+(6-WEEKDAY(TODAY(),2)),Sheet2!$G$8:$H$26,2,0),IF($B2="Sunday",VLOOKUP(TODAY()+(7-WEEKDAY(TODAY(),2)),Sheet2!$G$8:$H$26,2,0),"Error")))))))

The $G$8: $H$26 is the lookup for the date and note. Extend this to whatever you need it to be.

 

cheers pal thanks for the above, i was round about that when doing it myself but couldnt get the weekday code.

its bringing up an error for "H$26"

 

$H$26 is the last cell of your day "notes". Just do a find/replace on the entire formula.

 

ive filled all to h26 with data and it isnt bringing it up, surely i must have done something wrong.

thanks though, much appreciated.