Expanding Data As Needed

M

MT

I am wondering if there is a way to automatically expand a list of data that varys with clients, as needed. For instance, I have an Input Worksheet, where all information is entered on this page and every other worksheets is linked to the Input Worksheet. As the number of accounts that clients hold varies, is there away to add the acounts to the Input page where it will expand the information of the other worksheets automatically. I would like to prevent the need of having extra spaces to cover the amount of accounts that clients may have. That way if one client has three accounts and the next have ten, it will automatically have enough spaces only for the amount needed, without extra blank cells.

MT
 
J

jeff

Hi,

The answer is probably "yes", but can you give us some
more information? It's not quite clear when you say
"expand a list of data" and "have enough spaces" what
your sheets look like. How do you get the data from
the input sheet to the details? via formulae or a
(button &) macro?

jeff
-----Original Message-----
I am wondering if there is a way to automatically expand
Click to expand...
a list of data that varys with clients, as needed. For
instance, I have an Input Worksheet, where all
information is entered on this page and every other
worksheets is linked to the Input Worksheet. As the
number of accounts that clients hold varies, is there
away to add the acounts to the Input page where it will
expand the information of the other worksheets
automatically. I would like to prevent the need of
having extra spaces to cover the amount of accounts that
clients may have. That way if one client has three
accounts and the next have ten, it will automatically
have enough spaces only for the amount needed, without
extra blank cells.
 
D

Dave Peterson

I don't think I'd even try. There's just too much that can go wrong.

Even just typing the wrong client name would mean that you have to have a way to
delete that invalid entry from where ever it went.

I like to keep all my data in one worksheet. I can do nicer things with
it--sort/filter/pivottables/charts--really easily.

If I need to have separate sheets per client, then I'll have a run-on-demand
macro that creates each sheet from scratch each time.

You can see how Debra Dalgleish approaches it at:
http://www.contextures.com/excelfiles.html

Look for:

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- creates a list of unique items,
creates a sheet for each item, then replaces old data with current.
AdvFilterCity.xls 46 kb

and

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top