Remember Me?

#1
October 5th 05, 10:40 PM
 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~

#2
October 5th 05, 11:30 PM
 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~

#3
October 5th 05, 11:47 PM
 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)

#4
October 6th 05, 12:20 AM
 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)

#5
October 6th 05, 05:33 AM
 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.

#6
October 6th 05, 07:41 AM
 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.

#7
October 6th 05, 02:28 PM
 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~

#8
October 6th 05, 02:32 PM
 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)

#9
October 6th 05, 04:48 PM
 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~

 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.