Formula #REF error when updating a data table that holds less data (blank rows)

[Galoredk]

Hi there,

I am having a bit of an issue here.

I have built a Web analytics business dashboard which consist of some tables. These tables are based on formulas and the formulas reference to another sheet, where I have the raw data.

The raw data is updated on a daily basis through a REST interface. Thus the table that the formulas point to is changed over time.

This is not an issue when data is in the table, but for a few tables, there some times are no data (meaning nothing happened). One market for instance, simply does not get data on a daily basis for a particular data set. Thus the table is empty.

So what happens is that I get #ref errors in my formula every time it points to a row that had data but after the daily update does not have data in the raw data sheet. This of course causes some frustration with the users of the dashboard andI would very much like to find a way to lock the formula to NOT show a #ref error if no data is present, but simply preserve the formula.

Does anyone have a solution for this?
Any help would be greatly appreciated.

With kind regards
Ulrik

 

[Robert Mika]

Could you wrap your formula in:

IFERROR (EXCEL 07/10)

or

Excel 03/07/10

IF(ISERROR()...

statment?

Like
=IFERROR(yourformula,"")

or
=IF(ISERROR(yourformula),"",yourformula)

 

[Galoredk]

Hi Mika,

Thanks for replying. My issue is not that the cell that the formula calculates data into says #ref, but that the formula itself changes to #ref because a row in the raw data table is blank.

Here is a simplified example:

=Q28/VLOOKUP(P28;$W$24:$Y$33;2;FALSE)-1

Here I try and calculate the difference in a number from one month to another. But IF the table $W$24:$Y$33 does not contain any data tomorrow, or maybe only the first 2 rows contain data, then the formula looks like this:

=#RE/VLOOKUP(#REF!;$W$24:$Y$26;2;FALSE)-1

What I would like to achieve, is to have the formula remain the same, so that when the day after tomorrow data appears in the $W$24:$Y$33 table, the formula gets the right data again.

Right now I have to manually change the formula for this table for 120 different markets whenever they get a #ref error, that is not a viable way to do it .

Any suggestions?

 

[bolwahnn]

Hi Mika,

Thanks for replying. My issue is not that the cell that the formula calculates data into says #ref , but that the formula itself changes to #ref because a row in the raw data table is blank.

Here is a simplified example:

=Q28/VLOOKUP(P28;$W$24:$Y$33;2;FALSE)-1

Here I try and calculate the difference in a number from one month to another. But IF the table $W$24:$Y$33 does not contain any data tomorrow, or maybe only the first 2 rows contain data, then the formula looks like this:

=#RE/VLOOKUP(#ref !;$W$24:$Y$26;2;FALSE)-1

What I would like to achieve, is to have the formula remain the same, so that when the day after tomorrow data appears in the $W$24:$Y$33 table, the formula gets the right data again.

Right now I have to manually change the formula for this table for 120 different markets whenever they get a #ref error, that is not a viable way to do it .

Any suggestions?

Did you ever get a fix for this? I'm currently having the same issue...

 

[bolwahnn]

Hi Mika,

Thanks for replying. My issue is not that the cell that the formula calculates data into says #ref , but that the formula itself changes to #ref because a row in the raw data table is blank.

Here is a simplified example:

=Q28/VLOOKUP(P28;$W$24:$Y$33;2;FALSE)-1

Here I try and calculate the difference in a number from one month to another. But IF the table $W$24:$Y$33 does not contain any data tomorrow, or maybe only the first 2 rows contain data, then the formula looks like this:

=#RE/VLOOKUP(#ref !;$W$24:$Y$26;2;FALSE)-1

What I would like to achieve, is to have the formula remain the same, so that when the day after tomorrow data appears in the $W$24:$Y$33 table, the formula gets the right data again.

Right now I have to manually change the formula for this table for 120 different markets whenever they get a #ref error, that is not a viable way to do it .

Any suggestions?

Did you ever get a fix for this? I'm currently having the same issue...

 

[SpillerBD]

I would say you should be using Table Names instead of ranges.
A range can be table of data but a Table is an object type with benefits. CTRL+T to create a Table.

(Sorry, I have yet to hit the REST learning curve to say how you should/could build to a table.)