Remember Me?

 Korie Posts: n/a 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~

 Zack Barresse Posts: n/a 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~

 Harlan Grove Posts: n/a 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 Posts: n/a 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)

 Harlan Grove Posts: n/a 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.

 Biff Posts: n/a 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.

 Korie Posts: n/a 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~

 Korie Posts: n/a 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)

 Zack Barresse Posts: n/a 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~

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post meris Excel Worksheet Functions 1 September 6th 05 07:14 AM aaronwexler New Users to Excel 11 September 1st 05 03:11 PM [email protected] Excel Worksheet Functions 19 May 11th 05 09:48 AM Luke Excel Worksheet Functions 37 May 6th 05 07:21 AM Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 04:37 PM

All times are GMT +1. The time now is 11:04 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd. Copyright ©2004-2021 ExcelBanter.
The comments are property of their posters.

# About Us

"It's about Microsoft Excel"

Copyright © 2017