Hello,
I am looking to dynamically resize a table full of formulas relative to a power query data set in a separate sheet.
First, thanks you for looking and I apologize for the long post.
In more detail:
Every month, a large dump of data is being dropped as an excel file into a folder. It will have the same file name every time and simply replace the last months data dump. I don't want anyone to ever touch that data - it remains sacrosanct.
In a separate excel (2016) workbook, in a separate folder, I run a query to pick up all the information in the dumped sheet. That is formatted via the query to include only the information necessary for reporting.
In the same book, on a separate sheet, I have a table full of formulas to organize and display the data in a more intuitive and aesthetic way. The goal is essentially to report work which still needs to get done, and where that work is in its process.
For the end-users to use the information, it must be easy to understand at-a-glance, as they don't have time to trawl through data.
The query data comes organized according to a unique identifier (we’ll call it a PIN), by row. The data would look like this:
<tbody>
</tbody>
As you can notice, PIN #’s 69054 and 34215 in my example share the same Premises ID - 600006. Each location can have multiple PIN types attached.
The Premises ID is how I’m organizing the information in the separate sheet. This is done because the end users naturally identify work by location, not by PIN #. The Premises ID is the only consistent piece of information that connects PIN #’s – as you may notice in the Premises Name column, there are too many different options for any other field to be consistent.
So, my organized sheet looks like:
<tbody>
</tbody>
So, is there a way to expand the table I’m working on to pick up ALL the UNIQUE instances of Premises ID in the query sheet, dynamically?
What I’m really trying to avoid is the end-users having to go in the sheet and expand my table every month to match the query data size. If that becomes necessary, the sheet will never be used.
Ideally, I could lock the whole sheet down, so they can’t edit anything in the workbook AT ALL. This minimizes the chance that the formulas break.
If it helps, the formulas I’m using look like:
Premises ID:
PIN # (easily modified for type):
All of the other columns (Type A, A PIN Date, Name, etc.) are based on the =sumproduct formula above.
Again, sorry for the long post. Thanks again for reading!
I am looking to dynamically resize a table full of formulas relative to a power query data set in a separate sheet.
First, thanks you for looking and I apologize for the long post.
In more detail:
Every month, a large dump of data is being dropped as an excel file into a folder. It will have the same file name every time and simply replace the last months data dump. I don't want anyone to ever touch that data - it remains sacrosanct.
In a separate excel (2016) workbook, in a separate folder, I run a query to pick up all the information in the dumped sheet. That is formatted via the query to include only the information necessary for reporting.
In the same book, on a separate sheet, I have a table full of formulas to organize and display the data in a more intuitive and aesthetic way. The goal is essentially to report work which still needs to get done, and where that work is in its process.
For the end-users to use the information, it must be easy to understand at-a-glance, as they don't have time to trawl through data.
The query data comes organized according to a unique identifier (we’ll call it a PIN), by row. The data would look like this:
| PIN # | PIN Type | Premises ID | Premises Name | Date |
| 34215 | A | 600006 | Middle Earth | 4/11/1990 |
| 46532 | B | 600091 | Mordor | 6/09/1972 |
| 09897 | A | 7000817 | Gondor | 12/12/2002 |
| 69054 | C | 600006 | Earth, Middle | 6/05/2013 |
<tbody>
</tbody>
As you can notice, PIN #’s 69054 and 34215 in my example share the same Premises ID - 600006. Each location can have multiple PIN types attached.
The Premises ID is how I’m organizing the information in the separate sheet. This is done because the end users naturally identify work by location, not by PIN #. The Premises ID is the only consistent piece of information that connects PIN #’s – as you may notice in the Premises Name column, there are too many different options for any other field to be consistent.
So, my organized sheet looks like:
| Premises ID | TYPE A | A PIN # | A PIN DATE | TYPE B | B PIN # | B PIN DATE | TYPE C | C PIN # | C PIN DATE | Name |
| 600006 | Yes | 34215 | 4/11/1990 | No | - | - | Yes | 69054 | 6/05/2013 | Middle Earth |
| 600091 | No | - | - | Yes | 46532 | 6/09/1972 | No | - | - | Mordor |
<tbody>
</tbody>
So, is there a way to expand the table I’m working on to pick up ALL the UNIQUE instances of Premises ID in the query sheet, dynamically?
What I’m really trying to avoid is the end-users having to go in the sheet and expand my table every month to match the query data size. If that becomes necessary, the sheet will never be used.
Ideally, I could lock the whole sheet down, so they can’t edit anything in the workbook AT ALL. This minimizes the chance that the formulas break.
If it helps, the formulas I’m using look like:
Premises ID:
PIN # (easily modified for type):
All of the other columns (Type A, A PIN Date, Name, etc.) are based on the =sumproduct formula above.
Again, sorry for the long post. Thanks again for reading!
