Excel VBA referencing sheets by changing their name

0
votes

I have Sheet1 and it has some calculation:

  • Many other sheets are referencing this sheet's two cells
  • I have another sheet Sheet2 with a different type of calculation deriving same sort of results.
  • Somehow I want to change the name / codename of Sheet2 so same cells are being referenced by other worksheets.

e.g. Sheet1's cell U30 has a result of a calculation and Sheet2's cell U30 also has a result of a calculation

Either Sheet1 or Sheet2 can be used for calculations rest of the sheets depend on these sheets.

Other sheets are referencing it by =Sheet1!U30 when Sheet1 is used as Calculation sheet

I want a way so that other sheets can reference =Sheet2!U30 automatically when Sheet2 is used as Calculation sheet.

How can I achieve that?

Currently at the start of the workbook, user is presented with a form asking him which sheet you want to use as the calculation sheet. If he chooses Sheet1, Sheet2 is set to hidden and vice versa.

1
excelvba
You could use named ranges in the calculations in the other sheets, and use VBA to switch the "refers to range" for the ranges between the two sheets. – Tim Williams

1 Answers

0
votes

You could use named ranges in the calculations in the other sheets, and use VBA to switch the "refers to range" for the ranges between the two sheets. – Tim Williams Oct 19 at 6:39