Find last cell "=INDEX(G:G,MATCH(9.99999999999999E+307,G:G))

[mapakunk]

I have a spreadsheet that I would like to assign a macro to. So that when I click it it will automatically auto sort a certain column. I am new to this so go easy on me please

[ This Message was edited by: mapakunk on 2002-05-14 05:45 ]

[ This Message was edited by: mapakunk on 2002-05-14 06:00 ]

[Nobby]

First, you need to name the column, under Insert/Name/Define.

Then start to record your macro.
1st step - Edit/Go To/ type in column name
2nd step - Data/Sort/whatever order you want
Stop macro.

Then add a button to the worksheet via View/Toolbars/Forms/select the button and draw on sheet. When asked to assign a macro, select the one you just recorded.

Thie above only works if the column of data is always the same size. If you want a dynamic range, the Define Range formula needs to be based on the OFFSET function.

Hope this helps.

Nobby

[mapakunk]

That works perfectly!!

Now, how do I rename it from "Button 27" to
"Outstanding Checks" then I can use this principle from here in all my wonderful spreadsheets. Hee hee hee hu ah ha ha haaaaaaaaaa!

[Von Pookie]

Right-click on the button and select Edit Text.

[mapakunk]

Now one last thing, how can I make a formula that returns the last whole value in column G to a specific cell on the worksheet (look for last entry, then send cell x to c4?)

[ This Message was edited by: mapakunk on 2002-05-14 05:46 ]

[mapakunk]

please could someone answer the above question.

Thank you so much

[Aladin Akyurek]

On 2002-05-09 08:52, mapakunk wrote:
Now one last thing, how can I make a formula that returns the last whole value in column G to a specific cell on the worksheet (look for last entry, then send cell x to c4?)

[ This Message was edited by: mapakunk on 2002-05-14 05:46 ]

If G is of numeric type:

In C4 enter:

=OFFSET(G1,MATCH(9.99999999999999E+307,G:G)-1,1,1)

If G is of alphanumeric type:

=OFFSET(G1,MATCH(REPT("z"80),G:G)-1,1,1)

The second formula will ignore the last cell having a formula-generated blank.

Aladin