A few questions

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

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)

"Craig Schiller" <cschiller1@earthlink.net> wrote in message
news:uEr233yVGHA.2492@TK2MSFTNGP11.phx.gbl...
> 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
>

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" <cschiller1@earthlink.net> wrote in message
news:uEr233yVGHA.2492@TK2MSFTNGP11.phx.gbl...
> 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 wrote:

> 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.

Bill Ridgeway wrote:

> 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
>
> Regards.
>
> Bill Ridgeway
> Computer Solutions
>
> "Craig Schiller" <cschiller1@earthlink.net> wrote in message
> news:uEr233yVGHA.2492@TK2MSFTNGP11.phx.gbl...
>
>>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
>>
>
>
>

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)

"Craig Schiller" <cschiller1@earthlink.net> wrote in message
news:O0KsLTzVGHA.1236@TK2MSFTNGP11.phx.gbl...
> Bob Phillips wrote:
>
> > 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.
>

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

"Craig Schiller" <cschiller1@earthlink.net> wrote in message
news:O2J8BUzVGHA.1236@TK2MSFTNGP11.phx.gbl...
>
>
> Bill Ridgeway wrote:
>
>> 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
>>
>> Regards.
>>
>> Bill Ridgeway
>> Computer Solutions
>>
>> "Craig Schiller" <cschiller1@earthlink.net> wrote in message
>> news:uEr233yVGHA.2492@TK2MSFTNGP11.phx.gbl...
>>
>>>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
>>>
>>
>>
>>
>