Home 
Search 
Today's Posts 
#1




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 email me directly. ~Korie~ 
#2




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 email me directly. ~Korie~ 
#3




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)*2120,1) 
#4




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)*2120,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)*2120,1) 
#5




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




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




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 email me directly. ~Korie~ 
#8




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)*2120,1) 
#9




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 email me directly. ~Korie~ 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
IF formula?  Excel Worksheet Functions  
writing a formula for a colored value  New Users to Excel  
referencing named formula using INDIRECT function  Excel Worksheet Functions  
Simplify formula  Excel Worksheet Functions  
Match / Vlookup within an Array formula  Excel Discussion (Misc queries) 