Sorting Indirect Cells?
Posted by Charles Davis on May 18, 2001 7:45 AM
I have several large lists of data that I need to extract some data from and then sort it.
Each record in these lists is similar to:
LastName FirstName Value
I need the name combined, so I have added...
=CONCATENATE(A1," ",B1)
to give...
LastName FirstName Value Name
I don't want to change the raw data (the sequence of records has meaning).
I just want to sort the "Value" and "Name" columns.
My approach has been to make copies to the right...
LastName FirstName Value Name [=D1] [=C1]
I'd like to sort columns E and F by the values in column F.
I can get it to work if I add 2 columns (G & H)...
=INDIRECT("E1") and =INDIRECT("F1")
My problem is that I can't figure out how to write a macro that creates these 2 new columns.
I'm stumped!
Posted by Dave Hawley on May 18, 2001 8:04 AM
Hi Charles
This code will add two new Columns:
Columns("G:H").EntireColumn.Insert
Dave
OzGrid Business Applications
Posted by Charles Davis on May 18, 2001 5:45 PM
Thanks. I obviously didn't express the problem very well.
What I want to do is NOT create 2 new blank columns.
I want to create a macro that creates in existing columns,
cells that contain a formula which reads something like: =INDIRECT("A1")
I can write a macro that creates: =INDIRECT(A1), but getting
the quotes doesn't seem to work.
Actually, I'm not stuck on using the =INDIRECT("A1") method,
but it DOES allow the two columns to be sorted correctly
when I manually create cells with that formula in them.
But I can't make a macro to create the formula with the quotes.
Charles
Posted by Dave Hawley on May 19, 2001 10:20 AM
In that case....
Is this what you mean ?
= "=INDIRECT(""A1"")"
Dave
OzGrid Business Applications
Posted by Charles Davis on May 21, 2001 11:56 AM
Re: In that case....
= "=INDIRECT(""A1"")"
Nope. That just displays =INDIRECT("A1") in the cell.
Not at all what I want. I want the contents of cell A1 to display
and be able to sort it (and other similar cells).
thanks for trying...Charles
