Need a Formula ASAP

I'm working with two sheets. I have certain cell's information from sheet 1
going to sheet 2. I was doing fine until I needed to add an insane amount
more information to sheet 1. I can't figure out a formula that will
calculate the information I need onto sheet 2.

Example: Sheet 1/Column A/Row 1's information is also on Sheet 2 ( formula I
use on sheet 2: =Sheet1!A1). If one Sheet 1, I use only Column
A/Rows1,22,43,64,..., 35872, what formula (or a step/setting) can I use so I
can cut/paste instead of manually adding 21 to my previous formula on sheet 2?

If I'm confusing you, I'm sorry. I don't know that much about Excel and
really need help. Thanks. Please either respond or e-mail me directly.

~Korie~

Hi Korie,

You could use something like this ...

=INDEX(Sheet1!$A:$A,(ROW(A1)-1)*21+1,1)

This is assuming that you want to only see the data in each sequential row
in sheet two. Put this formula in a cell, then copy down as needed. It
will only show every 21st row, starting with row 1 of Sheet1.

HTH

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


"Korie" <[email protected].(donotspam)> wrote in message
news:[email protected]...
> I'm working with two sheets. I have certain cell's information from sheet
> 1
> going to sheet 2. I was doing fine until I needed to add an insane amount
> more information to sheet 1. I can't figure out a formula that will
> calculate the information I need onto sheet 2.
>
> Example: Sheet 1/Column A/Row 1's information is also on Sheet 2 ( formula
> I
> use on sheet 2: =Sheet1!A1). If one Sheet 1, I use only Column
> A/Rows1,22,43,64,..., 35872, what formula (or a step/setting) can I use so
> I
> can cut/paste instead of manually adding 21 to my previous formula on
> sheet 2?
>
> If I'm confusing you, I'm sorry. I don't know that much about Excel and
> really need help. Thanks. Please either respond or e-mail me directly.
>
> ~Korie~

Zack Barresse wrote...
>You could use something like this ...
>
>=INDEX(Sheet1!$A:$A,(ROW(A1)-1)*21+1,1)
>
>This is assuming that you want to only see the data in each sequential row
>in sheet two. Put this formula in a cell, then copy down as needed. It
>will only show every 21st row, starting with row 1 of Sheet1.
....

This formula would need to be placed in some cell in Sheet2!1:1 in
order for it to reference Sheet1!A1. Generally safer to use ROWS than
ROW, so if the topmost result cell in Sheet2 were B5, the formula would
be

B5:
=INDEX(Sheet1!$A:$A,ROWS(B$5:B5)*21-20,1)

Interesting. I've used the two before, but not in such a fashion/context.
I know there are many, many permutations which you can use. Such as ...

=INDEX(Sheet1!$A:$A,(ROWS(B$5:B5)-1)*21+1,1)
=INDEX(Sheet1!$A:$A,(ROW(A1)-1)*21+1,1)
=INDEX(Sheet1!$A:$A,ROWS(B$5:B5)*21-20,1)

The list goes on. They should all be safe from row insertions. It's funny
how the ROW reference will be errored out when a row is deleted though, and
not ROWS. I'm assuming this could be chalked up to Excel and it's
calculation and/or function nuances?

Anyway, thanks for the note. Love to learn something every day.

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)



"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> Zack Barresse wrote...
>>You could use something like this ...
>>
>>=INDEX(Sheet1!$A:$A,(ROW(A1)-1)*21+1,1)
>>
>>This is assuming that you want to only see the data in each sequential row
>>in sheet two. Put this formula in a cell, then copy down as needed. It
>>will only show every 21st row, starting with row 1 of Sheet1.
> ...
>
> This formula would need to be placed in some cell in Sheet2!1:1 in
> order for it to reference Sheet1!A1. Generally safer to use ROWS than
> ROW, so if the topmost result cell in Sheet2 were B5, the formula would
> be
>
> B5:
> =INDEX(Sheet1!$A:$A,ROWS(B$5:B5)*21-20,1)
>

Zack Barresse wrote...
>Interesting. I've used the two before, but not in such a fashion/context.
>I know there are many, many permutations which you can use. Such as ...
....

You're right. I confused your ROW(A1) with ROW(). Sorry.

Not a problem for this particular formula, but there are situations in
which ROW(...) can cause problems that ROWS(...) avoids when used in
INDIRECT or OFFSET calls inside other function calls.

>They should all be safe from row insertions.
>=INDEX(Sheet1!$A:$A,(ROW(A1)-1)*21+1,1)

That one isn't.

=INDEX(Sheet1!$A:$A,(ROWS($1:1)-1)*21+1)

Biff

"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> Zack Barresse wrote...
>>Interesting. I've used the two before, but not in such a fashion/context.
>>I know there are many, many permutations which you can use. Such as ...
> ...
>
> You're right. I confused your ROW(A1) with ROW(). Sorry.
>
> Not a problem for this particular formula, but there are situations in
> which ROW(...) can cause problems that ROWS(...) avoids when used in
> INDIRECT or OFFSET calls inside other function calls.
>

