Display Cell as Blank and not #REF!

English
, , , ,
#1

Hello,
I have a sheet that brings in stats from other sheets. Problem is I need to create the sheets down the road. Sheets are named say 00001 then 00002 and so on. Right now I’m using =INDIRECT(B5&".G16")

This formula checks B5 and if that sheet name exist say 00001 then it will place the content from sheet 00001.G16 in that cell.

If the sheet does not exist it displays #REF! after the sheet is created it will populate correctly. What can I add to the formula to make it just stay blank till the sheet is created? I was having a problem with isblank not sure if I’m entering it wrong or if it does not work in this situation.

Thank You!

#2

A cell of a not existing sheet does not exist. It is not a blank cell.

#3

(1) Keeping many sheets is mostly bad design.
(2) A cell containing a formula never is blank. The formula can, however, return the empty text as its result.
(3) =IFERROR(INDIRECT(B5&".G16);"") should do. (LibO version 4.0 or higher!)
(4) You may, of course, also return a message like “Sheet does not exist.” instead of empty text.

#4

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.