Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
My sheet could have anywhere from 1 to 200 rows.
A B C 1 Jones 9 8 2 Smith 3 3 Jones 6 6 4 Clark 4 3 5 Young 1 1 There's alot of other data in numerous sheets which gets summarized on one sheet. From this sheet I need to find the row numbers is C0 and B<C. The problem is I need a one line answer for the summary sheet (ex - The following rows have errors: 1, 4). Is there anyway to do this? Due to the way my data is compiled, I am unable to use Data Validation. Thanks! |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
How about an alternative.
Add headers in row 1. Add an new column with formulas like: =if(and(c22,b2<c2),"Error","") And then drag down the column. Select that new column and do Data|Filter|Autofilter to show just the Error's. laszlo wrote: My sheet could have anywhere from 1 to 200 rows. A B C 1 Jones 9 8 2 Smith 3 3 Jones 6 6 4 Clark 4 3 5 Young 1 1 There's alot of other data in numerous sheets which gets summarized on one sheet. From this sheet I need to find the row numbers is C0 and B<C. The problem is I need a one line answer for the summary sheet (ex - The following rows have errors: 1, 4). Is there anyway to do this? Due to the way my data is compiled, I am unable to use Data Validation. Thanks! -- Dave Peterson |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
For a single line summary, run this macro:
Sub laszlo() Dim n As Long, m As Integer Dim bv As Integer, cv As Integer n = Cells(Rows.Count, "A").End(xlUp).Row messagee = "The following rows have errors: " For i = 1 To n bv = Cells(i, "B").Value cv = Cells(i, "C").Value If cv 0 And bv < cv Then messagee = messagee & i & ", " End If Next Range("D1").Value = Left(messagee, Len(messagee) - 2) End Sub -- Gary''s Student - gsnu200742 "laszlo" wrote: My sheet could have anywhere from 1 to 200 rows. A B C 1 Jones 9 8 2 Smith 3 3 Jones 6 6 4 Clark 4 3 5 Young 1 1 There's alot of other data in numerous sheets which gets summarized on one sheet. From this sheet I need to find the row numbers is C0 and B<C. The problem is I need a one line answer for the summary sheet (ex - The following rows have errors: 1, 4). Is there anyway to do this? Due to the way my data is compiled, I am unable to use Data Validation. Thanks! |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thank you so much! I added this to my existing (& very elementary) macro and
it worked out great. One thing though... what can I do if none of the rows have errors? If there a way to have no message? "Gary''s Student" wrote: For a single line summary, run this macro: Sub laszlo() Dim n As Long, m As Integer Dim bv As Integer, cv As Integer n = Cells(Rows.Count, "A").End(xlUp).Row messagee = "The following rows have errors: " For i = 1 To n bv = Cells(i, "B").Value cv = Cells(i, "C").Value If cv 0 And bv < cv Then messagee = messagee & i & ", " End If Next Range("D1").Value = Left(messagee, Len(messagee) - 2) End Sub -- Gary''s Student - gsnu200742 "laszlo" wrote: My sheet could have anywhere from 1 to 200 rows. A B C 1 Jones 9 8 2 Smith 3 3 Jones 6 6 4 Clark 4 3 5 Young 1 1 There's alot of other data in numerous sheets which gets summarized on one sheet. From this sheet I need to find the row numbers is C0 and B<C. The problem is I need a one line answer for the summary sheet (ex - The following rows have errors: 1, 4). Is there anyway to do this? Due to the way my data is compiled, I am unable to use Data Validation. Thanks! |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thanks for responding. I had tried that formula too although I had forgotten
about the autofilter function. This is a monthly spreadsheet so I wanted something that would calculate automatically each time the sheet was updated. I already have a macro in it to create the summary sheet, so I was able to incorporate Gary"s Student's suggestion. Now I just need to figure out how to skip the error message if there are no errors. Thanks again! "Dave Peterson" wrote: How about an alternative. Add headers in row 1. Add an new column with formulas like: =if(and(c22,b2<c2),"Error","") And then drag down the column. Select that new column and do Data|Filter|Autofilter to show just the Error's. laszlo wrote: My sheet could have anywhere from 1 to 200 rows. A B C 1 Jones 9 8 2 Smith 3 3 Jones 6 6 4 Clark 4 3 5 Young 1 1 There's alot of other data in numerous sheets which gets summarized on one sheet. From this sheet I need to find the row numbers is C0 and B<C. The problem is I need a one line answer for the summary sheet (ex - The following rows have errors: 1, 4). Is there anyway to do this? Due to the way my data is compiled, I am unable to use Data Validation. Thanks! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Multiple cell references | Excel Worksheet Functions | |||
Function(s) that return multiple separated references | Excel Worksheet Functions | |||
I cant get Excel OFFSET Fnct to return multiple references. | Charts and Charting in Excel | |||
How do I put multiple references in the same cell? | Excel Worksheet Functions | |||
Return Cell References | Excel Worksheet Functions |