Remove #value! error

[Miloni]

I have a workbook that will have total 13 sheets; 12 for each month Jan to Dec and 13th is the summary. I will call the data from each of the 12 sheets into the 13th sheet. A new sheet for each month would be created at the beginning of each month. Meanwhile I set up formula in the the summary sheet to call data for all the months whenever the file is opened. Right now I have only January sheet created in the workbook. I dont have any other month sheet in the workbook. At this point for February to December when I call the data, it gives me #value! error and what i understand is that it is because the refered sheets donot exist. Can I get a blank cell instead of this error? I use the following formula in the summary sheet:

=SUMIF(February!$L$3:$L$13,"=???5*",February!$M$3:$M$8)

The above code works fine when used to get January data in summary sheet.


any help appreciated.

[EB08]

=if(iserror(SUMIF(February!$L$3:$L$13,"=???5*",February!$M$3:$M$8)),"",SUMIF(February!$L$3:$L$13,"=???5*",February!$M$3:$M$8))


I think that would give you a blank cell

[Miloni]

Thanks, it did work, but on pressing enter, it opens a dialog box "Update Values: November" and wants me to select a file and click ok. Also this dialog box is displayed only when i click enter when the cursor is in the formula bar displaying the formula entered. But if I am in a different cell and move on to that particular cell and press enter it doesnot open the dialog box.

Did I make myself clear?

[sous2817]

Do you have a November sheet created yet?

[Miloni]

No..I have only one sheet created: January. but I am applying formulas for all the months. November is just an example.

[Jonnyu182]

In order for you to reference the November sheet you need to have a sheet called November. This is why you are getting the pop up dialog. It is trying to find the November Sheet.

[sous2817]

If you're maintaining the workbook, maybe you can create all of the sheets and then hide the ones that are in the future. At the start of each month, it's simple enough to unhide the proper sheet (you could even have Excel do it for you with a little VBA)...just my $.02

[Miloni]

Thanks everyone..

[Aladin Akyurek]

If you are not unconfortable with expensive formulas regarding this particular wb...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
   SUMIF(INDIRECT("'"&A2&"'!$L$3:$L$13"),"=???5*",
        INDIRECT("'"&A2&"'!$M$3:$M$13"))))

where A2 houses a sheet name like February.