Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for neg. #'s on different sheets
I am currently working on a project that has 5 sheets, each sheet is the
same format. I am wanting to create a sheet that will look at each of the 5 sheets and tell me how many negative numbers are on each sheet. For example: I want it to tell me: Sheet 1 has 5 neg. numbers, Sheet 2 has 3 neg. #'s, so on. Any help would be GREATLY APPRECIATED!! thanks for your time. Kyle ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for neg. #'s on different sheets
One way:
="Sheet 1 has " & COUNTIF(Sheet1!1:65536,"<0") & " neg. numbers" ="Sheet 2 has " & COUNTIF(Sheet2!1:65536,"<0") & " neg. numbers" .... In article , banchee wrote: I am currently working on a project that has 5 sheets, each sheet is the same format. I am wanting to create a sheet that will look at each of the 5 sheets and tell me how many negative numbers are on each sheet. For example: I want it to tell me: Sheet 1 has 5 neg. numbers, Sheet 2 has 3 neg. #'s, so on. Any help would be GREATLY APPRECIATED!! thanks for your time. Kyle ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for neg. #'s on different sheets
Say in your new sheet you choose cell A1, enter this code:
=Countif(Sheet1!A1:I1000,"<0") What that will do is count the number of cells with a value less than zero in it on sheet1 of your workbook and display the result in cell A1 of your new sheet. Do the same for the other sheets in your workbook. HTH "banchee" wrote in message ... I am currently working on a project that has 5 sheets, each sheet is the same format. I am wanting to create a sheet that will look at each of the 5 sheets and tell me how many negative numbers are on each sheet. For example: I want it to tell me: Sheet 1 has 5 neg. numbers, Sheet 2 has 3 neg. #'s, so on. Any help would be GREATLY APPRECIATED!! thanks for your time. Kyle ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for neg. #'s on different sheets
=countif(Sheet1!A1:Z5000,"<0")
change the A1:Z5000 to reflect the range you expect to contain information. If you want text with it ="Sheet1 has " & countif(Sheet1!A1:Z5000,"<0") & " neg. numbers" if you want all five sheets reported in 1 cell, then concatenate ="Sheet1 has " & countif(Sheet1!A1:Z5000,"<0") & " neg. numbers" & ", Sheet2 has " & countif(Sheet2!A1:Z5000,"<0") & " neg. numbers" -- Regards, Tom Ogilvy "banchee" wrote in message ... I am currently working on a project that has 5 sheets, each sheet is the same format. I am wanting to create a sheet that will look at each of the 5 sheets and tell me how many negative numbers are on each sheet. For example: I want it to tell me: Sheet 1 has 5 neg. numbers, Sheet 2 has 3 neg. #'s, so on. Any help would be GREATLY APPRECIATED!! thanks for your time. Kyle ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching sheets | Excel Worksheet Functions | |||
Searching across all sheets | Excel Discussion (Misc queries) | |||
searching all sheets in a wb | Excel Worksheet Functions | |||
Data searching between sheets | Excel Discussion (Misc queries) | |||
Data searching between sheets | New Users to Excel |