Dynamically expanding Excel Table

[Vivek D]

I have a sheet with some data in it. In another sheet (let's call it "Final Sheet"), I have a table of data that uses the data in the source sheet and manipulates it as required and shows the output.

I need the "Final Sheet" to dynamically increase in size (if possible decrease too) based on the number of rows in the source sheet.

I was able to do this using the Offset, COUNTA solution that has been provided in a lot of blog posts. However, I want this "Final Sheet" table to be an "excel table" too and that's what I'm not able to achieve right now.

I've attached a sample file showing both the cases. I put all 3 tables in the same sheet in the sample but in my actual case they will be on different sheets.

 

[Somendra Misra]

Hi Vivek,

How about converting your source data to Excel Table and create a pivot table from it in Tabular form?

Regards,

 

[Vivek D]

Hi Vivek,

How about converting your source data to Excel Table and create a pivot table from it in Tabular form?

Regards,

Yes. That's one possible solution but setting up all the formulae in the pivot is a bit of pain as opposed to just having them in the cells. Some are fairly complex formulae so would prefer to have it within the cells.

 

[NARAYANK991]

Hi Vivek ,

If you want data to be processed and laid out in the form of a table , I think that is not possible , since the Excel Table is a structure meant for data entry and not for just formulas to be laid out.

Secondly , I am not able to appreciate the reasoning behind why you want the final output to be in a table.

If you can explain what you want to do instead of how it is to be done , a different solution may emerge.

Narayan

 

[Vivek D]

Hi Vivek ,

If you want data to be processed and laid out in the form of a table , I think that is not possible , since the Excel Table is a structure meant for data entry and not for just formulas to be laid out.

Secondly , I am not able to appreciate the reasoning behind why you want the final output to be in a table.

If you can explain what you want to do instead of how it is to be done , a different solution may emerge.

Narayan

We receive some data from a particular source. We need to transform that data in certain ways e.g. replace . with say / or apply some mapping and replace codes with textual descriptions etc and then load that processed data into another place.

This will eventually happen via an ETL process but for now it's manual and I'm trying to make it as easy as possible.

The OFFSET/COUNTA option works out and I can go with that. I just wanted to know if I can do the same using an excel table itself so I can have the advantages of having an excel table too. Just wanted to know if it's possible or not. Not a big deal if it cannot be done.

 

[jeffreyweir]

Just wanted to know if it's possible or not.

It sure is, with a Macro.

But here's what I'd explore first: make the Source Data an Excel Table, and add some extra columns with formulas that do the ETL stuff, then point a Pivot at the whole lot and just bring in the transformed columns.

I do that all the time. I use tables for both Data Entry and 'live' ETL. Sometimes I overwrite the transformed figures with their hard-coded values afterwards via a macro, to keep the processing overhead down - particularly if the data grows to be tens of thousands of rows long. But that's simple. And as soon as you add new data, the table expands and the ETL formulas are copied down and the ETL done right before your very eyes.

Happy to assist further in need, but I'd need you to upload a more realistic sample file.