#1
|
|||
|
|||
Changing Cell address within a formula
I need to copy every third cell in column A to sheet2 to consecutive rows in column A of sheet2 by placing a formula in receiving cells. I cannot find a way to change the row number in the cell addresses of the formula.
|
#2
|
||||
|
||||
Starting in row 1 on sheet2, try
Code:
=indirect("sheet1!A"&row()*3)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
This brings forward cell A1,A4,A3,A2,A5,A8,A7,A6,A9..... .and I am seeking A1,A4,A7,A10....
Maybe I didn't describe this clearly, I need every fourth cell, with three in between the cells I need. Can you help? Ken |
#4
|
||||
|
||||
Is this better ?
Code:
=indirect("sheet1!A"&((row()-1)*3)+1)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#5
|
|||
|
|||
This what sheet1 has on it
A1 John Doe A2 no name A3 no name A4 no name A5 Jill Doe A6 no name A7 no name A8 no name A9 Tom Doe A10 no name A11 no name A12 no name A13 Mary Doe This is the results from the new formula =INDIRECT("sheet1!A"&((ROW()-1)*3)+1) on sheet2 A1 John Doe A4 no name A7 no name A10 no name A13 Mary Doe Not yet the desired results Last edited by vermont_past; 09-20-2012 at 12:28 PM. Reason: to correctly display spread sheets results |
#6
|
||||
|
||||
Pse post a sample sheet showing before and after
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#7
|
|||
|
|||
I put the cell address in each cell with the text (a name or "no name") and this is the results from a cut and paste from sheet1 and sheet2.
|
#8
|
||||
|
||||
No sheet attached
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#9
|
|||
|
|||
I think it is attached, now.
|
#10
|
||||
|
||||
Your sheet does still not show what you want to achieve , only the result of my suggestion which is not what you need
Is it Code:
=INDIRECT("sheet1!A"&((ROW()-1)*4)+1)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#11
|
|||
|
|||
The last had both sheet1 and sheet2, I believed.
This time I am sending file, sheet1, which is the project from which I wish to extract (copy) cells and place (paste) them on another sheet thus creating a list of names, addresses, etc. Thanks for hanging with me and you continued assistance. It seems that there MUST be a way. |
#12
|
||||
|
||||
Like this ?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#13
|
|||
|
|||
YES. I am off to work on it. Thank you bunches
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Keep cell in formula constant | jprt68 | Excel | 1 | 06-26-2012 08:31 AM |
Changing cell leftpadding moves whole table | kae0088 | Word Tables | 1 | 05-12-2012 11:25 PM |
need to check whether cell contains formula | etodem | Excel | 2 | 10-23-2010 07:33 PM |
Formula in a Cell with condition? | Learner7 | Excel | 1 | 07-19-2010 10:10 AM |
Code for Changing Cell Backgrounds | leroytrolley | Excel | 2 | 12-05-2008 02:05 AM |