Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I highlight data differences in workbook
I have a workbook which has 3 worksheets named 03 feb, 10feb-24 mar and 31 mar-19th May. All worksheets have data in columns A-K and have 220 entries. What im trying to do is to be able to match up data but where its different in column A I need it highlighted. I.E I have in cell A5 on 03 feb and 10 feb-24 mar the number 4 but in cell A5 on 31 mar-19 may its different and has the number 9. What I want to do is where its different I want it highlighted. Can I do this. Thanks -- tweacle |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I highlight data differences in workbook
Hi, from what I understand I think this is possible; assuming that you
want the range to refer to the same row in the other sheets you could use a simple logical IF formula, along the lines of: =IF(OR('10feb-24 mar'!A5<'03 feb'!A5,'31 mar-19th May'!A5<'03 feb'!A5,'10feb-24 mar'!A5<'31 mar-19th May'!A5),FALSE,TRUE) Just insert it adjacent to the range and double click the fill handle. tweacle wrote: I have a workbook which has 3 worksheets named 03 feb, 10feb-24 mar and 31 mar-19th May. All worksheets have data in columns A-K and have 220 entries. What im trying to do is to be able to match up data but where its different in column A I need it highlighted. I.E I have in cell A5 on 03 feb and 10 feb-24 mar the number 4 but in cell A5 on 31 mar-19 may its different and has the number 9. What I want to do is where its different I want it highlighted. Can I do this. Thanks -- tweacle |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I highlight data differences in workbook
OK so I just reread the question. Once you have copied this formula
down (in column L), you need a way of knowing. You can either apply an autofilter (alt-dff) for the FALSE values, or set up conditional formatting to get a visual on which cells return a FALSE value. Hope this helps. Mark bridgesmj wrote: Hi, from what I understand I think this is possible; assuming that you want the range to refer to the same row in the other sheets you could use a simple logical IF formula, along the lines of: =IF(OR('10feb-24 mar'!A5<'03 feb'!A5,'31 mar-19th May'!A5<'03 feb'!A5,'10feb-24 mar'!A5<'31 mar-19th May'!A5),FALSE,TRUE) Just insert it adjacent to the range and double click the fill handle. tweacle wrote: I have a workbook which has 3 worksheets named 03 feb, 10feb-24 mar and 31 mar-19th May. All worksheets have data in columns A-K and have 220 entries. What im trying to do is to be able to match up data but where its different in column A I need it highlighted. I.E I have in cell A5 on 03 feb and 10 feb-24 mar the number 4 but in cell A5 on 31 mar-19 may its different and has the number 9. What I want to do is where its different I want it highlighted. Can I do this. Thanks -- tweacle |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I highlight data differences in workbook
Hi
One way Click on the first sheet tab, hold down shift and click on the last sheet tab. This will group the sheets. In a spare column on the sheet, enter =IF(SUM('03 Feb':'31 mar - 19th May'!A1)<A1*3,"Different","") Copy down as far as required. Now click on any sheet tab to ungroup the sheets The word Different will show up on each row where the three value are not the same. -- Regards Roger Govier "tweacle" wrote in message ... I have a workbook which has 3 worksheets named 03 feb, 10feb-24 mar and 31 mar-19th May. All worksheets have data in columns A-K and have 220 entries. What im trying to do is to be able to match up data but where its different in column A I need it highlighted. I.E I have in cell A5 on 03 feb and 10 feb-24 mar the number 4 but in cell A5 on 31 mar-19 may its different and has the number 9. What I want to do is where its different I want it highlighted. Can I do this. Thanks -- tweacle |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I highlight data differences in workbook
Roger, that's a good way - I had thought of that in my sleep somehow
and was just about to post it - minus the bit about grouping the sheets. Hence in B5: =IF(SUM(A5,'10feb-24 mar'!A5,'31 mar-19th May'!A5)<A5*3,FALSE,TRUE) Regards, Mark. Roger Govier wrote: Hi One way Click on the first sheet tab, hold down shift and click on the last sheet tab. This will group the sheets. In a spare column on the sheet, enter =IF(SUM('03 Feb':'31 mar - 19th May'!A1)<A1*3,"Different","") Copy down as far as required. Now click on any sheet tab to ungroup the sheets The word Different will show up on each row where the three value are not the same. -- Regards Roger Govier "tweacle" wrote in message ... I have a workbook which has 3 worksheets named 03 feb, 10feb-24 mar and 31 mar-19th May. All worksheets have data in columns A-K and have 220 entries. What im trying to do is to be able to match up data but where its different in column A I need it highlighted. I.E I have in cell A5 on 03 feb and 10 feb-24 mar the number 4 but in cell A5 on 31 mar-19 may its different and has the number 9. What I want to do is where its different I want it highlighted. Can I do this. Thanks -- tweacle |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
worksheet to worksheet . . . | Excel Worksheet Functions | |||
Pulling data from another workbook | Excel Worksheet Functions | |||
Printing data validation scenarios | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Excel: Use a name with external workbook reference for data valida | Excel Worksheet Functions |