Make linked data "auto-expand" - (?)

[Guest]

I wish I knew how to ask this - but I just don't know the terminology. I have
been using excel to create estimates and invoices for many years. Also for
many years, I've wished I could create a way for the estimate/contract to
"auto-expand" as I type in row after row of product description - quantity -
unit price - totals and so forth. I want this because there is lots of text
after the product listing, and right now I have to hide rows or add rows
according to my needs. I link the cells on my actual printable estimate to
another series of sheets where I calculate my costs and markup. I've asked
many people over the years how to do this, but no one ever can give me an
answer - though I've seen spreadsheets that work the way I want (I just don't
know how to get in touch with the authors!). I understand this may be a
macro/VBA issue but If someone out there could even just point me in the
right direction I would appreciate it.

 

[Guest]

Set your column that contains your descriptive text and then format the
column for word wrap. After selecting the cells, right-click on one of the
selected cells, select format cells, and then click on the Alignment tab. In
the Text Control panel click the Word Wrap check box. You may also want to
set the vertical alignment in the cell to top.

 

[Guest]

Thanks for your input Kevin - let me be more descriptive of what I need to
do: I am creating a list of rows and columns containing products, prices,
quantity, and totals. I have row headings labeled as such. Imagine I have 100
products I can sell. Therefore I have the need to create an estimate sheet
that has the ability to contain 100 different rows - one for each product
with its associated quantity, price, description and total. My dillema is
that I never actually create an invoice or estimate with all 100 products I
sell, but the total amount varies greatly - say from five products to 75. So
I've created a spreadsheet that can handle 75 different rows in case I need
to list that much. If I list only five products on an estimate, I've got 70
empty rows until the disclaimer and other text at the bottom of my estimate.
That would make for several blank sheets if I don't hide or delete those
rows. I want to be able to have excel "auto-expand" from five to 10 to 75 or
whatever amount of rows depending on how much I enter in my main data entry
sheet. Am I making any sense? The issue is not making one cell wrap around a
lot of text, it's adding or hiding the rows themselves automatically.

 

[Pete]

In a column away from the printed area of the spreadsheet, you can add
a formula like:

=A1

and copy down to row 75. Highlight these cells and go to Data | Filter
| Autofilter (check on).

You will now have a filter for this column only, and you can hide the
unused rows by selecting a custom filter for not equal to 0 (zero). Of
course, this assumes that you have something in column A - if this is
not the appropriate column then change the formula. If you have a block
of rows that you want to appear on all invoices (eg your address,
customer details etc), then you can remove the formulae for these rows.

It isn't automatic, but it is easy to change each time after you have
entered the details.

Hope this helps.

Pete

 

[Guest]

Pete... you da man! I never thought of that before! I've used the autofilter
for lists of people and like products and such, but never thought of
filtering a range of cells and selecting "non-blank" to only show the rows
that have data in them. This will work great for me. Have a great day.

 

[Pete]

Glad you could make use of it - thanks for getting back.

Pete