If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel formula question - nested VLOOKUP?
So far, I have a VLOOKUP function that searches another sheet and pulls
the data from a cell matching a name located in row 4 of the same column, and converts the result from a number of seconds to mm:ss. Cell B27's contents (this checks the name in B4 and looks up their number in col C on the sheet with data for that day): =VLOOKUP(B4,'6-23-2004'!A1:C70,3,FALSE)/60/60/24 This works fine. However, I have an array here.. it'll take me forever to write all this out manually, and there's got to be an easier way. Column A contains all the dates for the month (6-1-2004, 6-2-2004, etc.) How can I modify that formula so it looks on a worksheet (in the same workbook is OK, i can hide the sheets) for that day? Each row is related to one day, and each column related to one person. I'd like it to go like.. =VLOOKUP(B4,A27!A1:C70,3,FALSE)/60/60/24 ..and have it look in B4 for the name, A27 for the date (the worksheet will be named the same as the date,) and then do the vlookup in A1:C70, find the date in col C, etc. Is that possible? Can I use a nested function to return the date located in col A to search a worksheet by the same name? --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
Excel formula question - nested VLOOKUP?
|
#3
|
|||
|
|||
Excel formula question - nested VLOOKUP?
Hmm.. Maybe I'm not getting the syntax here?
Col A has a list of dates like so (just text labels.. it would be easier to have actual mm-dd-yyyy labels but I can't get it to display like "mm-dd-yyyy", only "mm/dd/yyyy" and i can't use / in worksheet or workbook names) 6-1-2004 6-2-2004 ... 6-30-2004 I have other worksheets named 6-1-2004, 6-2-2004, etc, with the relevant data (names in Col A, numbers I pull in Col C) B27 is: =VLOOKUP(B$4,'6-23-2004'!$A$1:$C$70,3,FALSE)/60/60/24 B28 is: =VLOOKUP(B$4,'6-24-2004'!$A$1:$C$70,3,FALSE)/60/60/24 I tried using INDIRECT in there a few ways, but can't get it to add up right. I want to replace the worksheet name (which i have to type manually right now - clumsy!!) with a function referring $A27, $A28, etc, and using that text label to refer to the correct worksheet. The formula checker says that A27 = 6-23-2004 as it should, but then says that the formula result is Volatile, and then returns an error. I can't seem to figure the syntax out... sorry if I'm being dense, it's been a long day. --- Message posted from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
Excel formula question - nested VLOOKUP?
"brightshadow " wrote...
Hmm.. Maybe I'm not getting the syntax here? Col A has a list of dates like so (just text labels.. it would be easier to have actual mm-dd-yyyy labels but I can't get it to display like "mm-dd-yyyy", only "mm/dd/yyyy" and i can't use / in worksheet or workbook names) 6-1-2004 6-2-2004 .. 6-30-2004 ? You *CAN* format date entries as mm-dd-yyyy, but you may need to do so as a custom number format. I have other worksheets named 6-1-2004, 6-2-2004, etc, with the relevant data (names in Col A, numbers I pull in Col C) B27 is: =VLOOKUP(B$4,'6-23-2004'!$A$1:$C$70,3,FALSE)/60/60/24 B28 is: =VLOOKUP(B$4,'6-24-2004'!$A$1:$C$70,3,FALSE)/60/60/24 I tried using INDIRECT in there a few ways, but can't get it to add up right. I want to replace the worksheet name (which i have to type manually right now - clumsy!!) with a function referring $A27, $A28, etc, and using that text label to refer to the correct worksheet. ... If A27 were "6-23-2004" and A28 were "6-24-2004", try the following. B27: =VLOOKUP(B$4,INDIRECT("'"&A27"&'!$A$1:$C$70"),3,FA LSE)/60/60/24 then fill B27 into B28. -- To top-post is human, to bottom-post and snip is sublime. |
#5
|
|||
|
|||
Excel formula question - nested VLOOKUP?
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel question - formula design | brightshadow | General Discussion | 2 | June 28th, 2004 11:26 PM |
Question about Absolute reference formula | Maria | General Discussion | 3 | June 23rd, 2004 06:27 PM |
Excel - VLOOKUP Question - Urgent | ajw150 | General Discussion | 6 | June 23rd, 2004 06:25 PM |
Question about an argument, in an OFFSET dynamic range formula | Terry B. | Worksheet Functions | 6 | December 10th, 2003 10:53 PM |
nested formula help needed | Worksheet Functions | 0 | October 14th, 2003 04:17 PM |