Displaying text from another worksheet

[AshleyWebb]

Hi all,

My first post here.

I'm doctoring an existing spread sheet (downloaded from the internet) for booking IT equipment at work. There are two tabs, booking and availability. In the bookings tab the user will enter the dates and equipment they wish to use. Does anyone know how to pull the text from the E column of the bookings sheet so that it is displayed in white text on the corresponding booking in the availability sheet?

There is already a formula in the cells of the availability sheet that blocks out the booking in red. I can't work out what or where to add in order to display the text.

Thanks in advance.

Ashley

[ChemistB]

In H7 copied across and down

=IFERROR(INDEX(Booking!$E$5:$E$997, IF(SUMPRODUCT((Booking!$F$5:$F$997<=H$6)*(Booking!$G$5:$G$997>=H$6)* (Booking!$H$5:$H$997=$C7)*(Booking!$I$5:$I$997=$F7))=1,SUMPRODUCT(( Booking!$F$5:$F$997<=H$6)*(Booking!$G$5:$G$997>=H$6)*(Booking!$H$5:$H$997=$C7)* (Booking!$I$5:$I$997=$F7)*(ROW($A$5:$A$997)-ROW($A$5)+1)),"")),"")

Then using conditional formatting, I changed the rules
For blue
Cell value = ""
For Red, use formula
=LEN(H7)>0 format with white text
Does that work for you?

[TMS]

The formula you have is designed to produce a one (1), a zero (0) or a negative number. That's then used in Conditional Formatting to set the colour of the cell.

Not sure how you would include the name in the cell ... but if you can you'll need to adapt the CF formulae.

Regards, TMS

[TMS]

@ChemistB: ah, so that's how you can do it

[AshleyWebb]

Thank you very much! That seems to have done the trick. I'd never have worked that out. :-)

[AshleyWebb]

Further to the help I received on this spread sheet.

I wondered if anyone can get the conditional formatting to work so that when a double booking is made for apiece of IT equipment the cells show yellow. There is a rule for this in the formatting but it doesn't seem to work. If a double booking is made nothing happens at all.

Many thanks.