Name Range Change

[wec43wec]

I have two questions which pertains to the same or similar data as follows -

My data base range is (ie) a1 to x100.

A1 thru a10 has a state name (ie) Fla,
the next ten rows has another state name of CA - a11 thru a20 Ca, Now the same concept every 10 rows has a different state name. The next column would have the city name and the third column would have the population.

Now here is the problem.

I would and have created a "range name" for data in cells a1 thru x100 which normally have the same exact data fields in the same positions, however at some point an additional state name or city will be added which will extend the data field from 100 rows to 101 rows or 102, etc. As such, rows 101 and 102, etc will not be included in the "name range" previously created.

The second issue is that I normally sort the data base by population and extract the top 5 cities within each state into a report which is normally in the same cell location after I sort the data base by State and then population.


How can I extend the data base as well as maintain the formulas in the report sheet that is pulling data from the raw data base in cells a1 thru x100?

 

[GlennUK]

Firstly, look at the faq:
How can I rename a table as it changes size faq68-1331

What are the current formulas in the report that do this extracting? And what is it about them that you think needs maintenance?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[wec43wec]

The formula in the "report file" is directed at an exact cell location within the raw data file that pulls data after I sort the fields.

Note: the raw data file and the report file are on two different files. I do not open the the report file until I download the raw data and then sort by state and population.

As such, if anf when I add a city, the top 5 cell locations will shift.

Thanks for your input thus far, I will not be able to look at your response until later today - agian, thanks.

 

[GlennUK]

You could have formulas to find the first data per state after sorting. I'd use the MATCH function to find the row of the first State entry, and base my formulas on that.

Cheers, Glenn.

Beauty is in the eye of the beerholder.