Taking the last value entered in a column and showing it els

[GETanner]

Hi there,

I trying to create a sheet that will take a set of variable data and extract various information from it.

I have worked out how to get the largest and smallest value from the data =LARGE(A1:A150,1) and =SMALL(A1:A150,1) aswell as the average.

Yet what I can't seem to do is work out is how to take the last value entered into the data and display it elsewhere.

For example, using columns.

Day Data
1 2
2 4
3 9
4 4
5 1

I would like to take the value for day 5 and use it elsewhere but when I enter day 6's data, I would like this then to replace day 5 and so on with each new day's data.

I looked through the function helps files in Excel XP but could not find any help there.

I enter the data onto one work sheet but display the calc's on another sheet and that is where I would like the last entered data value shown.

Is this possible?

Thanks in advance.

Guy

[Aladin Akyurek]

On 2002-03-16 07:38, GETanner wrote:
Hi there,

I trying to create a sheet that will take a set of variable data and extract various information from it.

I have worked out how to get the largest and smallest value from the data =LARGE(A1:A150,1) and =SMALL(A1:A150,1) aswell as the average.

Yet what I can't seem to do is work out is how to take the last value entered into the data and display it elsewhere.

For example, using columns.

Day Data
1 2
2 4
3 9
4 4
5 1

I would like to take the value for day 5 and use it elsewhere but when I enter day 6's data, I would like this then to replace day 5 and so on with each new day's data.

I looked through the function helps files in Excel XP but could not find any help there.

I enter the data onto one work sheet but display the calc's on another sheet and that is where I would like the last entered data value shown.

Is this possible?

Thanks in advance.

Guy

Guy,

Lets say that your data are in columns A and B from row 2 on and you want to display in C2 the last value entered in column B.

=OFFSET($B$2,MATCH(9.99999999999999E+307,B:B)-ROW(2:2),0,1,1)

will retrieve the desired value.

Aladin

[MrExcel]

Guy - this is Bill Jelen at MrExcel. Can you go into your profile and change your e-mail address? Somehow the . before the @ is causing everything to be sent to MrExcel.com

Thanks - and welcome to the board!

Bill

[Dave Hawley]

Hi Guy

You would be best to create a dymamic range of your data then use:

=INDEX(MyRange,ROWS(MyRange))

To see about dynamic ranges I have many examples here: http://www.ozgrid.com/Excel/DynamicRanges.htm

Or if you do not use a dynamic range you can use:
=INDEX(A1:A10000,MATCH(-9E+306,A1:A10000,-1))


Either way, I would avoid using entire columns in formulae as it is bad practice.

[GETanner]

On 2002-03-16 09:12, MrExcel wrote:
Guy - this is Bill Jelen at MrExcel. Can you go into your profile and change your e-mail address? Somehow the . before the @ is causing everything to be sent to MrExcel.com

Thanks - and welcome to the board!

Bill

Sorry about that, consider it changed. First time I've ever come across that problem.

Hope it did not cause too much trouble.

Guy

[ This Message was edited by: GETanner on 2002-03-16 18:39 ]

[GETanner]

On 2002-03-16 07:57, Aladin Akyurek wrote:

Guy,

Lets say that your data are in columns A and B from row 2 on and you want to display in C2 the last value entered in column B.

=OFFSET($B$2,MATCH(9.99999999999999E+307,B:B)-ROW(2:2),0,1,1)

will retrieve the desired value.

Aladin

Thank you very much indeed.

It worked a treat. It did exactly what I needed it to do.

I can now sit down with a cup of tea and try and work out how it worked.

Best regards,

Guy

[GETanner]

On 2002-03-16 13:33, Dave Hawley wrote:
Hi Guy

You would be best to create a dymamic range of your data then use:

=INDEX(MyRange,ROWS(MyRange))

To see about dynamic ranges I have many examples here: http://www.ozgrid.com/Excel/DynamicRanges.htm

Or if you do not use a dynamic range you can use:
=INDEX(A1:A10000,MATCH(-9E+306,A1:A10000,-1))


Either way, I would avoid using entire columns in formulae as it is bad practice.

Dave,

Thanks for the reply. I've not come across the use of dynamic ranges before, so your tip will take me a little more time to apply though I will sit down and have a good go at learning this new topic.

The answer before yours worked for my current needs but on quick inspection for that link you suggested, I do think my sheet will benefit from using Dynamic Ranges.

Time to creat another sheet.

Thanks again,

Guy