Excel 2003 Conditional Formatting from Other Worksheet Value

[AndrewFerr]

I have a series of tabs with data, 25 columns by 600 rows, all with numbers. A cell should be red and lined out if its' corresponding cell on the very last tab is less than 50. I've tried to name range the area on the last tab (RawBuyers) and use it in a Formula Is CF on the first worksheet, but I get errors.

I'm seeking help with a CF formula that I can apply to each tabs data range.

I've seen threads similar to my issue, so I apologize if this is redundant (but nothing I've found is doing the trick.)

 

[Gerald Higgins]

Hi Andrew, welcome to the board.
In 2003, you can not do this directly.
You can do it using named ranges, but I've never done it msyelf and I guess you might need a separate range for each cell, which might be a problem.

Another way round it is to have a helper area on the sheet that repeats the values from the final tab. That way CF will work fine.

 

[pgc01]

Hi Andrew
Welcome to the board

Sorry, I'm not sure I understood correctly.

Please confirm.

- you have a master table in a worksheet, let's call the worksheet LastTab

- You want to paint for example B4 in Sheet3 if the value of LastTab!B4 is less than 50.

Is this right?

 

[AndrewFerr]

Yes; that's correct. But like the first Reply Post stated, I may have to create a reference for every cell in LastTab. Yikes.

 

[AndrewFerr]

I may take Gerald up on his idea. It may be just as easy to copy the base sizes (last tab data) onto each of the sheets needing the CF. Copy-paste a range to the right on multiple tabs is much easier than a named range for each of 25X600 cells!

 

[Gerald Higgins]

Don't copy the data from the last tab, use formulas to repeat the values, so that if the underlying data changes, your CF takes account of that.

 

[pgc01]

Hi

If I understood correctly your problem is simple.

Try this:

- In any worksheet select cell B2

- whith B2 selected define a named formula for the corresponding cell in the LastTab worksheet

Name: LTCell

Refers To: =LastTab!B2<50

- now still with B2 Selected define the Conditional Formatting with the formula:

=LTCell

That's it. Now you can copy the format to any other cell in any other worksheet.

Remark: replace the name LastTab with the name of the worksheet where you have the master table

 

[wsjackman]

cancell

 

[wsjackman]

have rawbuyers refer to A1:last cell of interest in last tab

select all the other sheets highlight the cells you wish to conditionally format
use conditionally format formula

=index(rawbuyers,row(a1),column(a1))<50

format as wanted

change the a1 in the conditional format formula to be the first cell you wish to format

try this on a copy

 

[AndrewFerr]

Sweet Mother of formatting! This worked! I MUST make it a point to understand the =index operator. All hail wsjackman! Thanks a $MM!

Andrew