Thread: If function to separate the days from a date field

i have a complex question... this is sheet 2
Guest Name 1 2 3 4 5 6
Pat Gullufsen 99 99 99 99
Jamie Ortikoff 89
Timothy Hunyor 99 99

This is sheet 1
Guest Name Room # Folio # Ck in Ck out Total Room Rate
Pat Gullufsen 113 126093 03/01/10 03/05/10 4 99
Jamie Ortikoff 208 126859 03/01/10 03/02/10 1 89
Timothy Hunyor 115 126863 03/03/10 03/05/10 2 99


How can i make it automaticaly insert the room rate into the corrisponding Date on sheet 1

For sheet2, starting in B2, =IF(Vlookup(A2,Sheet1!$A$2:$F$4,6,0)>=B$1,Vlookup(A2,Sheet1!$A$2:$G$4,7,0),"")

can you break down that formula even more for me so i can learn what it all means... Thanks

Vlookup looks up a value (1st argument, in this case the name in A2) in the first column of an array (2nd argument), then returns the value in the specified column (3rd argument). The fourth argument tells it to look for an exact match if False or 0 and the closest match without going over if 1 or True. With names, 0 is almost always required.

The formula itself basically tells it to see if the number of nights for a given person is less than or equal to the number of a given night (in row 1) and, if it is, the formula looks up the room rate for that person and returns it. Otherwise, it returns blank.

Because of the $ in the formula, it can be copied down as necessary while still maintaining the appropriate references for the array and row 1. Just keep in mind that it will not work for multiple occurrences of the same name. It will look up and return the result from the first instance of the name only.

that helps me some... but what if the date starts on the 3rd and ends on the 4th will it take the room rate and ony put it in the correct cell like

Guest Name 1 2 3 4 5 6
Pat Gullufsen 0 0 99 0 0 0

I misunderstood your requirements. Those looked to me like the number of days in the stay, rather than the dates of a month.

It brings up additional questions. Will this sheet start over each new month? Are these numbers shortened from actual dates (i.e., your real sheet reads 3/1, 3/2, etc.)? It might be best to post a dummy workbook if you can, just to iron out all issues quickly.

here are a couple links to pics of the worksheet.

and yes sheet 2 the numbers at the top represent the day of the week so like if sheet 1 says someone was here on the 5th only than that is the only cell to fill in the room rate.

http://i3.photobucket.com/albums/y73...ies/sheet1.jpg
http://i3.photobucket.com/albums/y73...ies/sheet2.jpg

Any chance you could click Go Advanced at the bottom of this page, then use the paper clip icon to attach the workbook itself? Most contributors here don't have the time to recreate your work to test their formulas.

like this?

This should help. I took the liberty of updating your formula in column A.

WOW thanks a bunch man.

hey darkyam i have a problem with it now... it wont update new info on page 2. it comes up with #na on all the days in sheet 2.

Note that the vlookup ranges only go through row 41. You'll need to adjust these ranges in the first cell down as far as you'll need, and then highlight the cell, hit Ctrl+shift+right arrow, then Ctrl+R, then Ctrl+Shift+Down, then Ctrl+D.

thanks again u the man