Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 5th 05, 10:40 PM
Korie
 
Posts: n/a
Default 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   Report Post  
Old October 5th 05, 11:30 PM
Zack Barresse
 
Posts: n/a
Default

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   Report Post  
Old October 5th 05, 11:47 PM
Harlan Grove
 
Posts: n/a
Default

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   Report Post  
Old October 6th 05, 12:20 AM
Zack Barresse
 
Posts: n/a
Default

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   Report Post  
Old October 6th 05, 05:33 AM
Harlan Grove
 
Posts: n/a
Default

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   Report Post  
Old October 6th 05, 07:41 AM
Biff
 
Posts: n/a
Default


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   Report Post  
Old October 6th 05, 02:28 PM
Korie
 
Posts: n/a
Default

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   Report Post  
Old October 6th 05, 02:32 PM
Korie
 
Posts: n/a
Default

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   Report Post  
Old October 6th 05, 04:48 PM
Zack Barresse
 
Posts: n/a
Default

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~








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
IF formula? meris Excel Worksheet Functions 1 September 6th 05 07:14 AM
writing a formula for a colored value aaronwexler New Users to Excel 11 September 1st 05 03:11 PM
referencing named formula using INDIRECT function [email protected] Excel Worksheet Functions 19 May 11th 05 09:48 AM
Simplify formula Luke Excel Worksheet Functions 37 May 6th 05 07:21 AM
Match / Vlookup within an Array formula 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