Subtotals

  • Thread starter Thread starter firefytr
  • Start date Start date
F

firefytr

hello Danny,

i'm sorry , i couldn't reporoduce the error stated in your firs
question.

but the answer to your second question, is yes. at lest i have withou
problems. unless my success in your second question is systematic o
my failure to reproduce your first question.

could you post more data? what you have, where it's at, how it's se
up, etc
 
Aardvark,

Posting dating is very rude and likely to result in fewer responses.

Regards,
Kevin
 
#1. Excel likes to warn you if it's not sure it will guess correctly.

You can make the guessing a little easier by formatting that top header row
differently (bold works nicely).

If you don't want to change the format, just make sure you select your range
correctly.

#2. That's not an option built into excel. But you could use a macro that
moves things over. But just as a general rule, I've found that life is a lot
easier if you can learn to accept the way excel does it. When you have to
recreate the subtotals, you'll have less to do.
 
LOL - What Kevin meant was that your system date is wrong, and is set to a day
ahead. Given that some use that as a tactic for ensuring their posts stay on
top for a while, many will simply delete it solely because of that, meaning you
lose a chunk of your potential audience. Please fix your system date and time,
as it screws things up doing what you are doing.
 
Thank you for the warning. I have not posted any data on a
newsgroup, nor will I. However for those that are happy to help I
will email info.

Thank you.
Click to expand...

Hi
Kevin (as far as I understood it) did not mean posting attachments BUT
your posting date/time. You may check your system time as you're one
day ahead of the rest of us - and I'm quite sure you don't have such a
thing like a time machine :-)

This causes your posts to appear for one day at the top of all other
posts -> this is considered rude. Though in your case it just seems to
be a wrong setting / a mistake
-> please check your system date and correct it :-)
 
OK so to move the subtotals over how would this work? I cannot set the
macro to move every second row as the subtotal positions are not constant
relative to each other and are changing.

Thanks,

D
 
Just be sure you really want to do this first - Don't get me wrong here, as I'm
not trying to preach or anything, but it is just usually *so* much easier to go
with built in Excel functionality rather than fight it. The Data / Subtotals
option not only does all the work for you, but allows you to collapse the data
set and show only the subtotals, as well as allowing you different options in
terms of summing/counting.averaging etc. You can if you want select only these
subtotal lines and then copy these only and paste them elsewhere if you wish,
too.

You *can* do just about anything you like with macros, but without some serious
effort in testing, you always run the risk that it will need to be edited later
to encompass some change to your data, and if you aren't totally sure how to do
that you may not necessarily want to be reliant on it. These kinds of changes
though are usually handled quite easily by built in functionality.

Like I said, not trying to preach, but just trying to pass on the benefit of
pain that some of us have already gone through, in the hope that you don't have
to

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Aardvark said:
OK so to move the subtotals over how would this work? I cannot set the
macro to move every second row as the subtotal positions are not constant
relative to each other and are changing.

Thanks,

D
Click to expand...
 
I put a bunch of test data on a worksheet and applied Data|subtotals.
In my test case, I had 3 levels on the left hand side (only one subtotal).
I wanted to move the subtotals over to the right for those formulas.

I put headers in row 1 and data in A:F.

Option Explicit
Sub testme02()

Dim myRng As Range
Dim myRngV As Range
Dim myRngVF As Range
Dim myCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
Set myRng = .Range("a2:F" & .Cells(.Rows.Count, "A").End(xlUp).Row)
.Outline.ShowLevels rowlevels:=2
Set myRngV = Nothing
Set myRngVF = Nothing
On Error Resume Next
Set myRngV = myRng.Cells.SpecialCells(xlCellTypeVisible)
Set myRngVF = myRngV.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If myRngVF Is Nothing Then
MsgBox "No visible formulas found"
Exit Sub
End If

For Each myCell In myRngVF.Cells
With myCell
If IsEmpty(.Offset(0, 1).Value) Then
.Offset(0, 1).Formula = .Formula
.ClearContents
Else
MsgBox "No room for: " & .Address(0, 0)
End If
End With
Next myCell

End With

End Sub

MyRng is the range that had the subtotal.
myRngV is the visible cells after showing level 2.
myRngVF is the formula cells in that visible range.

Then I just cycle through those cells. If I see that there's a value in the
adjacent cell, I issue a warning message, but continue. I'm not sure what you
would want if you have two columns with subtotals.
 
Thanks for the help guys,
I am in an interesting situation. Actually I agree with you, but I have
been asked to set a system up and also figure that a good way to learn the
intricacies of Excel is to trouble shoot. If there is not a simple way to
do this then I have learned something! Yippee!

Now onto further adventures. btw what the heck is a delimited list?

Thanks,

D
 
Dear All,

I have a couple of questions:-
I have a list of data with column headings in the first row and use the
subtotal command it says that Excel cannot determine which row contains the
labels...why not?

Also is it possible to set the subtotal result to appear in an adjacent
column rather than above or below?

Thanks,

Danny
 
It's a list of stuff that's separated by a common character (or set of common
characters).

In excel, the most common delimiter is usually a comma, but could be
anything--in fact, windows keeps track of what it wants to use as a list
separator under:

Windows Start Button|Settings|Control Panel|Regional Settings Applet.
Look at the Number tab (at the bottom).

For me, it's the comma. (That's pretty much the standard in the USA. Other
contries use semicolons. And you could change yours to whatever you wanted--but
you'll have headaches later when you share info between users.)

A lot of programs can't create .xls files natively. But to be able to share
info from one program with another, a lot (most???) can create these delimited
lists. MS calls them Comma Separated Values (CSV) files. Even though you may
not be using a comma!

And excel will own the .csv file (just like .xls). If you double click on a
..csv file in windows explorer, it'll load into excel.

If you rename your .csv file to .txt, then you can use File|open within excel.
xl will recognize it as a text file and allow you to specify how it should be
imported.

You can specify "fixed width"--columns 1-10 is a field, 11-14, 15-22, etc. Each
record in the file has fields that are exactly the same as the one below it.

Or you can specify "delimited" and be able to choose your delimiter (even type
in the one you want if it isn't listed). Then things like:

Aardvark,234,test
a,3,b

will place each field in the appropriate column (it doesn't depend on the length
of each field).


Thanks for the help guys,
I am in an interesting situation. Actually I agree with you, but I have
been asked to set a system up and also figure that a good way to learn the
intricacies of Excel is to trouble shoot. If there is not a simple way to
do this then I have learned something! Yippee!

Now onto further adventures. btw what the heck is a delimited list?

Thanks,

D
Click to expand...
 
So delimited lists (like csv) are really only used for data import and
export. Presumably when exporting from Excel each cell is then separated
with a comma.

I think I have it,

Thanks,

D
 
Dear Kevin,

Thank you for the warning. I have not posted any data on a newsgroup, nor
will I. However for those that are happy to help I will email info.

Thank you.

A

That said...how did you set the subtotals to appear in the next door column?

Thanks,
A
 
Back
Top