Newbie Needs Help

[The Wolf]

I use get external data from FileMaker Pro into Excel. It's a small database
of purchase orders.

All I'm trying to do in Excel is total the amount of each purchase order to
compare against the budget.

For some reason the subtotal option is grayed out when I select the "Total
Amount" column. Obviously I'm doing something elementary wrong.

And if I get that solved is there an easy way to make comparisons for each
quarter?

Tia,
Michael
--

 

[JE McGimpsey]

Couple of questions, as I can't replicate your problem:

1) What version of XL and OS?

2) Is Data/Subtotals... the only option grayed out?

3) Do you by any chance have multiple sheets selected?


As far as making comparisons for each quarter, that's what Pivot Tables
were made for. PT's have a sharp learning curve, but once you understand
the basics, they make many types of complex analysis easier.
Specifically, if you have your dates in a pivot table field, CTRL-click
the field header and choose Group and Outline, where you'll be given the
option of grouping by quarters.

 

[The Wolf]

Couple of questions, as I can't replicate your problem:

1) What version of XL and OS?

OS9.1 Excel 2001

2) Is Data/Subtotals... the only option grayed out?

Under data

Subtotal and Table are grayed out.

3) Do you by any chance have multiple sheets selected?

How would I know? Sorry very little experience.

 

[JE McGimpsey]

2) Is Data/Subtotals... the only option grayed out?

Under data

Subtotal and Table are grayed out.
[/QUOTE]

Then you probably have your data in a List (using the List Manager).

You can't use Subtotal in a List. However, if you have a Totals Row at
the end of your list and you apply autofilter to your column, the totals
row will only show the subtotal of the values that meet the autofilter
criteria.

If you need to display subtotals, remove the List manager

How would I know? Sorry very little experience.

You would know by looking at your worksheet tabs - if more than one are
selected (white, rather than gray) then many menu options are disabled.
You'd also see "[Group]" in the worksheet title bar.

However, as I said, there would be far more options grayed out if this
were the case.

 

[The Wolf]

Under data

Subtotal and Table are grayed out.

Then you probably have your data in a List (using the List Manager).

You can't use Subtotal in a List. However, if you have a Totals Row at
the end of your list and you apply autofilter to your column, the totals
row will only show the subtotal of the values that meet the autofilter
criteria.

If you need to display subtotals, remove the List manager

How would I know? Sorry very little experience.

You would know by looking at your worksheet tabs - if more than one are
selected (white, rather than gray) then many menu options are disabled.
You'd also see "[Group]" in the worksheet title bar.

However, as I said, there would be far more options grayed out if this
were the case.[/QUOTE]

OK I am in the List Manager.

Can't I define a cell as total of e1, e2, e3, etc?

 

[John McGimpsey]

OK I am in the List Manager.

Can't I define a cell as total of e1, e2, e3, etc?

I wasn't very clear - I meant that you can't use the Data/Subtotal menu
item in a list to automatically group and subtotal your data.

You can certainly enter a formula into a list, including a SUBTOTAL():

=SUBTOTAL(9,E1:E3)

which will give you the sum of E1:E3, and the subtotal won't be added to
the column total (since it's a SUBTOTAL() function, too).

 

[The Wolf]

I wasn't very clear - I meant that you can't use the Data/Subtotal menu
item in a list to automatically group and subtotal your data.

You can certainly enter a formula into a list, including a SUBTOTAL():

=SUBTOTAL(9,E1:E3)

which will give you the sum of E1:E3, and the subtotal won't be added to
the column total (since it's a SUBTOTAL() function, too).

Thanks for responding, from my Apple Works experience I was able to do that.

Herein lies the rub; the purchase orders are requested by different people,
I have the list filtered to show the total of the person I want to compare
with the budget BUT the formula =SUM(E3:E12) gives the total of ALL the
purchase orders, I only want the total of the filtered list.

Any ideas?

 

[John McGimpsey]

Thanks for responding, from my Apple Works experience I was able to do that.

Herein lies the rub; the purchase orders are requested by different people,
I have the list filtered to show the total of the person I want to compare
with the budget BUT the formula =SUM(E3:E12) gives the total of ALL the
purchase orders, I only want the total of the filtered list.

Any ideas?

That's one feature of SUBTOTAL() - SUBTOTAL(9,E3:E12) will only give the
total for the visible cells.

 

[The Wolf]

That's one feature of SUBTOTAL() - SUBTOTAL(9,E3:E12) will only give the
total for the visible cells.

=SUM(E3:E12) is currently in cell E20

Exactly what would I type in cell E20 to return a subtotal of the visible
cells?


SUBTOTAL() - SUBTOTAL(9,E3:E12) doesn't work

Thanks

 

[John McGimpsey]

=SUM(E3:E12) is currently in cell E20

Exactly what would I type in cell E20 to return a subtotal of the visible
cells?


SUBTOTAL() - SUBTOTAL(9,E3:E12) doesn't work

Try:

=SUBTOTAL(9, E3:E12)

 

[The Wolf]

Try:

=SUBTOTAL(9, E3:E12)

Thanks! That works.