Solved

Excel: Row References Are Mangled When Cells Are Moved

Posted on 2011-02-24
16
390 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
  • 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Expert Comment

by:teylyn
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

Expert Comment

by:teylyn
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

Expert Comment

by:teylyn
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now