Excel sheet Reference

[ziad alsayed]

dear all

let say i have a number in cell A1 (5) sheet1 and another number in cell A1 (7) sheet2 .
in sheet3 cell A1 i have the sum of both cells A1 from sheet1 and sheet2.

my question is : what formula should i use in sheet3 that refer to both cells A1 if but some time i may delete both sheets 1 and 2, or i may delete the column A in sheet one or the row 1 in sheet1 .

appreciate any help

 

[GlennUK]

Do you mean that you want the formula to still to refer to any possible sheets that may exist, and still point at cell A1 regardless of deletion of rows or columns?

If that's right, see if his helps ... insert new sheets of Start and End, and position Start before Sheet1, and End after Sheet2, and use a formula in Sheet3 of :
=SUM(Start:End!A1)

 

[ziad alsayed]

dear GlennUK

thanks for the reply ,but this is not what i need.

actually if i delete the row or column or the sheet , i don't want it to refer to it again .

the problem i am facing is, let say cell A1 in sheet3 is the sum of cells A1 in sheet 1 and sheet2, if i delete row 1 in sheet 1 the formula will not work anymore,

i think i should use indirect function but i didn't how to do it.

below is an example, the total should be 7 but after i deleted A1 in sheet one i want the total to be 4 which is the value in A1 sheet2

Excel Workbook
	A
1	#REF!
Sheet3

Excel 2010

Cell Formulas
Range	Formula
A1	=Sheet1!#REF!+Sheet2!A1

 

[GlennUK]

What happens if you use an IFERROR function, like:
=IFERROR(Sheet1!A1,0)+IFERROR(Sheet2!A1,0)

does that do what you want?

 

[ziad alsayed]

thanks GlennUK, yep it works, it was in front of me and i didn't look at it..

thanks for your assistance.

 

[GlennUK]

That's great!