Excel sheet Reference

Well-known Member
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

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

GlennUK

Well-known Member
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)

Well-known Member
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
RangeFormula
A1=Sheet1!#REF!+Sheet2!A1

GlennUK

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

does that do what you want?

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

That's great!

Replies
3
Views
171
Replies
1
Views
521
Replies
3
Views
554
Replies
3
Views
377
Replies
3
Views
221

1,190,783
Messages
5,982,900
Members
439,806
Latest member
ShakeShark1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back