Link cell to to sheet not yet created..

[tlc53]

Hi,

At the top of my spreadsheet I am going to have the Clients name.

The client name information is taken from a sheet in the workbook which hasn't yet been created.

For example, in Sheet1 Cell D6 will have the following formula: "=ABC!B3" which will bring through the clients name.

However, the sheet ABC will only be created once the spreadsheet is opened by the user for the first time. If I link the cell without the sheet yet being present, it returns #REF !

What would be the best way to deal with this? Perhaps a macro which runs on opening and puts the formula in then? Maybe a VBA code? Unsure of the best and most efficient way to handle this.

Thanks you!

 

[Fazza]

hello

to me it isn't clear what is being done
so that makes it nigh impossible to advise

as you best know what you're doing, just do anyway that works for you
if it isn't the most efficient way it won't matter

regards

 

[gior]

Hi, pls check usage in Sheet1 Cell D6 the following formula: "=IFERROR(INDRECT("ABC!B3"), "No sheet ABC")" - initially without sheet ABC, then adding sheet ABC.

 

[tlc53]

Hi gior,
Thank you! That was a good idea!
However, regardless of whether sheet ABC is present, D6 returns "No sheet ABC". Even if I select the formula and enter to refresh it, it still remains as "No sheet ABC"

 

[tlc53]

Hi, pls check usage in Sheet1 Cell D6 the following formula: "=IFERROR(INDRECT("ABC!B3"), "No sheet ABC")" - initially without sheet ABC, then adding sheet ABC.

Thanks again!! I played around with the formula and it now works! =IFERROR(INDIRECT("ABC!B3"),"")
Nice easy answer. Brilliant

 

[gior]

Hi, tlc53. The formula is working to me. If sheet ABC is present in same workbook with formula? If your formula "=ABC!B3" is working at this moment?

 

[footoo]

hi, tlc53. The formula is working to me. If sheet abc is present in same workbook with formula? If your formula "=abc!b3" is working at this moment?

indirect