Excel sheet Reference

ziad alsayed

Well-known Member
Joined
Jul 17, 2010
Messages
665
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
Joined
Jul 8, 2002
Messages
11,723
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)
 
Upvote 0

ziad alsayed

Well-known Member
Joined
Jul 17, 2010
Messages
665
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
 
Upvote 0

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,723
What happens if you use an IFERROR function, like:
=IFERROR(Sheet1!A1,0)+IFERROR(Sheet2!A1,0)

does that do what you want?
 
Upvote 0

ziad alsayed

Well-known Member
Joined
Jul 17, 2010
Messages
665
thanks GlennUK, yep it works, it was in front of me and i didn't look at it.:).

thanks for your assistance.
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top