attempting to format a color pattern in tab 3, based tab 2 and tab

[mturboman]

I have many cells of data on tab 1 (Actual data), it is replicated on tab 2
but with a different data set (Budget data), and tab 3 is the difference
between those first 2 tabs and I want each of the cells on tab 3 to evaluate
against tabs 1 and 2. In tab 3, if the same cell, say c3, in tab 1 is less
than ABS of 1, AND the same cell in tab 2 is greater than ABS of 1, then the
same cell, say C3, on tab 3 I want a color pattern of Blue, else no color
pattern.

Thanks for your help.

 

[Vital_ar]

Dear Muturboman,
You can do this with Conditional Formatting ("Use a formula to determine
which cells to format" in Excel 2007 or "Formula is" in Excel 2003) in Tab3
i.e c3 using a formula like
=AND(ABS($c$1)<=1,ABS($b$1)>=1)

Hope this is the which you required (From your query).

Regards,

 

[mturboman]

Hey Vital_ar,

That formula will not provide the result I am looking for on tab 3, as I
need tab 3 to look at tab 1 and tab 2 to evaluate the format change, and help
says you can not use another tab in a conditional format or formula. The
formula you provided below if I use in tab 3 does not look at tab 1 or 2 and
that is the issue I have, so I do not know how this situation I have would be
resolved.

Do you have any other ideas?

Thanks,
mturboman

 

[Vital_ar]

Hai Mturboman,
Sorry, I thought tab means cell. Also do u mean ABS reers to the ABS
function.
Ofcourse the formula which i have given is wrong. The correct formula is
given below
=AND(ABS(a1)<=1,ABS(b1)>=1)
(Use it in Conditional formating - Formula is, in the cell c1 and in the
format tab change the color you want). If the a1 value is less than 1 and b1
value is greater than 1 surely the format i.e font or the background color
which you choose in the format tab will surely appear.

Thanks

 

[mturboman]

Hey Vital_ar,

Thanks again for your response, but I think I am still not completely clear
with you on what I am looking to accomplish. Yes i do wish to use the ABS
function. You are close, but I want to format the cell in Tab 3, Cell A1,
based on the criteria related to Tab 1, Cell A1, and Tab 2, Cell A1. For
instance, if I use your formula below I will try to show you what I want to
do, but the cell formula does not allow you to reference a different tab than
the tab you are in, and that is why I am asking the question, as I don't know
how else to to what I am trying to accomplish. If I modify your formula to
what I want, i think it would look something like this.

Formula in {Tab 3}[Cell] A1
=AND(ABS({Tab 1} [Cell]A1)<=1,ABS({Tab 2} [Cell]A1)>=1)

Does this help explain a little better what I am looking for? I just don't
know how to accomplish this, as you can not reference Tab 1 or Tab 2 when
writing a formula in Cell A1 of Tab 3, per Excel 2003 help.

Let me know if you have anymore ideas.

Thanks,
Mturboman

 

[Vital_ar]

Hai Mturboman,
Try this
Assuming that tab1 is sheet1 and tab2 is sheet2
First define 4 names like this (Insert-->Name-->Define)

list1: =(INDIRECT(ADDRESS(ROW(),COLUMN(),,,"sheet1")))
list2: =(INDIRECT(ADDRESS(ROW(),COLUMN(),,,"sheet2")))
list3: =ABS(list1)
list4: =ABS(list2)

**Here Sheet1 & sheet2 refers the names of the sheet (Tab)

Then in the tab3 i.e sheet3, select "conditional formatting" where "formula
is" and type this formula
=AND(list3<1,list4>1)
and select the format which you want to display.

Here i used "Indirect" Function because if you use this conditional
formatting in a1 cell in sheet 3 (Tab3) it automatically selects the a1 cells
in Sheet1 (Tab 1) and sheet 2 (Tab 2) and so on.

--
Regards

Hey Vital_ar,

Thanks again for your response, but I think I am still not completely clear
with you on what I am looking to accomplish. Yes i do wish to use the ABS
function. You are close, but I want to format the cell in Tab 3, Cell A1,
based on the criteria related to Tab 1, Cell A1, and Tab 2, Cell A1. For
instance, if I use your formula below I will try to show you what I want to
do, but the cell formula does not allow you to reference a different tab than
the tab you are in, and that is why I am asking the question, as I don't know
how else to to what I am trying to accomplish. If I modify your formula to
what I want, i think it would look something like this.

Formula in {Tab 3}[Cell] A1
=AND(ABS({Tab 1} [Cell]A1)<=1,ABS({Tab 2} [Cell]A1)>=1)

Does this help explain a little better what I am looking for? I just don't
know how to accomplish this, as you can not reference Tab 1 or Tab 2 when
writing a formula in Cell A1 of Tab 3, per Excel 2003 help.

Let me know if you have anymore ideas.

Thanks,
Mturboman

Hai Mturboman,
Sorry, I thought tab means cell. Also do u mean ABS reers to the ABS
function.
Ofcourse the formula which i have given is wrong. The correct formula is
given below
=AND(ABS(a1)<=1,ABS(b1)>=1)
(Use it in Conditional formating - Formula is, in the cell c1 and in the
format tab change the color you want). If the a1 value is less than 1 and b1
value is greater than 1 surely the format i.e font or the background color
which you choose in the format tab will surely appear.

Thanks