#REF error when cells definitely exist

[Shaz11]

I am receiving this error when I know that the cells that are referenced definitely exist. Could there be too many formulae in my spreadsheet?

 

[Peter_SSs]

Welcome to the MrExcel board!

Pretty hard to make any comment without seeing the formula and the data (dummy) that the formula is referencing.

I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[KOKOSEK]

In that reference into other sheet? Maybe tiny mistype in sheet name?

 

[Shaz11]

Welcome to the MrExcel board!

Pretty hard to make any comment without seeing the formula and the data (dummy) that the formula is referencing.

I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Thank you for your reply - I didn't expect someone to answer so quickly! This is a screenshot. The formulae in this sheet link to another sheet (daily data 3min reach) and all of these formulae work. I have checked the sheet and all of the required data is there?

In that reference into other sheet? Maybe tiny mistype in sheet name?

Thank you for the suggestion, I wish that were the problem!
I really appreciate your help

 

[Shaz11]

Welcome to the MrExcel board!

Pretty hard to make any comment without seeing the formula and the data (dummy) that the formula is referencing.

I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Thanks - I will need to work out how to install XL2BB - much appreciated!

 

[Peter_SSs]

This is a screenshot.

Unfortunately we cannot tell anything from that - other than that there is a #REF! error (but you already told us that)

all of these formulae work

Hmm, if that was the case I don't think that you would have started this thread.

 

[Shaz11]

Unfortunately we cannot tell anything from that - other than that there is a #REF! error (but you already told us that)



Hmm, if that was the case I don't think that you would have started this thread.

This is the formula and there is data in all the cells in the sheet referenced.

=SUM('Daily Data 3min Reach'!JP9:KT9)

The formula is used in several places and works or the bar charts would not be showing values up to September - I just don't understand why the same formula is not working for October.

I realise I am going to look mighty stupid at some point in this thread but I really want to get to the bottom of this

 

[Peter_SSs]

This is the formula and there is data in all the cells in the sheet referenced.

=SUM('Daily Data 3min Reach'!JP9:KT9)

If that is the one returning a #REF! error, then the first thing would be to carefully check JP9:KT9 in 'Daily Data 3min Reach' to ensure one of those cells does not contain a #REF! error.

If that is not the problem I don't think XL2BB will help in this case as it looks like you have too much data to use XL2BB.

Instead, if still a problem, can you upload a copy of the file, with any sensitive data disguised or removed (provided the error still exists) to somewhere like DropBox or OneDrive or Google Drive etc and provide a link here so we can take a look at the actual file?

 

[RoryA]

If you enter:

=COUNT('Daily Data 3min Reach'!JP9:KT9)

into a cell, what does it return?

 

[Peter_SSs]

Another formula to try that might pinpoint the potential error(s)

=LET(d,'Daily Data 3min Reach'!JP9:KT9,TEXTJOIN(", ",,FILTER(ADDRESS(9,COLUMN(d),4),ISERROR(d),"No errors")))