Copy Formula Down

[nacskins]

I have searched and i am unable to find an answer, is there a way to copy a formula down a column and have it auto skip every other ref cell. Example below. Thanks

=CONCATENATE(A2, ",")
=CONCATENATE(A4, ",")

 

[nacskins]

OK, then this version should do it.
Put this in cell B2 and copy down:

=CONCATENATE(INDIRECT("A" & (ROW()-1)*2),",")

Thanks, looks like that will work.

Any suggestions for the Column C and D to do the same


=SUBSTITUTE(A2, "2TK", "MDCENB00") = Column C, C2, C3 and so on wanting to pull from A2, A4, A6 and so on.

Then Column D

=SUBSTITUTE(A3, ":", "") i need to pull from A3, A5, A7 and so on. and put the formula in D2, D3, D4......

 

[Joe4]

Thanks, looks like that will work.

Any suggestions for the Column C and D to do the same


=SUBSTITUTE(A2, "2TK", "MDCENB00") = Column C, C2, C3 and so on wanting to pull from A2, A4, A6 and so on.

Then Column D

=SUBSTITUTE(A3, ":", "") i need to pull from A3, A5, A7 and so on. and put the formula in D2, D3, D4......

The logic should be exactly the same, so you should be able to incorporate the logic I can you on these other ones.

I will explain to you the logic.
What are trying to do is dynamically build the cell references (i.e. "A2", "A4", etc).
The problem is if we use formulas to build those cell references, they are treated as "literal text" and not cell references.
We correct that problem by wrapping it in an INDIRECT function, which tells Excel we are building a range reference, not a string.

So let's break it down.

INDIRECT("A" & (ROW()-1)*2)

We want to build a formula that returns "A2" when placed in cell "B2", returns "A4" when placed in cell "B3", etc.
So, the way to do this is to first build the "inner" part of the indirect formula (just return the string so you can see what it is doing).

We know that it will always be column "A", so we can hard-code that part.
The ROW() function will return the row number that the formula is placed in.
So, if we enter the following formula in cell B2:
="A" & ROW()
it will return "A2" (it copied down, it will return "A3", "A4", etc).
So it doesn't exactly work when copied down, but we will come back to that later.

Because we want the row value to increment by 2 for every row we copy the formula down, we need to multiply the ROW() function by 2, i.e.
="A" & ROW()*2
However, then that will put "A4" in cell B2, "A6" in cell B3, etc.
So we need to adjust that by subtracing 1 from the ROW() part, i.e.
="A" & (ROW()-1)*2
Now, this returns what we want ("A2" in cell B2, "A4" in cell B3, etc).

Now that we have the calculation working the way we need, we simply wrap it in the INDIRECT function to make it a range reference, i.e.

INDIRECT("A" & (ROW()-1)*2)

So the way that I usually approach this is to first build the inner part of the indirect function to return the values you need for each cell (like I showed above).
Then, once you have that working well, just wrap it in the INDIRECT function, and you are good to go!

Give it a shot with your other formulas and see how you do (you will learn a lot more trying this yourself than having me do it for you again).
The first one should be very easy, as it is practically the exact same calculation, just a different column reference.

Just note that the INDIRECT function should replace your current cell reference. So the structure will be like:
=SUBSTITUTE(INDIRECT(...), "2TK", "MDCENB00")

 

[nacskins]

The logic should be exactly the same, so you should be able to incorporate the logic I can you on these other ones.

I will explain to you the logic.
What are trying to do is dynamically build the cell references (i.e. "A2", "A4", etc).
The problem is if we use formulas to build those cell references, they are treated as "literal text" and not cell references.
We correct that problem by wrapping it in an INDIRECT function, which tells Excel we are building a range reference, not a string.

So let's break it down.

INDIRECT("A" & (ROW()-1)*2)

We want to build a formula that returns "A2" when placed in cell "B2", returns "A4" when placed in cell "B3", etc.
So, the way to do this is to first build the "inner" part of the indirect formula (just return the string so you can see what it is doing).

We know that it will always be column "A", so we can hard-code that part.
The ROW() function will return the row number that the formula is placed in.
So, if we enter the following formula in cell B2:
="A" & ROW()
it will return "A2" (it copied down, it will return "A3", "A4", etc).
So it doesn't exactly work when copied down, but we will come back to that later.

Because we want the row value to increment by 2 for every row we copy the formula down, we need to multiply the ROW() function by 2, i.e.
="A" & ROW()*2
However, then that will put "A4" in cell B2, "A6" in cell B3, etc.
So we need to adjust that by subtracing 1 from the ROW() part, i.e.
="A" & (ROW()-1)*2
Now, this returns what we want ("A2" in cell B2, "A4" in cell B3, etc).

