Ask Your Question

Display Cell as Blank and not #REF!

asked 2017-01-12 09:41:57 +0200

bdenn gravatar image

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!

edit retag flag offensive close merge delete


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

Lupp gravatar imageLupp ( 2017-01-12 11:33:53 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-01-12 11:31:52 +0200

Lupp gravatar image

updated 2017-01-12 12:07:45 +0200

(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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-01-12 09:41:57 +0200

Seen: 124 times

Last updated: Jan 12 '17