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" .(donotspam) wrote in message
...
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" wrote in message
oups.com...
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" wrote in message
ups.com...
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 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)

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" .(donotspam) wrote in message
...
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~

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" .(donotspam) wrote in message
...
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" .(donotspam) wrote in message
...
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~