Solved

Excel: Row References Are Mangled When Cells Are Moved

Posted on 2011-02-24
16
406 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
Technology Partners: 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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

690 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