Variable Sheet Name in Formula

Question

I have a workbook, let's say with 3 sheets.  I have a database tool that pulls information from my database into the tabs Sheet 2 and Sheet 3, and on Sheet 1, I have a formula that adds the amounts from Sheet 2 & Sheet 3.  So the formula in Sheet 1 cell A1 is:  =Sheet2!A1+Sheet3!A1. 

Sheet 2 is static, however, when the tool pulls the info from the database, Sheet 3 gets deleted until it finds data that belongs in Sheet 3.  When Sheet 3 is deleted, I get the expected #Ref! error.

Is there a way to reference text in the above formula so that when Sheet 3 is deleted and subsequently added back, I don't lose the formula that references Sheet 3?

Answer 1

You could use a formula like:

=IF(ISERROR(CELL("address",INDIRECT("sheet3!a1"))),0,INDIRECT("sheet3!a1"))

  +Sheet2!A1

But there may be alternatives.

Maybe you could add a new sheet that's just used to retrieve the values from the

Sheet3 that gets refreshed from that database tool.

When that db tool is done, you could copy|paste all the cells to this new

sheet.  Then you don't need to have to worry about that sheet that the db tool

updates being there or not.

In fact, if you have to delete the sheet, maybe you could clear the cells on

that other sheet at the same time.

=====

Another option.

Before the db tool runs, change all the formulas on Sheet1 to strings:

Select Sheet1

Select all the cells

edit|replace

what:  =        (equal sign)

with:  $$$$$=   (a unique string)

replace all

Run the db tool.  But since there are no formulas to break, nothing goes wrong.

Then after a new sheet3 is created, you can change the strings back to formulas.

Fletchpdx wrote:

I have a workbook, let's say with 3 sheets.  I have a database tool that pulls information from my database into the tabs Sheet 2 and Sheet 3, and on Sheet 1, I have a formula that adds the amounts from Sheet 2 & Sheet 3.  So the formula in Sheet 1 cell A1 is:  =Sheet2!A1+Sheet3!A1.

Sheet 2 is static, however, when the tool pulls the info from the database, Sheet 3 gets deleted until it finds data that belongs in Sheet 3.  When Sheet 3 is deleted, I get the expected #Ref! error.

Is there a way to reference text in the above formula so that when Sheet 3 is deleted and subsequently added back, I don't lose the formula that references Sheet 3?

--

Dave Peterson

Answer 2

Hello,

Two options:

=Sheet2!A1+INDIRECT("Sheet3!A1")

(if only Sheet3 is being deleted and recreated later)

and via VBA:

Sub mycalc()

Sheets("Sheet1").Range("A1") = Sheets("Sheet2").Range("A1") + _

                               Sheets("Sheet3").Range("A1")

End Sub

(you run this via a button whenever you have your data ready, for example)

Regards,

Bernd

---

www.sulprobil.com

Answer 3

Hi,

Try this, you wwill still get a #REF error as long as heet 3 doesn't exist but it will sum sheet 3 when it is added

=SUM(INDIRECT("Sheet2!A1"),INDIRECT("Sheet3!A1"))

---

If this post answers your question, please mark it as the Answer.

Mike H

Answer 4

Thank you to everyone.  All excellent responses!  I appreciate the help.