Indirect alternative

dispelthemyth

Well-known Member
Joined
Mar 16, 2006
Messages
654
Hi

I currently use the below to reference a sheet as the sheet is deleted and recreated, so i use Indirect to stop the #REF errors once the sheet is deleted

The name of the sheet will always be the same, is there a way to not use indirect as i have this sheet reference about 100 times



INDIRECT("'Table!A:E")


I could use VBA to recreate the formula, but i was hoping there was an alternative formula that could be used
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

CROY1985

Active Member
Joined
Sep 21, 2009
Messages
498
The only thing that springs to mind is to use clear contents and paste new data onto sheet. Rather than deleting and recreating.
 

dispelthemyth

Well-known Member
Joined
Mar 16, 2006
Messages
654
Good question Paddy, and thanks both of you for the input

I've been making a few adjustments to someone else's file (Mandated we have to use it) to better suit my end users, the owner wrote probably 20K+ characters of code which does some pretty complex work

What i'm trying to do is create some sheets that i can just add to this pack rather than adjust their code as they make adjustments to that every time i use it, so its hard to track changes so it's easier to just use formulas rather than amend their code every time i use that template.

If its not possible (Which i thought it likely that Indirect was the only option) i'll just add a new module to recreate the formula after the template has done its work



Why is the sheet deleted and recreated?
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
If the sheet has to be deleted, it's either indirect() or code
 

Forum statistics

Threads
1,137,300
Messages
5,680,701
Members
419,928
Latest member
dolincasting

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