Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Checking a number of sheets
Hi, I've a number of sheets. The 1st cell in the C column is blank on all the sheets except one. The same is true for the next cell, except the value may be on a different sheet. For example with sheets 1 to 3 .....sheet 1.....sheet 2....... sheet 3 C1........................................4 C2.....................3.................. C3........................................2 C4....1................................... etc.... I hope you get the idea. I've also got a summary sheet which I want to return a (different) value from the sheet which happens to have a number on it (from M1). I've got the formula below which works for 3 sheets, but I'd like something which I can extend to any number of sheets. =IF(ISBLANK(Sheet1!C7),IF(ISBLANK(Sheet2!C7),IF(IS BLANK(Sheet3!C7),"error",Sheet3!M$1),Sheet2!M$1),S heet1!M$1) Any suggestions welcome! Cheers -- sphenisc ------------------------------------------------------------------------ sphenisc's Profile: http://www.excelforum.com/member.php...fo&userid=3697 View this thread: http://www.excelforum.com/showthread...hreadid=485919 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Checking a number of sheets
Let A1:A3 contain Sheet1, Sheet2, and Sheet3
Since only one sheet will ever have a value for C7, if Column M contains numerical values, try =SUMPRODUCT(--(N(INDIRECT("'"&A1:A3&"'!C7"))<""),N(INDIRECT("'" &A1:A3&"' !M7"))) If Column M contains text values, try... =INDEX(T(INDIRECT("'"&A1:A3&"'!M7")),MATCH(TRUE,N( INDIRECT("'"&A1:A3&"'!C 7"))<"",0)) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the range for your sheet names accordingly. Hope this helps! In article , sphenisc wrote: Hi, I've a number of sheets. The 1st cell in the C column is blank on all the sheets except one. The same is true for the next cell, except the value may be on a different sheet. For example with sheets 1 to 3 ....sheet 1.....sheet 2....... sheet 3 C1........................................4 C2.....................3.................. C3........................................2 C4....1................................... etc.... I hope you get the idea. I've also got a summary sheet which I want to return a (different) value from the sheet which happens to have a number on it (from M1). I've got the formula below which works for 3 sheets, but I'd like something which I can extend to any number of sheets. =IF(ISBLANK(Sheet1!C7),IF(ISBLANK(Sheet2!C7),IF(IS BLANK(Sheet3!C7),"error",She et3!M$1),Sheet2!M$1),Sheet1!M$1) Any suggestions welcome! Cheers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert text-format number to number in excel 2000%3f | Excel Discussion (Misc queries) | |||
Master invoice number | Excel Worksheet Functions | |||
Min and Max values from over 200 sheets | Excel Discussion (Misc queries) | |||
Copy comments to several sheets in a workbook? | Excel Worksheet Functions | |||
PROTECTING/UNPROTECTING SHEETS | Excel Discussion (Misc queries) |