Excel: Row References Are Mangled When Cells Are Moved
Excel 2003
Windows XP
I received code from Question: Q_26669899.html
However, it is not working properly. I would like someone to show me how to fix it, or to write another version.
When I move a column of cells that contain the Concatenate function, the Row references are no longer correct.
QUESTION:
I need a VBA Macro that aligns the row references to the actual row in which the concatenate function resides.
USAGE:
I need to be able to select consecutive cells of the column affected.
I should be able to select the macro using ALT-F8.
Here is the text that appears in the original column:
Data A1 Data B1 =concatenate(A1, "text1")
Data A2 Data B2 =concatenate(A2, "text2")
Data A3 Data B3 =concatenate(A3, "text3")
Data A4 Data B4 =concatenate("String4", A4, "text4")
Data A5 Data B5 =concatenate("String5", A5, "text5")
Data A6 Data B6 =concatenate("String6", A6, "text6")
Data A7 Data B7 =concatenate(A7, "String7", B7, "text7")
Data A8 Data B8 =concatenate(A8, "String8", B8, "text8")
Data A9 Data B9 =concatenate(A9, "String9", B9, "text9")
Here's an example of what happens when I move those cells: (notice that the values in the function refer to incorrect rows.)
Data A10 Data B10 =concatenate(A1, "text1")
Data A11 Data B11 =concatenate(A2, "text2")
Data A12 Data B12 =concatenate(A3, "text3")
Data A13 Data B13 =concatenate("String4", A4, "text4")
Data A14 Data B14 =concatenate("String5", A5, "text5")
Data A15 Data B15 =concatenate("String6", A6, "text6")
Data A16 Data B16 =concatenate(A7, "String7", B7, "text7")
Data A17 Data B17 =concatenate(A8, "String8", B8, "text8")
Data A18 Data B18 =concatenate(A9, "String9", B9, "text9")
Depending on how these cells are copied and pasted, the row references can be scrambled and out of order completely:
Data A10 Data B10 =concatenate(A8, "String8", B8, "text8")
Data A11 Data B11 =concatenate("String4", A4, "text4")
Data A12 Data B12 =concatenate(A7, "String7", B7, "text7")
Data A13 Data B13 =concatenate("String5", A5, "text5")
Data A14 Data B14 =concatenate(A1, "text1")
Data A15 Data B15 =concatenate(A3, "text3")
Data A16 Data B16 =concatenate("String6", A6, "text6")
Data A17 Data B17 =concatenate(A2, "text2")
Data A18 Data B18 =concatenate(A9, "String9", B9, "text9")
Windows XP
I received code from Question: Q_26669899.html
However, it is not working properly. I would like someone to show me how to fix it, or to write another version.
When I move a column of cells that contain the Concatenate function, the Row references are no longer correct.
QUESTION:
I need a VBA Macro that aligns the row references to the actual row in which the concatenate function resides.
USAGE:
I need to be able to select consecutive cells of the column affected.
I should be able to select the macro using ALT-F8.
Here is the text that appears in the original column:
Data A1 Data B1 =concatenate(A1, "text1")
Data A2 Data B2 =concatenate(A2, "text2")
Data A3 Data B3 =concatenate(A3, "text3")
Data A4 Data B4 =concatenate("String4", A4, "text4")
Data A5 Data B5 =concatenate("String5", A5, "text5")
Data A6 Data B6 =concatenate("String6", A6, "text6")
Data A7 Data B7 =concatenate(A7, "String7", B7, "text7")
Data A8 Data B8 =concatenate(A8, "String8", B8, "text8")
Data A9 Data B9 =concatenate(A9, "String9", B9, "text9")
Here's an example of what happens when I move those cells: (notice that the values in the function refer to incorrect rows.)
Data A10 Data B10 =concatenate(A1, "text1")
Data A11 Data B11 =concatenate(A2, "text2")
Data A12 Data B12 =concatenate(A3, "text3")
Data A13 Data B13 =concatenate("String4", A4, "text4")
Data A14 Data B14 =concatenate("String5", A5, "text5")
Data A15 Data B15 =concatenate("String6", A6, "text6")
Data A16 Data B16 =concatenate(A7, "String7", B7, "text7")
Data A17 Data B17 =concatenate(A8, "String8", B8, "text8")
Data A18 Data B18 =concatenate(A9, "String9", B9, "text9")
Depending on how these cells are copied and pasted, the row references can be scrambled and out of order completely:
Data A10 Data B10 =concatenate(A8, "String8", B8, "text8")
Data A11 Data B11 =concatenate("String4", A4, "text4")
Data A12 Data B12 =concatenate(A7, "String7", B7, "text7")
Data A13 Data B13 =concatenate("String5", A5, "text5")
Data A14 Data B14 =concatenate(A1, "text1")
Data A15 Data B15 =concatenate(A3, "text3")
Data A16 Data B16 =concatenate("String6", A6, "text6")
Data A17 Data B17 =concatenate(A2, "text2")
Data A18 Data B18 =concatenate(A9, "String9", B9, "text9")
That's called the absolute reference.
so if you want to move the formula "=A1+B1" to any location without changing A1 & B1 then make it "=$A$1+$B$1".
Also you can use it this way:
"=$A1+$B1" when you copy this to any other cell A&B will not change but the Row will adjust to the new location.
"=A$1+B$1" when moved to another cell the Row will not change but A&B will be adjusted to the new cell.
Fix one cell and change the other:
"=$A$1+B1"
And so on you can work as per your needs.
so if you want to move the formula "=A1+B1" to any location without changing A1 & B1 then make it "=$A$1+$B$1".
Also you can use it this way:
"=$A1+$B1" when you copy this to any other cell A&B will not change but the Row will adjust to the new location.
"=A$1+B$1" when moved to another cell the Row will not change but A&B will be adjusted to the new cell.
Fix one cell and change the other:
"=$A$1+B1"
And so on you can work as per your needs.
ASKER
That looks simple enough, let me give it a try.
ASKER
It seems that Dollar signs are used keep Row and Column values constant.
What I need is for the row values to adjust when moved to a different row.
What I need is for the row values to adjust when moved to a different row.
Then use that sign for the columns only as in "=$A1+$B1"
For your first line:
=concatenate($A1, "text1")
For your first line:
=concatenate($A1, "text1")
ASKER
The Two examples of scrambled data SHOULD LOOK LIKE THIS:
Data A10 Data B10 =concatenate(A10, "text1")
Data A11 Data B11 =concatenate(A11, "text2")
Data A12 Data B12 =concatenate(A12, "text3")
Data A13 Data B13 =concatenate("String4", A13, "text4")
Data A14 Data B14 =concatenate("String5", A14, "text5")
Data A15 Data B15 =concatenate("String6", A15, "text6")
Data A16 Data B16 =concatenate(A7, "String7", B16, "text7")
Data A17 Data B17 =concatenate(A8, "String8", B17, "text8")
Data A18 Data B18 =concatenate(A9, "String9", B18, "text9")
and THIS:
Data A10 Data B10 =concatenate(A10, "String8", B10, "text8")
Data A11 Data B11 =concatenate("String4", A11, "text4")
Data A12 Data B12 =concatenate(A12, "String7", B12, "text7")
Data A13 Data B13 =concatenate("String5", A13, "text5")
Data A14 Data B14 =concatenate(A14, "text1")
Data A15 Data B15 =concatenate(A15, "text3")
Data A16 Data B16 =concatenate("String6", A16, "text6")
Data A17 Data B17 =concatenate(A17, "text2")
Data A18 Data B18 =concatenate(A18, "String9", B18, "text9")
Data A10 Data B10 =concatenate(A10, "text1")
Data A11 Data B11 =concatenate(A11, "text2")
Data A12 Data B12 =concatenate(A12, "text3")
Data A13 Data B13 =concatenate("String4", A13, "text4")
Data A14 Data B14 =concatenate("String5", A14, "text5")
Data A15 Data B15 =concatenate("String6", A15, "text6")
Data A16 Data B16 =concatenate(A7, "String7", B16, "text7")
Data A17 Data B17 =concatenate(A8, "String8", B17, "text8")
Data A18 Data B18 =concatenate(A9, "String9", B18, "text9")
and THIS:
Data A10 Data B10 =concatenate(A10, "String8", B10, "text8")
Data A11 Data B11 =concatenate("String4", A11, "text4")
Data A12 Data B12 =concatenate(A12, "String7", B12, "text7")
Data A13 Data B13 =concatenate("String5", A13, "text5")
Data A14 Data B14 =concatenate(A14, "text1")
Data A15 Data B15 =concatenate(A15, "text3")
Data A16 Data B16 =concatenate("String6", A16, "text6")
Data A17 Data B17 =concatenate(A17, "text2")
Data A18 Data B18 =concatenate(A18, "String9", B18, "text9")
ASKER
QUESTION:
I need a VBA Macro that aligns the row references to the actual row in which the concatenate function resides.
USAGE:
I need to be able to select consecutive cells of the column affected.
I should be able to select the macro using ALT-F8.
I need a VBA Macro that aligns the row references to the actual row in which the concatenate function resides.
USAGE:
I need to be able to select consecutive cells of the column affected.
I should be able to select the macro using ALT-F8.
ASKER
You said - so if you want to move the formula "=A1+B1" to any location without changing A1 & B1 then make it "=$A$1+$B$1".
This is not what I want. I want the exact opposite.
This is not what I want. I want the exact opposite.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That seems to work.
ASKER
After a bizarre turn of events, out of the murky mire the solution appeared. Thanks for the elegant solution.
Hello,
I know this question is closed already, but just FYI: Indirect() is volatile, i.e. it will recalculate every time ANY cell in the sheet is changed. If you have a lot of formulas using Indirect(), you will eventually notice that the calculation and speed of the spreadsheet slows down.
Instead of staring at a "Calculating ... 35%" message, treat yourself to the Index() function, which can deliver the same result.
=CONCATENATE(index(A:A,ROW
cheers, teylyn
I know this question is closed already, but just FYI: Indirect() is volatile, i.e. it will recalculate every time ANY cell in the sheet is changed. If you have a lot of formulas using Indirect(), you will eventually notice that the calculation and speed of the spreadsheet slows down.
Instead of staring at a "Calculating ... 35%" message, treat yourself to the Index() function, which can deliver the same result.
=CONCATENATE(index(A:A,ROW
cheers, teylyn
I need to amend my statement above, after a friendly nudge from another expert.
Indirect() is still volatile, but so is Row(), hence using Index() with Row() is also volatile.
cheers, teylyn
Indirect() is still volatile, but so is Row(), hence using Index() with Row() is also volatile.
cheers, teylyn
ASKER
Teylyn, are you saying that Index() with Row() does NOT save time over Indirect() ?
Is there still an advantage of using Index instead of Indirect?
Is there still an advantage of using Index instead of Indirect?
Yes, that's what I meant. Sorry for the confusion.
ASKER
Thank you for the input.
Two solutions:
=CONCATENATE(index(A:A,row
=CONCATENATE(indirect("A"&
Two solutions:
=CONCATENATE(index(A:A,row
=CONCATENATE(indirect("A"&
For example:
=concatenate(A1, "text1")
will be:
=concatenate($A$1, "text1")