Formula that refers to a cell on a sheet that may or may not exist.

Hi,

I have a spreadsheet that has loads of completed forms on. I have a formula that references to these sheets, up to 420. But there may not always be so many sheets, as soon as I update the links, the formula is broken for good.

I would like the formula to stay the same weather the sheet is refers to exists or not!

Please help

Please Login or Register  to view this content.

Turns in to:

Please Login or Register  to view this content.

I need it to stay the same weather the sheet is there or not!

Try it like this:

=IFERROR(CONCATENATE(iferror(INDIRECT("'SHEET (2)'!N25"),""),'SHEET (2)'!O25,'SHEET (2)'!P25,'SHEET (2)'!Q25,'SHEET (2)'!R25,'SHEET (2)'!S25,' (2)'!T25,'SHEET (2)'!U25,'SHEET (2)'!V25,'SHEET (2)'!W25,'SHEET (2)'!X25,)," ")

I've only made the change (shown in red) to the first term, but you need to do it to all terms within the CONCATENATE function.

Hope this helps.

Pete

I just keep getting an error when I use that formula! any ideas?

I don't see how you can get an error when you have IFERROR in there to trap it. Did you remember to put all the brackets and quotes in? Post your version of the formula (i.e. the one you are having trouble with).

Pete