copy color using vlookup

[Ravens Fan]

I need to copy color from my source file to a destination file. I prefer to
use vlookup if possible. I pull a report every week and I need to capture the
color of the text.

The code I'm using to pull the data is:

=IF(ISNA(VLOOKUP(A69,[Rates.xls]Sheet1!$C$3:$D$101,2,FALSE))=TRUE,"",VLOOKUP(A69,[Rates.xls]Sheet1!$C$3:$D$101,2,FALSE))

Is there a way to pull the color of the data over also?

Any help would be appreciated

 

[Jim Thomlinson]

Using Vlookup there is no way to retrieve the colour. Fuctions return values
only. They can not return formats. Conditional formatting can change the
format based on a function but for that to work there needs to be some logic
as to how the colours of your source file are determined and you are limited
(pre XL2007) to 3 conditional formats.

 

[Ravens Fan]

Thanks Jim,

There is no logic. It is based on a rate change that week.

Example:

Rate changes from 720 to 800

The data in the source file has an identifing number (say 1234). I do my
vlookup on the "1234" and pull "800" over into my destination file. Since
there was a change from the week before, the 800 will be in red. If there was
no change, the number would be green.

So, I'm trying to capture that change from the week before.

Any suggestions?
--
Baltimore Ravens
--
Baltimore Ravens

Using Vlookup there is no way to retrieve the colour. Fuctions return values
only. They can not return formats. Conditional formatting can change the
format based on a function but for that to work there needs to be some logic
as to how the colours of your source file are determined and you are limited
(pre XL2007) to 3 conditional formats.
--
HTH...

Jim Thomlinson

I need to copy color from my source file to a destination file. I prefer to
use vlookup if possible. I pull a report every week and I need to capture the
color of the text.

The code I'm using to pull the data is:

=IF(ISNA(VLOOKUP(A69,[Rates.xls]Sheet1!$C$3:$D$101,2,FALSE))=TRUE,"",VLOOKUP(A69,[Rates.xls]Sheet1!$C$3:$D$101,2,FALSE))

Is there a way to pull the color of the data over also?

Any help would be appreciated

 

[Jim Thomlinson]

Then you can do this with a conditional format. Change from cell value is to
formula and use 2 vlookups

=vlookup(1234, thisWeek, 2, false) = vlookup(1234, lastweek, 2, false)

If the values are the same then the formual will return true and you can
format the values Green. (By default make the numbers red)
--
HTH...

Jim Thomlinson

Thanks Jim,

There is no logic. It is based on a rate change that week.

Example:

Rate changes from 720 to 800

The data in the source file has an identifing number (say 1234). I do my
vlookup on the "1234" and pull "800" over into my destination file. Since
there was a change from the week before, the 800 will be in red. If there was
no change, the number would be green.

So, I'm trying to capture that change from the week before.

Any suggestions?
--
Baltimore Ravens
--
Baltimore Ravens

Using Vlookup there is no way to retrieve the colour. Fuctions return values
only. They can not return formats. Conditional formatting can change the
format based on a function but for that to work there needs to be some logic
as to how the colours of your source file are determined and you are limited
(pre XL2007) to 3 conditional formats.
--
HTH...

Jim Thomlinson

I need to copy color from my source file to a destination file. I prefer to
use vlookup if possible. I pull a report every week and I need to capture the
color of the text.

The code I'm using to pull the data is:

=IF(ISNA(VLOOKUP(A69,[Rates.xls]Sheet1!$C$3:$D$101,2,FALSE))=TRUE,"",VLOOKUP(A69,[Rates.xls]Sheet1!$C$3:$D$101,2,FALSE))

Is there a way to pull the color of the data over also?

Any help would be appreciated

 

[Ravens Fan]

Thanks again Jim,

I'll try it.
--
Baltimore Ravens

Then you can do this with a conditional format. Change from cell value is to
formula and use 2 vlookups

=vlookup(1234, thisWeek, 2, false) = vlookup(1234, lastweek, 2, false)

If the values are the same then the formual will return true and you can
format the values Green. (By default make the numbers red)
--
HTH...

Jim Thomlinson

Thanks Jim,

There is no logic. It is based on a rate change that week.

Example:

Rate changes from 720 to 800

The data in the source file has an identifing number (say 1234). I do my
vlookup on the "1234" and pull "800" over into my destination file. Since
there was a change from the week before, the 800 will be in red. If there was
no change, the number would be green.

So, I'm trying to capture that change from the week before.

Any suggestions?
--
Baltimore Ravens
--
Baltimore Ravens

Using Vlookup there is no way to retrieve the colour. Fuctions return values
only. They can not return formats. Conditional formatting can change the
format based on a function but for that to work there needs to be some logic
as to how the colours of your source file are determined and you are limited
(pre XL2007) to 3 conditional formats.
--
HTH...

Jim Thomlinson


:

I need to copy color from my source file to a destination file. I prefer to
use vlookup if possible. I pull a report every week and I need to capture the
color of the text.

The code I'm using to pull the data is:

=IF(ISNA(VLOOKUP(A69,[Rates.xls]Sheet1!$C$3:$D$101,2,FALSE))=TRUE,"",VLOOKUP(A69,[Rates.xls]Sheet1!$C$3:$D$101,2,FALSE))

Is there a way to pull the color of the data over also?

Any help would be appreciated