Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy color using vlookup
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 -- Baltimore Ravens |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy color using vlookup
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 "Ravens Fan" wrote: 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 -- Baltimore Ravens |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy color using vlookup
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 "Jim Thomlinson" wrote: 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 "Ravens Fan" wrote: 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 -- Baltimore Ravens |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy color using vlookup
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 "Ravens Fan" wrote: 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 "Jim Thomlinson" wrote: 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 "Ravens Fan" wrote: 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 -- Baltimore Ravens |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy color using vlookup
Thanks again Jim,
I'll try it. -- Baltimore Ravens "Jim Thomlinson" wrote: 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 "Ravens Fan" wrote: 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 "Jim Thomlinson" wrote: 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 "Ravens Fan" wrote: 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 -- Baltimore Ravens |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy color - Excel 2007 | Excel Discussion (Misc queries) | |||
How do I keep the font color with VLOOKUP? | Excel Worksheet Functions | |||
copy color | Excel Worksheet Functions | |||
Sum & copy color format...? | Excel Worksheet Functions | |||
Is it possible to run a vlookup on font color | Excel Worksheet Functions |