Hi,

I'm doing a projection spreadsheet. The new months in diff sheets will be
added as and when the actual month passes, ie, up to now i have sheets up to
Aug. But in the MAIN sheet, I'm supposed to have months for the whole year up
to Dec. Since Sep to Dec don't have equivalent sheets yet, the cells show
#Ref since my formula is =Oct!A1.

I want to tell the cell to get the amount from "Oct" sheet if there's such a
sheet and if not, get the amount from "Sep" sheet. I tried this
"=if(Oct!A1="#REF", Sep!A1,Oct!A1)" but it doesn't work. What can I do?

Thanks!
Val

I think I've worked it out by using ISNUMBER function. But now i have a
problem...when I later add in a "Oct" worksheet, I must go to the cells in
the MAIN worksheet to manually go into the cell and go out to refresh the
cell. Is there an automatic way to refresh the cells when i add in the new
worksheet?

"Dolphinv4" wrote:

Hi,

I'm doing a projection spreadsheet. The new months in diff sheets will be
added as and when the actual month passes, ie, up to now i have sheets up to
Aug. But in the MAIN sheet, I'm supposed to have months for the whole year up
to Dec. Since Sep to Dec don't have equivalent sheets yet, the cells show
#Ref since my formula is =Oct!A1.

I want to tell the cell to get the amount from "Oct" sheet if there's such a
sheet and if not, get the amount from "Sep" sheet. I tried this
"=if(Oct!A1="#REF", Sep!A1,Oct!A1)" but it doesn't work. What can I do?

Thanks!
Val

Use the ISERROR function:
=IF(ISERROR(Oct!A1),"",Oct!A1)

/Marcus

"Dolphinv4" wrote:

Hi,

I'm doing a projection spreadsheet. The new months in diff sheets will be
added as and when the actual month passes, ie, up to now i have sheets up to
Aug. But in the MAIN sheet, I'm supposed to have months for the whole year up
to Dec. Since Sep to Dec don't have equivalent sheets yet, the cells show
#Ref since my formula is =Oct!A1.

I want to tell the cell to get the amount from "Oct" sheet if there's such a
sheet and if not, get the amount from "Sep" sheet. I tried this
"=if(Oct!A1="#REF", Sep!A1,Oct!A1)" but it doesn't work. What can I do?

Thanks!
Val

One way:

=IF(NOT(ISERROR(INDIRECT("'oct'!A1"))),INDIRECT("' oct'!a1"),Sep!A1)



Dolphinv4 wrote:

Hi,

I'm doing a projection spreadsheet. The new months in diff sheets will be
added as and when the actual month passes, ie, up to now i have sheets up to
Aug. But in the MAIN sheet, I'm supposed to have months for the whole year up
to Dec. Since Sep to Dec don't have equivalent sheets yet, the cells show
#Ref since my formula is =Oct!A1.

I want to tell the cell to get the amount from "Oct" sheet if there's such a
sheet and if not, get the amount from "Sep" sheet. I tried this
"=if(Oct!A1="#REF", Sep!A1,Oct!A1)" but it doesn't work. What can I do?

Thanks!
Val

--

Dave Peterson