Conditional formatting and data in another sheet

[xlns]

Hi,

I want to graphically show periods of time for resources - available, confirmed etc. My solution now is a VBA based one, where the user selects two cells in the overview, indicating the start and end date. A form is shown, the user picks the status and the data is stored in a data sheet:

1 - Data.jpg

A shape is also inserted in the overview sheet, indicating that period of time:

2 - Overview.jpg

(The shape may also be deleted by clicking on it and the corresponding data is removed from the data sheet - the code reads where the shape is located (dates and resource) and identifies the data through a string, which is a unique combination of dates, resource and status.)

Now, I would like to reverse it - without VBA - and have the user entering the data and using a formula to pull the data into the overview and format the cells according to the status, i.e. populating the data sheet and have the formula pull the data into the overview. I guess I could have done this with a diagram but prefer having it all presented in the sheet directly.

How should the formula in the overview look like?

Please get back if it's unclear what I'm after ;-)

[Richard Buttrey]

Hi, and welcome to the forum.

Please upload the workbook. Pictures are rarely much use.

[xlns]

Thanks Richard

[xlns]

Thanks Richard

Here we go: Resource availability - ver 1a.xlsx

[sktneer]

Select the range B2:AF8 and use the following formulas for various status to make a New Rule for conditional formatting and set the format as per your choice.

For status Available:

Please Login or Register  to view this content.

For status Confirmed:

Please Login or Register  to view this content.

Is this what you are trying to achieve?

[xlns]

Definitely, sktneer - thank you so much!

I had to tweak the formula a bit to fit the European formula standard and, bang, it worked!

Again, thanks

[sktneer]

You're welcome. Glad it worked.
Thanks for the feedback.

If that takes care of your original question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.