H

I have a workbook containing worksheets "Feb" "Apr" "Jun" "Aug" "Oct" & "Aug". There is a merged sheet called "Consolidated". On the consolidated sheet, I have information regarding all clients contined on the other sheets which is pulled in from each sheet

Some addresses are pulled from "Dec" and where there is no address in this sheet I work backwards until all addresses have been pulled in. I work backwards because "Dec" contains all the latest known addresses which may have been updated frim the other worksheets due to clients moving etc

The formulas I use for each part of the address is as follows

=IF(ISERROR(VLOOKUP($A2,'Dec'!$A$2:$E$447,5,FALSE) ),0,VLOOKUP($A2,'Dec'!$A$2:$E$447,5,FALSE)

This works fine but now I have to have a way to know which record is pulling from which sheet. Is there a way to add something to this code to make the text or cell a particular colour. I would like to have data pulled from "Dec" in red and data pulled from "Oct" in blue etc.

I hope this makes sense

Thanks in advanc

Malcolm Davidson

I don't believe you can do that with a worksheet
function. You would have to do it in VBA. But if you can
be a little flexible...You could add a column adjacent to
your lookup formula, hide it, and in that column have a
similiar lookup that returns a text constant. For example:

=IF(ISERROR(VLOOKUP($A2,'Dec'!$A$2:$E$447,5,FALSE) ),IF
(ISERROR(VLOOKUP($A2, 'Nov'!$A$2:$E$447, 5, FALSE)), IF
(.......<more sheets), "Nov"),"Dec")

Then you can evaluate that second column using the
conditional format feature. Using the "Formula is" option
in the condition, refer to the hidden column. For
example, =B1="Dec", as your value - then set your color in
the conditional.

Of course, you're only allowed three conditions in the
conditional format. :(

Good luck.

-Brad

-----Original Message-----
Hi

I have a workbook containing
worksheets "Feb" "Apr" "Jun" "Aug" "Oct" & "Aug". There
is a merged sheet called "Consolidated". On the
consolidated sheet, I have information regarding all
clients contined on the other sheets which is pulled in
from each sheet.

Some addresses are pulled from "Dec" and where there is
no address in this sheet I work backwards until all
addresses have been pulled in. I work backwards
because "Dec" contains all the latest known addresses
which may have been updated frim the other worksheets due
to clients moving etc.

The formulas I use for each part of the address is as
follows.

=IF(ISERROR(VLOOKUP($A2,'Dec'!
$A$2:$E$447,5,FALSE)),0,VLOOKUP($A2,'Dec'!
$A$2:$E$447,5,FALSE))

This works fine but now I have to have a way to know
which record is pulling from which sheet. Is there a way
to add something to this code to make the text or cell a
particular colour. I would like to have data pulled
from "Dec" in red and data pulled from "Oct" in blue etc..

I hope this makes sense.

Thanks in advance

Malcolm Davidson
.