Conditional formatting based on range of cells in different sheet

Please post a small sample file showing what you expect.

Two problems.
1) If you want to put conditional formatting on the sheet "2" and uses the formula all cells that sheet, will be faced with circular reference.
2) If You use all the cells of a sheet may be faced with the error Excel does not have enough resources for ...

Formula:

Please Login or Register  to view this content.

Two problems.
1) If you want to put conditional formatting on the sheet "2" and uses the formula all cells that sheet, will be faced with circular reference.
2) If You use all the cells of a sheet may be faced with the error Excel does not have enough resources for ...
If you do not use sheet "2" formula can be

Formula:

Please Login or Register  to view this content.

Thank you for the fast answer.
I can use sheet 3 for the formula with cell range in sheets 1 and 2. I don't need the entire range. I need it from A1:J200. The formula you wrote give me an error. Could you please help me to make it with sheets 1 and 2 and the range I mentioned.

I have seats which I sell to concrete people. There will be a list with concrete people names on sheets 1 and 2. When the seat is occupied we put the number of a seat on a row of a person name. In sheet 3 there will be a scheme with all seats. So I expected when I look on sheet 3 to have green "free" sectors and red "occupied" sectors, so I can quickly managed free and occupied. The range I will use on sheets 1 and 2 is from A1:J200.
Thank you. I hope you can help me.

Hi Glenn,

I made a draft of what it will be.
Here you are.Seats.xlsx

In your example the formula is

Formula:

Please Login or Register  to view this content.

The condicional formating is applied to SCHEME!B3E32.
I attached your changed file

I don't think you can use COUNTIF as it fills in a range rather than individual cells (seats). I believe (not being an expert) that you need to MATCH each individual cell (seat).

The following (simplistic) rules appear to work but something more generic would be better as it requires applying these to each column:

=MATCH($C$2&A3,OWNERS!$F$2:$F$30,0) for cells C3:c30 on SCHEME
=MATCH($C$2&A3,HOTELl!$C$2:$C$30,0) for cells C3:C30

I tried an OR with the above but did nor work (for me!)


Happy to be proved wrong!

You are right, countif fills range of cells. I need individual. I tried Jose Augusto wrote, but it fills range of cells. I hope there is a decision. The number 1.1.1 is block 1, sector 1, seat 1.

I can make it easier - only with block and seat number. I redrafted. Seats.xlsx

Originally Posted by Glenn Kennedy

This is probably still wrong, as I'm not clear what's a block and what's a sector... but. Take a look at this.

There is one FUNDAMENTAL flaw that, I think, I can address later. There is NOTHING to stop double booking of the same seat. But, let's get the formatting right first.

When I allow "enable editing" and your fomulas are gone. I make it only with blocks and seat numbers. The block number is first, the second is seat number. It will be great if we could avoid double booking. Thank you for the efforts!

Originally Posted by Glenn Kennedy

OK. It's clear. Check this out carefully and see if it's doing what you want.

then we can address double-booking issues.

Enter the booking as normal, don't touch the pale green cells. Just check that the cell reference in the relevant row (N.B. I MEAN sheet reference NOT table reference) has turned red.

YES!!! This is exactly what I need!!! When we address double booking issues, is it possible for you to wait for me about 2-3 days to make the real file and to put the right formulas in it, so I can be sure it's working properly? Unfortunately, what you did it's too hard for me and I not sure I can transfer it... Thank you again.

The same happens to me. Why?

Originally Posted by Glenn Kennedy

I don't understand what you mean by:

"When I allow "enable editing" and your fomulas are gone. I make it only with blocks and seat numbers. "

If this is still an issue with the latest version, please explain again!!

The new file you send me is OK!

Last file is good for me.
Thanks

Attached in sample solution based on Glenn's approach: I take no credit for the solution.

Apologies to Glenn who had already posted the solution: I guess we crossed in the post. For my part I learnt a lot from Glenn's posting ... so thank you.

Glenn,

There was nothing wrong with your first version - it was just what was required. I was simply changing the Address calculation to reflect the new formatting . The solution was all yours so again my apologies if I inadvertently implied your solution was wrong.

John

Originally Posted by Glenn Kennedy

Hi again. I thought that I'd deal with the double-booking bit now; while it's fresh in my mind. In your final version, the green cells can be hidden and I may use the Availability result as Data Validation to prevent entry of a double booking. We'll wait and see how you get on with adapting it to your real sheet.

Glenn, I will be ready with the file after 6-7 hours. I would like to discuss with you another issue. I was decided when I am ready with the file, to copy the data in google sheets. This I want to make, because thru wi-fi connection and google drive, I will be able to update online, even without need of closing the file. I'd tried this previous days. So 5 people could work simultaneously on the area. The problem is that when I tried to transfer your file in google sheets, it says that conditional formatting could not work in multiple sheets. Do you have an idea how could I solve this problem.

Originally Posted by Glenn Kennedy

I don't use google sheets; so I don't know what restrictions there are on CF. Why not stick to Excel? What's so special bout Google sheets?

What does it mean stick to excel? There is nothing special about google sheets. I need no matter which cloud service, so we can use this file in cloud space and to update it at each moment. This is a very big area with seats (beach with 700 sunbeds) and all day we need to know which one is occupied or free.

Originally Posted by Glenn Kennedy

See how this works... I have hidden the column with the formulas on each of the three sheets.

Glenn, thank you so much!!! It's absollutely great!!! I am so grateful for your efforts.

I believe the booking logic assumes entries in "OW OASIS I" is in row sequence: an entry in row 20 is accepted but if I now put the same entry in row 15 then row 15 is designated as "OK" and row 20 as "Already Booked" when in reality it is the reverse.

Should the COUNTIF no check the whole column as it does for the other sheets?

Am I correct in my assumption about entry sequence?

FWIW, here's a screen snip of my SCHEME sheet..

Seating.gif