I was after a little help. Ive got 12 tables in a spreadsheet each one named after a month January to February. Each table has each day of the month in it and some names (lets Bob Fred and Jon).
On a another sheet I have a list of the names and in cell A1 it has the current date. Currently I use the following formula =hlookup(a1,january,3,false) with 3 being the persons name. Is there away to get the formula to look at all the months?
Last edited by NBVC; 03-12-2012 at 01:06 PM.
Try:
=HLOOKUP(A1,INDIRECT(TEXT(A1,"MMMM")),3,FALSE)
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
It doesnt seem to work. I assume the "mmmm" is the months but should there comma's?
did you try "MM"
If you have a date in A1, like say, June 10, 2012
then the formula looks for a table named "June" gotten from TEXT(A1,"MMMM") and finds the date June 10, 2012 in the first row of the table, and returns the name that is in the 3rd row, which is what I assumed you were doing with =hlookup(a1,january,3,false)... ie. looking up the date that is in A1, finding it in first row of january table, and returning 3rd row value..
This whole part: INDIRECT(TEXT(A1,"MMMM")) returns the name of the table, which corresponds to the month name from date in A1.
If not, can you post a sample workbook?
Thanks, got it working!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks