CODEWITHSUNDEEP
excelif-statementexcel-formula
Zoe
Zoe

Reputation: 191

Concatenate Formula (Getting a #REF Error)

Afternoon all :)

This is kind of a little difficult to explain but ill happily clarify where ever it is needed. Thank you for taking the time to read this post ^^ Here goes..

I am currently creating a spreadsheet that is been extracted from the database whereby I am tasked to concatenate data from 2 adjacent cells. I change the database on a frequent basis adding or removing data wherever necessary so the range of data is always different. To concatenate the two cells I use the following formula:

e.g: =IF(ISBLANK(B8&H8),"",B8&H8)

This formula works out great when im dealing with increasing amounts of data as I can simply drag the formula down as far as i want and i know that it will pick up the formula whenever I refresh the database without the need of seeing value errors when the formula ends up referring to a blank cell. The snag here (and my query as well) is if I have less data then before the formula within the last set of cells looks something like this:

e.g: =IF(ISBLANK(#REF!&#REF!),"",#REF!&#REF!)

I have dealt with #REF before in other spreadsheets whereby I simply used a ISERROR in the statement but I dont know if there is a possible way of including this within my formula. I need the ISBLANK there so I have more control and dont have to drag the formula as often.

If there is a better way around this or a way to amend the current formula Id appreciate the help :)

Upvotes: 1

Views: 2210

Answers (1)

Stepan1010
Stepan1010

Reputation: 3136

The only way you are going to get something like this:

=IF(ISBLANK(#REF!&#REF!),"",#REF!&#REF!)

in the formula bar is if you(or the system you are using) are somehow deleting the cells that were originally referenced. This should be avoidable. You can clear the cells referenced instead of deleting them completely - then you won't get this error - and your formulas will remain intact. Now you can certainly use the formulas provided in the comments to hide the errors - but the root of your problem seems to me to be that the errors are occuring in the first place. Good Luck.

Upvotes: 2

Related Questions