Indirect alternative

[dispelthemyth]

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

 

[CROY1985]

The only thing that springs to mind is to use clear contents and paste new data onto sheet. Rather than deleting and recreating.

 

[PaddyD]

Why is the sheet deleted and recreated?

 

[dispelthemyth]

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]

If the sheet has to be deleted, it's either indirect() or code