Dynamic Named Range not working as expected

Hi,

I have a dynamic named range that works as expected in all but one situation. That situation is when data is not entered in the first row cell but is in the rest of the row cells.

When this happens the Dynamic Named range is not picking up the row of data.

For example: In a table of Columns A, B, C and Rows 1, 2, 3.

The data in the table is:
A B C
1 1 2 3
2 4 5 6
3 8 9

In this case it would not be picking up row 3 as nothing is entered in cell A3.

The formula I am using for my data is:

=OFFSET(Incidence!$F$2,1,0,COUNTA(Incidence!$F:$F)-1,3)

Could anyone offer some help please.

Thank you.

Many thanks for taking the time to reply.

I have attached a sample workbook as requested.

Hopefully that should explain things easier.

Thank you for taking the time to download and look at my problem. I thought you had cracked it with a simple solution (another of those, fresh eyes seeing the obvious answer) but unfortunately that formula doesn't work if further rows of data are added say with nothing in F again but data in G & H.

It doesn't expand the named range down to include the additional row of data.

I have attached a new example with your two suggestions included in the named rages.

Any further suggestions welcome!

v2_Sample_Workbook_13_04_2015.xlsx

Given that example..
What range do you actually WANT the dynamic named range to refer to? And Why?

Thank you for your reply.

I want the Dynamic Named Range to refer to F3:H11 in the v2_Sample_Workbook I attached previously. Though I want this to expand as more rows of data are added.

The reason for this is that the table forms part of a much larger Spreadsheet. On another tab I have cells that will look back to this table and enter what ever value is in the row.

This works as expected apart from when there is no data in the first cell of the data named range, i.e. Column F is blank. If that cell is blank I just get #REF! returned in my other tab and none of the data is populated as it is trying to look up a named range that doesn't extend down to that row due to the named range being based on the F column.

Hopefully that makes sense?

I can make up another sample spreadsheet which shows this better but the reason I have not so far is that it will take some time to strip out the private data. But if needs be I can do this.

The COUNTA part of the formula should look at a column that ALWAYS contains data in every row.
In your given example, that's either C D or E.
Personally, I'd go With D

=OFFSET(Incidence!$F$2,1,0,COUNTA(Incidence!$D:$D)-1,3)

Thank you for the reply - I take it then that the COUNTA only works when as you say there is data in every row of a particular column.

I will have to have a think about how I go about implementing this, making it a requirement that at least one column has to have data. I'll also think about if I can do some sort or workaround with hidden columns for example that count the number of blank cells or something as this will always return a result even if the user were to forget to enter data in at least one of the cells and then this count of blank cells could be used for the COUNTA part of the function.

Time to get my thinking cap on, suggestions welcome!

Thank you for taking the time to provide such a detailed reply. I shall look into it further later today. Many thanks!

You're welcome.

As by way of an update - I used the information you provided and solved the problem by referring to a column that will always have data in the last cell. Many thanks for the solution and your time.