Auto Adjusting Table Size?

[robfanucchi]

Hi all, new to the forum.

I have problem (which is probably simple for you guys)

I have a template which I will filter and import different customers products into and then it does a simple total at the bottom.
How do I get the table to automatically change size when the customers information changes.

Example:
Customer A has 20 line items (rows 1 to 20), row 21 being the total.

then I will filter for Customer B and they only have 10 line items (rows 1 to 10) with row 21 still being the total and rows 11 to 20 being blank (or reflecting 0 or N/A) and taking up space.

How would i get the table to automatically change size or adjust to be the best fit for the right amount of line items.

Basically i would like the Total row (row 21) to automatically move to row 11 in this example.
And would all the formatting like borders etc automatically change as well?


Thanks,
Rob.

[tim201110]

upload the WB please
how many rows can be in the table?

[prinsr]

I cant think of regular Excel solution. Are you familiar with VBA? If so, you could use that.
A quick google search shows lots of examples.

[robfanucchi]

Hi,

(I am new here so im really struggling to upload an attachment of my WB, what is the best way to do it?)

This is a very basic example of what I am working with.
If I go into the pivot and select which customer i want to view and then copy their products and paste it into the summary sheet, then all their figures will pull through with some additional calculations.
So basically if you select customer A (with 20 line) all their products will fill the table on the summary tab.
then if i put customer B info into it then lines 12 to 21 will reflect #N/A.
Is there any way for the Total line (row 22) to automatically move up to row 12 deleting all those empty (#N/A) lines?

and then if i filter back on customer A will the total line automatically move back down to row 22?