Now that we have the calculation working the way we need, we simply wrap it in the INDIRECT function to make it a range reference, i.e.

INDIRECT("A" & (ROW()-1)*2)

So the way that I usually approach this is to first build the inner part of the indirect function to return the values you need for each cell (like I showed above).
Then, once you have that working well, just wrap it in the INDIRECT function, and you are good to go!

Give it a shot with your other formulas and see how you do (you will learn a lot more trying this yourself than having me do it for you again).
The first one should be very easy, as it is practically the exact same calculation, just a different column reference.

Just note that the INDIRECT function should replace your current cell reference. So the structure will be like:
=SUBSTITUTE(INDIRECT(...), "2TK", "MDCENB00")

Thank you!!!!!!!

 

[Joe4]

You are welcome.

If you run into any issues implementing this logic on your other two formulas, post back here, and show me the formula you tried, and I can help you adjust it.

 

[nacskins]

The logic should be exactly the same, so you should be able to incorporate the logic I can you on these other ones.

I will explain to you the logic.
What are trying to do is dynamically build the cell references (i.e. "A2", "A4", etc).
The problem is if we use formulas to build those cell references, they are treated as "literal text" and not cell references.
We correct that problem by wrapping it in an INDIRECT function, which tells Excel we are building a range reference, not a string.

So let's break it down.

INDIRECT("A" & (ROW()-1)*2)

We want to build a formula that returns "A2" when placed in cell "B2", returns "A4" when placed in cell "B3", etc.
So, the way to do this is to first build the "inner" part of the indirect formula (just return the string so you can see what it is doing).

We know that it will always be column "A", so we can hard-code that part.
The ROW() function will return the row number that the formula is placed in.
So, if we enter the following formula in cell B2:
="A" & ROW()
it will return "A2" (it copied down, it will return "A3", "A4", etc).
So it doesn't exactly work when copied down, but we will come back to that later.

Because we want the row value to increment by 2 for every row we copy the formula down, we need to multiply the ROW() function by 2, i.e.
="A" & ROW()*2
However, then that will put "A4" in cell B2, "A6" in cell B3, etc.
So we need to adjust that by subtracing 1 from the ROW() part, i.e.
="A" & (ROW()-1)*2
Now, this returns what we want ("A2" in cell B2, "A4" in cell B3, etc).

Now that we have the calculation working the way we need, we simply wrap it in the INDIRECT function to make it a range reference, i.e.

INDIRECT("A" & (ROW()-1)*2)

So the way that I usually approach this is to first build the inner part of the indirect function to return the values you need for each cell (like I showed above).
Then, once you have that working well, just wrap it in the INDIRECT function, and you are good to go!

Give it a shot with your other formulas and see how you do (you will learn a lot more trying this yourself than having me do it for you again).
The first one should be very easy, as it is practically the exact same calculation, just a different column reference.

Just note that the INDIRECT function should replace your current cell reference. So the structure will be like:
=SUBSTITUTE(INDIRECT(...), "2TK", "MDCENB00")

Ok i was able to get it to work for a bit...

=SUBSTITUTE(INDIRECT("A" & (ROW()-1)*2),"2TK","MDCENB00")

copied down but stops working after 13th row down in the column, any reason you can think of for that?

 

[nacskins]

=CONCATENATE(INDIRECT("A" & (ROW()-1)*2),",")

Also stops working after rown 12....hummmmm

 

[nacskins]

nevermind im a dummy

 

[nacskins]

Ok so got column C working with this one using your explination.

=SUBSTITUTE(INDIRECT("A" & (ROW()-1)*2),"2TK","MDCENB00")

im having a hard time with D using this one based off same explination to my understanging (or lack there of)

=SUBSTITUTE(INDIRECT("A" & (ROW()-1)*3),":","")

 

[Joe4]

Ok so got column C working with this one using your explination.

=SUBSTITUTE(INDIRECT("A" & (ROW()-1)*2),"2TK","MDCENB00")

im having a hard time with D using this one based off same explination to my understanging (or lack there of)

=SUBSTITUTE(INDIRECT("A" & (ROW()-1)*3),":","")

OK, because we are wanting the odd number rows now, we need to subtract 1 AFTER we multiply by 2 (rather than before), i.e.

=SUBSTITUTE(INDIRECT("A" & (ROW()*2)-1),":","")

 

[nacskins]

Working!!!!! TY Vm, interesting tho it quits working after about row 600, should not need that much tho... TY again!!!!!