Populate table dynamically

Robert Casaletta
Robert Casaletta used Ask the Experts™
on
I have a table that is generated from a data source.  The table is based on a 6-month time frame.  My challenge is how to populate the table if I do not have all 6-months of available.  So if I have 3-months of data, then they those 3-months would be displayed reading left-to-right, with the oldest in the left most column.

I have played with nested IF statements, but with no success.

Attached is a small example of my data with a table showing 4 of the 6 months and one with all 6-months. Table_With_Moving_Data_EE.xlsx
Table_With_Moving_Data_EE.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
I put the following formulas in cells G1 & G2, then copied across.
=IFERROR(AGGREGATE(15,6,$A2:$A7,COLUMNS($F1:F1)),"-")
=IFERROR(VLOOKUP(G$1,$A$2:$E$7,MATCH($F2,$A$1:$E$1,0),FALSE),"-")

Open in new window

Note that you need to edit the formulas (except the one for dates) when you bring back data from other tables.

The generic task that you are performing is called "pivoting the data". You can do it without formulas using a series of PivotTables.

It is also possible to pivot the data using PowerQuery add-in (now found under Data...Get and Transform in Excel 2016 and later).
Table_With_Moving_Data_EE.xlsx
NorieAnalyst Assistant

Commented:
Robert

Couldn't you use a simple SUMIF?

=SUMIF($A$2:$A$7,G$1,$B$2:$B$7)
Robert CasalettaManager, Warranty Performance

Author

Commented:
Thank you! Works great!  I will read up on the use of the aggregate function and pivoting the data.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial