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

  • Thread starter Thread starter mturboman
  • Start date Start date
M

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.
 
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,
 
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
 
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
 
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,
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


mturboman said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top