Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can a cell reference bring the color of the data to the new sheet
I'm creating a summary page with about 100 back sheets that have some data in
colors that should show up in the summary page. Is it possible to copy the colors when cell referencing on the summary page? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can a cell reference bring the color of the data to the new sheet
Cell formats cannot be linked or returned by formula.
Can you use Conditional Formatting to achieve your needs? Gord Dibben MS Excel MVP On Mon, 10 Jul 2006 16:18:02 -0700, SandyA wrote: I'm creating a summary page with about 100 back sheets that have some data in colors that should show up in the summary page. Is it possible to copy the colors when cell referencing on the summary page? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can a cell reference bring the color of the data to the new sh
I don't think that will work because some of the data is text rather than
numeric. Thanks for your help. "Gord Dibben" wrote: Cell formats cannot be linked or returned by formula. Can you use Conditional Formatting to achieve your needs? Gord Dibben MS Excel MVP On Mon, 10 Jul 2006 16:18:02 -0700, SandyA wrote: I'm creating a summary page with about 100 back sheets that have some data in colors that should show up in the summary page. Is it possible to copy the colors when cell referencing on the summary page? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can a cell reference bring the color of the data to the new sh
"SandyA" wrote:
.. I don't think that will work because some of the data is text rather than numeric. Some thoughts .. CF can support up to 3 triggers (4, inclusive of the "default" no-trigger fill). Provided it's within this CF limit, text is not a problem if the cells contain standard text strings to either match or to be searched (if the lookup standard text is housed within text strings). We could effect the same trigger criteria for standard text on the summary page. For eg, let's say we have 3 criteria to color trigger in the summary page's col A (say), presumed consistent with the criteria applied in the other 100 sheets' col A: Text1 - yellow fill Text2 - green fill Any numbers exceeding 500 - red fill / white font, bolded (Col A presumed to contain either text, eg: Text1, Text30, etc or real numbers: 500, 100, etc) Try in a new sheet, say Sheet1 .. Select col A (A1 active) Click Format Conditional Formatting Make the settings under conditions 1 to 3 (sequence is important) as : Cond1: =TRIM(A1)="Text1" Cond2: =TRIM(A1)="Text2" Cond3: =AND(ISNUMBER(A1),A1500) Trigger formats applied for Conds 1 to 3 a yellow fill, green fill, red fill / white font, bolded Test it out .. Input anywhere within col A, and we'd get: 501 (red fill, whote font, bolded) Text1 (yellow fill) 499 (no trigger) Text2 (green fill) 1000 (red fill, whote font, bolded) Text1 Text2 Text3 (no trigger) Text2 Text1 Text3 (no trigger) In another new sheet (Sheet2, say), Apply the CF to col A as per earlier except now we use as the cond format formulas: Cond1: =ISNUMBER(SEARCH("Text1",A1)) Cond2: =ISNUMBER(SEARCH("Text2",A1)) Cond3: =AND(ISNUMBER(A1),A1500) (Apply same trigger formats for Conds 1 to 3 as earlier) Test it out with the same inputs within col A, and we'd get: 501 (red fill, whote font, bolded) Text1 (yellow fill) 499 (no trigger) Text2 (green fill) 1000 (red fill, whote font, bolded) Text1 Text2 Text3 (yellow trigger) Text2 Text1 Text3 (yellow trigger) Text1 Text2 Text3 will now give yellow trigger as Cond1's formula will evaluate to TRUE, unlike in Sheet1 Text2 Text1 Text3 also gives yellow trigger as well (despite Text2's apparent position in the string ahead of Text1) as Cond1's formula will evaluate to TRUE ahead of Cond2 (the CF evaluation sequence is similar to an IF eval sequence [from left-to-right] evaluating from Cond1 Cond2 Cond3) If we had reversed Conds 1 and 2 in Sheet2 (with the same trigger color fills applied: Text1 - yellow, Text2 - green), ie used instead: Cond1: =ISNUMBER(SEARCH("Text2",A1)) Cond2: =ISNUMBER(SEARCH("Text1",A1)) Cond3: =AND(ISNUMBER(A1),A1500) then we'd get: Text1 Text2 Text3 (green trigger) Text2 Text1 Text3 (green trigger) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can a cell reference bring the color of the data to the new sh
Some clarifications:
Text1 Text2 Text3 (yellow trigger) Text2 Text1 Text3 (yellow trigger) Text1 Text2 Text3 (green trigger) Text2 Text1 Text3 (green trigger) The phrases "yellow trigger", "green trigger" above (and elsewhere in the earlier response) mean the same as "yellow fill", "green fill", i.e. trigger yellow fill, trigger green fill <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
Urgent: How to bring data from another sheet into a droplist | Excel Discussion (Misc queries) | |||
Using a relative SHEET reference for source data in a chart | Charts and Charting in Excel | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Automatic cell increment with data from sheet 1 to sheet 2 | Excel Worksheet Functions |