Excel 2007 : Referencing Dynamic Data Elegantly

[jwhite.ef]

In most simple Excel reporting I do, I have a Data sheet which imports data every time the workbook is opened. I do this because I've had to change the source of that imported data. On any given run of data import, I don't know how many rows will be imported, though it's never more than 400 rows.

I keep analysis and presentation on separate sheets. It's fairly easy to set up references and formulas for all 400 potential rows of data on my presentation and analysis sheets, but I'd very much like to cleanly display only the rows that have results for any given data run.

Any ideas? I mostly did searches for using dynamic named ranges, but didn't see how using them would help me. Maybe a condition for displaying nothing when outside of range of the original data set? I don't want to use the row as the index as the row offset might change as I insert and delete rows above the data on the presentation sheet. I don't want to have to manually blank out presentation rows, as I want a user to just open the workbook, and see clean results.

I can't help thinking there's an easy way to do this that's solidly in my blind spot.

[jwhite.ef]

Bump! Anyone have any ideas?

[Palmetto]

Your post lacks sufficient detail to provide a precise answer. It would best if you could upload a sample workbook meeting the following criteria:

1. It EXACTLY duplicates the structure of your real workbook, AND
2. It contains representative but non-sensitive dummy data, AND
3. It provides a couple of examples illustrating the desires results to provide clarity and assist with logic in developing the solution.

[broro183]

hi
Welcome to the Forum

We'll have a clearer picture once you upload a sample file that meets Palmetto's request. However, have you considered using a pivot table (with a dynamic range )?

I mostly did searches for using dynamic named ranges, but didn't see how using them would help me. Maybe a condition for displaying nothing when outside of range of the original data set? I don't want to use the row as the index as the row offset might change as I insert and delete rows above the data on the presentation sheet.

If your file has xyz rows of headers & then goes straight (with no intervening blank rows) to contiguous data, you should be able to use "the row as an index" because it will adjust as rows are inserted/deleted. If you are trying to identify something specific eg a subtotal row halfway down the data, you may be able to use the Index & Match worksheet functions. Let's see once we have a representative sample file...

hth
Rob