But what if on Sheet 1 the information to be transferred doesn't start till
A4 and is be put in at Sheet 2 A3? I hate to be picky, but I'm not
experienced in Excel. I really appreciate your help.

~Korie






"Zack Barresse" wrote:

> Hi Korie,
>
> You could use something like this ...
>
> =INDEX(Sheet1!$A:$A,(ROW(A1)-1)*21+1,1)
>
> This is assuming that you want to only see the data in each sequential row
> in sheet two. Put this formula in a cell, then copy down as needed. It
> will only show every 21st row, starting with row 1 of Sheet1.
>
> HTH
>
> --
> Regards,
> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>
>
> "Korie" <[email protected].(donotspam)> wrote in message
> news:[email protected]...
> > I'm working with two sheets. I have certain cell's information from sheet
> > 1
> > going to sheet 2. I was doing fine until I needed to add an insane amount
> > more information to sheet 1. I can't figure out a formula that will
> > calculate the information I need onto sheet 2.
> >
> > Example: Sheet 1/Column A/Row 1's information is also on Sheet 2 ( formula
> > I
> > use on sheet 2: =Sheet1!A1). If one Sheet 1, I use only Column
> > A/Rows1,22,43,64,..., 35872, what formula (or a step/setting) can I use so
> > I
> > can cut/paste instead of manually adding 21 to my previous formula on
> > sheet 2?
> >
> > If I'm confusing you, I'm sorry. I don't know that much about Excel and
> > really need help. Thanks. Please either respond or e-mail me directly.
> >
> > ~Korie~
>
>
>

But what if on Sheet 1 the information to be transferred doesn't start till
A4 (and then skips to 25, then 46, 67, 88,...) and is be put in at Sheet 2 A3
(then A4, A5, all the way down to the end)? I really appreciate everyone's
help. I am so behind on my Excel lessons. Thanks.

~Korie






"Harlan Grove" wrote:

> Zack Barresse wrote...
> >You could use something like this ...
> >
> >=INDEX(Sheet1!$A:$A,(ROW(A1)-1)*21+1,1)
> >
> >This is assuming that you want to only see the data in each sequential row
> >in sheet two. Put this formula in a cell, then copy down as needed. It
> >will only show every 21st row, starting with row 1 of Sheet1.
> ....
>
> This formula would need to be placed in some cell in Sheet2!1:1 in
> order for it to reference Sheet1!A1. Generally safer to use ROWS than
> ROW, so if the topmost result cell in Sheet2 were B5, the formula would
> be
>
> B5:
> =INDEX(Sheet1!$A:$A,ROWS(B$5:B5)*21-20,1)
>
>

As long as the structure hasn't changed, you don't need to change the bulk
of the formula, just alter the row number configuration. Using Biff's
formula ...

=INDEX(Sheet1!$A:$A,(ROWS($1:1)-1)*21+5)

... should give you every 21st row starting with row 1.

HTH

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


"Korie" <[email protected].(donotspam)> wrote in message
news:[email protected]...
> But what if on Sheet 1 the information to be transferred doesn't start
> till
> A4 and is be put in at Sheet 2 A3? I hate to be picky, but I'm not
> experienced in Excel. I really appreciate your help.
>
> ~Korie
>
>
>
>
>
>
> "Zack Barresse" wrote:
>
>> Hi Korie,
>>
>> You could use something like this ...
>>
>> =INDEX(Sheet1!$A:$A,(ROW(A1)-1)*21+1,1)
>>
>> This is assuming that you want to only see the data in each sequential
>> row
>> in sheet two. Put this formula in a cell, then copy down as needed. It
>> will only show every 21st row, starting with row 1 of Sheet1.
>>
>> HTH
>>
>> --
>> Regards,
>> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>>
>>
>> "Korie" <[email protected].(donotspam)> wrote in message
>> news:[email protected]...
>> > I'm working with two sheets. I have certain cell's information from
>> > sheet
>> > 1
>> > going to sheet 2. I was doing fine until I needed to add an insane
>> > amount
>> > more information to sheet 1. I can't figure out a formula that will
>> > calculate the information I need onto sheet 2.
>> >
>> > Example: Sheet 1/Column A/Row 1's information is also on Sheet 2 (
>> > formula
>> > I
>> > use on sheet 2: =Sheet1!A1). If one Sheet 1, I use only Column
>> > A/Rows1,22,43,64,..., 35872, what formula (or a step/setting) can I use
>> > so
>> > I
>> > can cut/paste instead of manually adding 21 to my previous formula on
>> > sheet 2?
>> >
>> > If I'm confusing you, I'm sorry. I don't know that much about Excel
>> > and
>> > really need help. Thanks. Please either respond or e-mail me
>> > directly.
>> >
>> > ~Korie~
>>
>>
>>