Dynamic resizing of table relative to query data

uceaamh

New Member
Joined
Feb 23, 2017
Messages
27
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:


PIN #PIN TypePremises IDPremises NameDate
34215A600006Middle Earth4/11/1990
46532B600091Mordor6/09/1972
09897A7000817Gondor12/12/2002
69054C600006Earth, Middle6/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 IDTYPE AA PIN #A PIN DATETYPE BB PIN #B PIN DATETYPE CC PIN #C PIN DATEName
600006Yes342154/11/1990No--Yes690546/05/2013Middle Earth
600091No--Yes465326/09/1972No--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:
=IF(OR((Data[Notice Type]=”A”),(Data[Notice Type]=”B”),(Data[Notice Type]=”C”)),Data[Premises - ID],0)

PIN # (easily modified for type):
=SUMPRODUCT((DATA[Premises - ID]=$A2)*1,(Table4[Notice Type]=”A”)*1,( DATA[PIN #])*1)

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!
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Watch MrExcel Video

Forum statistics

Threads
1,108,974
Messages
5,525,990
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top