Find and return the last used cell in a row, Formula or VBA?

**alan_stephen75@** · 07-23-2012, 01:59 PM

I am setting up a score sheet in Excel 2007 and would like to be able to enter numbered values in an 11 cell grid/row, these would be entered in any order and I would like them to appear in a column below but in the order in which they are entered, in other words as I enter scores they would appear in a numbered list below from the 1st to the 11th entry.

If it makes a solution easier as the entries are first made in the row they do not need to appear in the column list immediately and could perhaps be copied from a separate "sorted" list after the last entry is made if you follow this as long as they at the end they are numbered in the order of entry, I have been advised that it probably can't be done using a formula, which I would prefer, but can perhaps be with VBA, unless anyone can think of a workaround, open to all suggestions.

I attach a sample and would appreciate it if anyone can help.

**Pepe Le Mokko** · 07-23-2012, 02:59 PM

SelectF83 to F94 and enter

Please Login or Register  to view this content.

which is an array function and should be eneterd with Ctrl+Shift+Enter - Is that something you can live with?

**alan_stephen75@** · 07-23-2012, 03:14 PM

Many thanks for your help but I have followed your instructions and entered as an array but only getting "0". Am doing something wrong?

**Pepe Le Mokko** · 07-23-2012, 03:18 PM

Works for me - Commit with the entire target range (vertical) selected Copy of Game Test-1.xlsm

**alan_stephen75@** · 07-23-2012, 03:47 PM

Many thanks but can't understand why your copy works and mine doesn't? Unfortunately not quite correct in that I would like when the fist entry in the row is input that it appears as Game#1 not as the cell number in other words if you put the value in say I11 that it appears in F88 rather than F83.

EDITED One other thing if you do manage to solve this problem can you also where the cells return a 0.0 put in a blank "", many thanks

RE-EDIT Sorry got that the wrong way round that the first value should appear in F83 not F88, sorry can you help?

**alan_stephen75@** · 07-24-2012, 01:53 PM

Thanks for helping and I won't mark it as solved as it still is not quite what I was after and I think it can't be done but happy to give a star for trying. Thanks again.