A few questions

[Craig Schiller]

Greetings Excel Gurus!

A few questions, please:

1. I have a spreadsheet with subtotals that appear every seven rows.
Every month I add another 7 rows. I'd like to come up with a formula
that would allow me to total the subtotal cells. In other words, if my
subtotals are, e.g., in cells C6, C13, C20, C27 etc., what's the formula
that allows me to total these cells without inputting all the cell
numbers manually. There must be a function I'm not aware of that
provides for referencing cells in this manner, isn't there?

2. How does one find the last active row in a spreadsheet. I seem to
remember it has to do with creating an array, but I can't find wherever
I came across the info.

3. Does anyone have any suggestions for great books or websites of Excel
tips and tricks that would answer the types of questions I've posed here?

Thanks very much in advance,
Craig Schiller

 

[Bob Phillips]

1. =SUMPRODUCT(--(MOD(ROW(C1:C1000),7)=6),C1:C1000)

2. Just do a Ctrl_End, that takes you to the limit of the used range.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bill Ridgeway]

Q1:
You could name those cells and then the names would be referred to wherever
they appear in that (or even another, although it isn't advised)
spreadsheet. To do this, for each cell from which you want to 'export'
data -

Highlight the cell
Click on<Insert><Name><Define>
Type a name. Something like April2006A (Where 'A' will differentiate
between two similar names)

In your summary sheet for each cell in which you want to 'import' data -
Highlight the cell
Type "=" (without quotes), navigate your way to the cell with the source
data and press return. You will see that the formula will be something
like -
=April2006A

Q2:
A simple way (although I'm not sure it meets your requirements) is to click
on <End><Home>

Q3:
Of course reading books is great for learning but it does presume you need
to know everything about everything. That's OK but, in some respects, but I
find researching a real particular practical problem as it arises makes it
easier to learn and saves time.

Regards.

Bill Ridgeway
Computer Solutions

 

[Craig Schiller]

1. =SUMPRODUCT(--(MOD(ROW(C1:C1000),7)=6),C1:C1000)

Thanks, I'll try that. Just for my info, what is the purpose of the two
negative signs preceding (MOD ?

2. Just do a Ctrl_End, that takes you to the limit of the used range.

I'm sorry, I wasn't clear. I don't want to navigate to the last row, I
want to calculate it.

 

[Craig Schiller]

Q1:
You could name those cells and then the names would be referred to wherever
they appear in that (or even another, although it isn't advised)
spreadsheet. To do this, for each cell from which you want to 'export'
data -

Highlight the cell
Click on<Insert><Name><Define>
Type a name. Something like April2006A (Where 'A' will differentiate
between two similar names)

In your summary sheet for each cell in which you want to 'import' data -
Highlight the cell
Type "=" (without quotes), navigate your way to the cell with the source
data and press return. You will see that the formula will be something
like -
=April2006A

Yes, but wouldn't that still require manually adding the names cells?

Q2:
A simple way (although I'm not sure it meets your requirements) is to click
on <End><Home>

I'm sorry, I wasn't clear. I don't want to navigate to the last row, I
want to calculate it.

Q3:
Of course reading books is great for learning but it does presume you need
to know everything about everything. That's OK but, in some respects, but I
find researching a real particular practical problem as it arises makes it
easier to learn and saves time.

True, but if one doesn't know where to look...

Thanks for taking the time to respond.

Craig

 

[Bob Phillips]

1. See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

2.
=MAX((IF(ISNUMBER(MATCH(REPT("z",255),A:A)),MAX(MATCH(REPT("z",255),A:A)),0)
),
(IF(ISNUMBER(MATCH(9.99999999999999E+307,A:A)),MAX(MATCH(9.99999999999999E+3
07,A:A)),0)))



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bill Ridgeway]

Q1: Yes

Q2: Does this do what you want?
Value of last contiguous cell in column
=OFFSET(A1,COUNT(A:A)-1,)

Regards.

Bill Ridgeway
Computer Solutions