dynamic formatting in excel

[redlam]

hi there.
i am a total excel novice but i need to know if its possible to dynamically format excel (as you would crystal reports) so that subtotals, calculated and formatted cells are not dependent on fixed placement of the data. for example, i will be importing data from an external database into an excel file at runtime; the number of rows will vary each time the file is run. basically, i want to use excel as my report writer. does this make sense? can anyone help? ugh.
thanks.

 

[Dreamboat]

You bet --> makes sense to me.

Excel loves when data is:

First row contains headings.
Second row begins records (first record).
No completely blank columns within contiguous data.
No completely blank rows within contiguous data.

As long as you've got that, click in any cell of the data and hit Data-Subtotals.

Let's suppose Column B is "Department" and you want to subtotal column C, "Budget Dollars" by alphabetical department name.

Click anywhere in column B. Hit the AZ button on toolbar to sort by department name. (Make sure it leaves the heading "Department" in its place.) Then, hit Data-Subtotal.

Select at each change in:
Department

and have checkmark for subtotal:
Budget

Hit ok.

To the left of your ABC column letters, you'll now see a little 123.

Click on the three to get all data broken down AND their subtotals.

Click on the two to get all of the subtotals only (by department name), without the detail.

Click on the one to get the grand total of the entire budget dollars only.

To remove subtotals, go to Data-Subtotals-Remove all.

Let's suppose you want to find only Personnel Department budgeted items over $1000.

Hit Data-Autofilter. You'll get little dropdown arrows on every field from which you can use on the department column, and then for the Over $1000, you can hit the dropdown and select "custom". You figure it out from there.

Check out pivot tables too. I hate them so I refuse to tell people how to use them. But a lot of people love them!

techsupportgirl@home.com
Brainbench MVP for Microsoft Word

http://www.brainbench.com

 

[redlam]

that's definitely a start - thanks! it looks like there needs to be some user interaction every time the file is opened tho - is that true? what i mean is this...
i've created a worksheet from external data (for this test its a text file). i've set the 'external data range properties' so that the query definition is saved, the data is refreshed on file open and the cell formatting is preserved. the only issue is that every time the file is opened, i need to re-sort and re-subtotal. not sure if the users are going to be as savy. it would be nice if these options were saved and refreshed automatically... any clue if this is possible???
thank you, thank you!
michelle

 

[Dreamboat]

Sure. If you know what steps you will want to take to accomplish that formatting, you just record a macro. Tools-Macro-Record new macro. Check out the help file, michelle, or I'll be up all night, ok? Then, if you still have questions or need help, we'll go from there. Also, you can always email me a copy of the file if you like.
techsupportgirl@home.com
Brainbench MVP for Microsoft Word

http://www.brainbench.com

 

[redlam]

ok, i'll check out macros and see how it goes. thanks for your help!

 

[redlam]

ok, wow... i've learned a lot of excel. i'm really close to having a solution for this report. i ended up connecting to my data using MSQuery and i created a parameter query that accepts user defined parameters every time the file is opened and refreshed. my question now is:
can i create a formula in one of the cells that will display the parameter value that the user entered?
thanks much!