Dynamic Named Range to include blank cells?

[julios]

Hi guys,

I am trying to make a dynamic range that automatically expands upon new entries, but that includes blank cells. I have attached an example file. In the yellow cells are the values that I wish to produce automatically that need to be updated every time I add data in columns A,B or C. In column F I have created Named Ranges. These Ranges however are not dynamic so every time I add data on columns A,B or C, I need to go and change the range manually (this can be frustrating as I have more than 30 sheets in my workbook). The blank cells are also essential as they indicate that I haven't gotten a response in those cases. I have tried every "solution" that was offered online to no avail. Note that there are seven header lines.

Thanks in advance if anyone can help.

Here is the file as a google doc (I am working on excel though)

https://docs.google.com/spreadsheets/d/1-NoBYNPOUn-YTQNHXMcEP1PhDef12eWDG6IMN7SZD9Y/edit?usp=sharing

 

[Andrew Poulsom]

You can use a name that refers to:

=Sheet1!$A$8:INDEX(Sheet1!$A:$A,MATCH(9.99999999999999E+307,Sheet1!$A:$A))

 

[julios]

Thank you for your answer Andrew. Unfortunately this doesn't work. Or I am doing something wrong.

By googling I am finding that this should work, but it also produces an error:

=OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1)

 

[Andrew Poulsom]

In what way doesn't my formula work?

 

[rocketedtech]

You can use a name that refers to:

=Sheet1!$A$8:INDEX(Sheet1!$A:$A,MATCH(9.99999999999999E+307,Sheet1!$A:$A))

How can I apply this for a dynamic range of data?