Fill Series Problem

Bet this is simple to someone...

How can a continue this fill series down a column?

=$BC$3
=$BC$4
=$BC$5
=$BC$6

I would copy, paste and edit only there are 2500 rows!!

Just type in those few, select them, then pull down.

Worked for me!

It just repeats the same 4 for me, over and over

Delete the second $ from the formula and then drag.

Thanks guys.

Chimneys works but the column is not sortable unless the cells contain what I originally posted

Hopeful bump...

I'm not sure what you mean by "The column is not sortable unless the cells contain what you originally posted"?

If you drag down =$BC1, you will get

Please Login or Register  to view this content.

That will function exactly the same as if you typed in

Please Login or Register  to view this content.

You won't be able to sort by that column unless you also include BC or if you copy and paste special values before sorting. Does that help?

ChemistB

The column BC contains the results of a bunch of calculations.
As they are performed I need the column (automatically) sorted in ascending numerical order.

The series

Please Login or Register  to view this content.

was suggested by a friend and when I tried it (manually typing!) on the first 100 or so rows - it allowed me to sort the column numerically.

The series

Please Login or Register  to view this content.

Does not allow the column to be sorted.

All I want is an ascending numerical order for the results column.

maybe you can approach this another way.

If you take the column with the results, use the RANK() FUNCTION in a new column and copy this all the way down. This will show the ranking of that row in the whole range.

Then have another spreadsheet setup which does a vlookup from 1-200 or to the last number and it will pull all the information you need. As the rankings change, the vlookup spreadsheet will automatically update in numerical order.

hope this helps.

My apologies. You are correct. I can see two options.

Option 1, the simplist, is Copy your original column (BC) and paste special >Values. Then sort

Option 2, record a macro that either does the above and sorts or takes your formulas after you dragged them down and adds the necessary second $. You can attach this macro to a button or a toolbar icon.

I'll keep thinking on this one.

ChemistB

Originally Posted by ChemistB

My apologies. You are correct. I can see two options.

Option 1, the simplist, is Copy your original column (BC) and paste special >Values. Then sort

That's how I tested the rest of the calcs but I want this bit automated.

Originally Posted by ChemistB

Option 2, record a macro that either does the above and sorts.

That looks a good option!

Can a Macro be "permanently live"? ie constantly looking and updating the ascending order as new or edited input values are typed?

If not a "CALC" button with the Macro behind it might be a reasonable second best.

Thanks for all your input guys - much appreciated.

It can't be permanently live (running in the background) but it can be set up that it runs everytime there is a change made on the spreadsheet. Are we talking about imported data or manually entered data or pasted data?

ChemistB

Manually entered (or edited) so what you suggest sounds absolutely fine.



The first column of the sheet allows up to 50 discrete numbers to be input by the user.

All of the input numbers are tested, with various equations, against each other and the results are in one big 2500 row column.

That's the one I need in ascending order.

I'm then using the LOOKUP function to return the nearest number above and below the original - if that makes any sense!

I have it all working fine apart from the auto-sorting.

Here's an example worksheet. Just right click on the tab and View code to see

Please Login or Register  to view this content.

You would replace some of the Cell ids with yours (E.g. B2:B2500 with BC2:BC2500 and G2 with whatever column you want to paste into).

I know there are more efficient ways to do this but I'm an amateur with VBA and it works.

Great! Glad you got something working.

ChemistB

Originally Posted by Keebs

Not a million miles from yours and works like a charm

The solution i proposed will keep it updated every time you enter a new value. but it requires 2 sheets.