Solved

Excel: Row References Are Mangled When Cells Are Moved

Posted on 2011-02-24
16
405 Views
Last Modified: 2012-05-11
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")


0
Comment
Question by:WizeOwl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
  • 3
16 Comments
 
LVL 24

Expert Comment

by:jimyX
ID: 34977193
That cab be easily fixed by using this sign "$" to indicate using this exact cell:

For example:
=concatenate(A1, "text1")

will be:
=concatenate($A$1, "text1")
0
 
LVL 24

Expert Comment

by:jimyX
ID: 34977220
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.
0
 

Author Comment

by:WizeOwl
ID: 34977238
That looks simple enough, let me give it a try.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:WizeOwl
ID: 34977320
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.
0
 
LVL 24

Expert Comment

by:jimyX
ID: 34977336
Then use that sign for the columns only as in "=$A1+$B1"

For your first line:
=concatenate($A1, "text1")
0
 

Author Comment

by:WizeOwl
ID: 34977359
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")
0
 

Author Comment

by:WizeOwl
ID: 34977362
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.
0
 

Author Comment

by:WizeOwl
ID: 34977426
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.
0
 
LVL 24

Accepted Solution

by:
jimyX earned 500 total points
ID: 34977526
OK, you can use "INDIRECT("A"&ROW())".

To get the cell at the current row in column A use:
=CONCATENATE(INDIRECT("A"&ROW()), "text1")
0
 

Author Comment

by:WizeOwl
ID: 34977582
That seems to work.  
0
 

Author Closing Comment

by:WizeOwl
ID: 34977588
After a bizarre turn of events, out of the murky mire the solution appeared. Thanks for the elegant solution.
0
 
LVL 50
ID: 34978169
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()), "text1")

cheers, teylyn
0
 
LVL 50
ID: 34985129
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

0
 

Author Comment

by:WizeOwl
ID: 34985331
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?
0
 
LVL 50
ID: 34985344
Yes, that's what I meant. Sorry for the confusion.
0
 

Author Comment

by:WizeOwl
ID: 34985534
Thank you for the input.

Two solutions:

=CONCATENATE(index(A:A,row()), "text1")

=CONCATENATE(indirect("A"&row()), "text1")
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

742 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question