this should be easy but i not quite sure
Posted by tessa gazzard on January 08, 2002 4:38 PM
Hi i am doing a spreadsheet for my kids to show them some of excel usages. any way i have a sheet that work out there age in years monthes ect one that work out age in days and the last one is day of the week born on ect day of the week is in f coloum so in G depending on what day is displayed in F i want excel to put the corrosponding line from the nursery rymem mondays child is fair of face tuesdays child is ect
I have set up a table further over on the sheet with monday in one coloum and the corrosponding words from the ryme in the next i have done this with all the days of the week so depending on the result of day of week born on, in the next coloum G i would like excel to print the right verse from the table so how do i do this my table is in a range J & K 22-28
Posted by Derek on January 08, 2002 5:16 PM
Tessa
I assume the day displayed in column F is the date in a cell formatted as "dddd". If so then
put this formula in cell G1 and scroll it down:
=VLOOKUP(WEEKDAY(F1),$J$22:$K$28,2)
In your list replace the day names in column J with the day number, starting with 1 for Sunday.
Hope this helps
Derek
Posted by Tom Dickinson on January 08, 2002 5:44 PM
=OFFSET($K$21,WEEKDAY(F1),0) (NT)
Posted by tessa gazzard on January 08, 2002 5:47 PM
thuursday displayed as saturday any ideals
Posted by tessa gazzard on January 08, 2002 5:57 PM
Re: =OFFSET($K$21,WEEKDAY(F1),0) (NT)
dont understand this is my look up table
J22 K22
2 Mondays child is fair of face.
3 Tuesdays child is full of grace.
4 Wednesdays child is full of love.
5 Thursdays child has far to go.
6 Fridays child works hard for a living.
7 Saturdays child is loving and giving.
1 Sundays child is good and friendly
And this is my main part
ROWs F coloum G
4 Thursday lookup formuler goes here
5 Thursday
6 Thursday
7 Tuesday
8 Monday
Posted by tessa gazzard on January 08, 2002 6:04 PM
where do i put this offset formuler
Posted by Derek on January 09, 2002 12:31 AM
your list must go 1,2,3,4,5,6,7 so Sunday must be on top
Tessa
With vlookup the list MUST be in increasing numerical order. It searches from the top down till it finds the match. (If a number is absent it is likely to give you the answer to the last number it reached before the missing one).
Derek
dont understand this is my look up table
