I have created this page to provide help on Excel & Excel VBA. I will periodically post some tricks in excel that will help users to reduce their efforts and increase accuracy....
Sir i am experiencing problem in incremental copying in excel. Problem: Suppose i have these values a1:angle a2:1 a3:2 a4:3 a5:4
i am creating a matrix of 2x2 for variation of each angles. Suppose this is 2x2 matrix for variation of 1degree angle.
d2=$a2+2 e2=$a2+3 d3=$a2+2 e3=$a2+3
Now i want to copy d2:e3 and paste below the first 2x2 matrix i.e., starting from cell d6 to get the variation of 2degree angle. So, i want in this order below which i am not able to get.
d6=$a3+2 e6=$a3+3 d7=$a3+2 e7=$a3+3
Please help on this....This is a basis for a larger matrix formulation.
What I can figure out from your question is that you are facing problem with cell referencing. To achieve this you can use Indirect formula.
1. Please add a column at the end (i.e. column F) in your data, which we will use for Indirect formula. You can hide this column after applying formula. 2. Type 2 in cell F2. 3. Type formula in F3 as “=F2”. We are just taking same value as F2. 4. Now go to cell F6 and type formula as “=F2+1” 5. In Cell F7, copy the formula of cell F3. Which will become “=F6” 6. Now apply indirect formula in cell D2 as “=INDIRECT("A" & F2) +2” - Indirect function converts text into range, we are passing static value “A” and concatenating it with value of cell F2 (i.e. 2). So it becomes “A2” 7. Similarly apply formula in cell E2 as “=INDIRECT("A" & F2) +3”. 8. Now you can copy cells D2 and E2 and paste it in cells D3, E3 and in D6, E6, D7 & E7 respectively. 9. To further expand this table, you can select cells with formulas in 4 rows together, i.e. from D6:F7 and keep pasting it in the table below. (values in Column F will get incremented by 1 after every 4 rows, which will ensure that cell referencing of formulas in column D & E will be incremented only after 4 rows)
That’s it. You can manipulate formula of column F to get the proper referencing. Hope this helps. Thanks !
Sir i am experiencing problem in incremental copying in excel.
ReplyDeleteProblem: Suppose i have these values
a1:angle
a2:1
a3:2
a4:3
a5:4
i am creating a matrix of 2x2 for variation of each angles.
Suppose this is 2x2 matrix for variation of 1degree angle.
d2=$a2+2 e2=$a2+3
d3=$a2+2 e3=$a2+3
Now i want to copy d2:e3 and paste below the first 2x2 matrix i.e., starting from cell d6 to get the variation of 2degree angle.
So, i want in this order below which i am not able to get.
d6=$a3+2 e6=$a3+3
d7=$a3+2 e7=$a3+3
Please help on this....This is a basis for a larger matrix formulation.
Hi Punith,
ReplyDeleteWhat I can figure out from your question is that you are facing problem with cell referencing. To achieve this you can use Indirect formula.
1. Please add a column at the end (i.e. column F) in your data, which we will use for Indirect formula. You can hide this column after applying formula.
2. Type 2 in cell F2.
3. Type formula in F3 as “=F2”. We are just taking same value as F2.
4. Now go to cell F6 and type formula as “=F2+1”
5. In Cell F7, copy the formula of cell F3. Which will become “=F6”
6. Now apply indirect formula in cell D2 as “=INDIRECT("A" & F2) +2” - Indirect function converts text into range, we are passing static value “A” and concatenating it with value of cell F2 (i.e. 2). So it becomes “A2”
7. Similarly apply formula in cell E2 as “=INDIRECT("A" & F2) +3”.
8. Now you can copy cells D2 and E2 and paste it in cells D3, E3 and in D6, E6, D7 & E7 respectively.
9. To further expand this table, you can select cells with formulas in 4 rows together, i.e. from D6:F7 and keep pasting it in the table below. (values in Column F will get incremented by 1 after every 4 rows, which will ensure that cell referencing of formulas in column D & E will be incremented only after 4 rows)
That’s it. You can manipulate formula of column F to get the proper referencing.
Hope this helps. Thanks !
Thank you for your help Ritesh... Learnt a new method from you. I am expaning this method to 15x15 matrix. It really worked out... :)
ReplyDeleteThank you once again!