Getting values from a row based on a value between dates (1 Viewer)

[Kwt]

I'm trying to make an excel spreadsheet where i can see which rooms are booked on which day of the month.


the formula that i have isn't right and i want to get de customer from the customer column and not hard coded and the same with the rooms.
the formula i used is
'=IF(AND($F2>=MIN($C$2:$C$10);$F2<=MAX($D$2:$D$10);$B$2=$G$1);$A$2;"Free")
what i want to do is get the customer and the room where the date is between the from and till date.
Does anyone have a suggestion for this ?

 

[godsspeed]

shouldnt the formula have a comma (,) between the 2 condtions you have inside the AND function? ..instead of the (?? and everywhere else? lol i believe commas have to be used to separate the different arguments/parts of a function. also, it appears that you have too many arguments...
"AND($F2>=MIN($C$2:$C$10);$F2<=MAX($D$2:$D$10" =condition
"$B$2=$G$1" = Value if True (this wont return anything, btw)
"$A$2" = Value if False (this will simply return the value in A2
what is the last part intended to do??


as far as getting the Customer and date: are these results going to be in separate columns? can you show or upload a sample of the layout?
if im understanding what you're describing, i would think a vlookup in the Value if True part may work. ....seeing the layout would help

 

[Kwt]

I want something like this

"AND($F2>=MIN($C$2:$C$10);$F2<=MAX($D$2:$D$10 ;$B$2=$G$1 =condition ($B$2 needs to be variable a value from column b from a row where date in column F is between from and till date)
"$A$2" = Value if True (needs to be variable a value from column A from a row where date in column F is between from and till date)
"Free" = Value if False (this will simply return the value in A2)

 

[Kwt]

"," dont work in my excel spreadsheet can only use ";"

 

[Kwt]

.:banghead:

 

[Kwt]

need only 6 more

 

[Kwt]

need only 5 more i think

 

[Kwt]

only 4 more

 

[Kwt]

only 3 more

 

[Kwt]

maybe only 1 more

 

[Kwt]

i think i can post a picture now

 

[godsspeed]

IF(AND($F2>=MIN($C$2:$C$10);$F2<=MAX($D$2:$D$10);$B$2=$G$1);$A$2;"Free")

try that? added a ")" and removed a space from your previous formula...


any luck with the pic?

 

[Brianwarnock]

http://www.access-programmers.co.uk/forums/showthread.php?t=223479

The above post tells you that you can post a zipped file without having to have 10 posts.

Brian

 

[Kwt]

I want something like this

"AND($F2>=MIN($C$2:$C$10);$F2<=MAX($D$2:$D$10 ;$B$2=$G$1 =condition ($B$2 needs to be variable a value from column b from a row where date in column F is between from and till date)
"$A$2" = Value if True (needs to be variable a value from column A from a row where date in column F is between from and till date)
"Free" = Value if False

Does anyone know a formula or example ?
so that i can get the row based on the row where the day(example: 2 april 2013) is between the from and till date(1 april 2013 - 5 april 2013)
and then use that row to get the customer and room value.

 

[Brianwarnock]

It appears that each sheet represents a month, so why not have the dates down column A starting in row 2 and the rooms in row 1 starting in col B then the customers can be entered in the correct date/room combination , fill down will make that swift, and you will instantly have what you are trying to achieve. You could apply conditional formatting to the sheet to highlight the empty rooms.

Brian

 

[Kwt]

@Brianwarnock,

I'm sorry but i don't really understand what you mean.

About the image: i did use conditional formatting thats why when there is a customer the room will be red and if its free the room is green.

and column A - D is supposed to be a sheet where
all months can be inserted (all from - till dates)

and column F - I is as you said a sheet per month

 

[Brianwarnock]

I'm suggesting that you only need the sheets that contain F to I and that the data is entered directly there, you need to reference those sheets to see if the rooms are free so just enter the data in them, a blank cell would show that a room/date combination is free, it could still be green.

Brian

 

[godsspeed]

$B$2 will cause the formula to only and always look at that specified cell..... no matter where you move the formula (filling down to rows below)........... thats going to cause a bit of an issue wouldnt it?

 

[NBVC]

I am not sure why you have H2 and H3 filled with "Customer2" if Customer2 occupied room from Apr 3rd to 5th?

If that is an error and should show "Free", then try this formula in G2:

=IFERROR(INDEX($A$2:$A$10,MATCH(1,INDEX(($B$2:$B$10=G$1)*($C$2:$C$10<=$F2)*($D$2:$D$10>=$F1),0),0)),"Free")

copied down and across