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

GETanner

New Member
Joined
Mar 15, 2002
Messages
23
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
On 2002-03-16 07:57, Aladin Akyurek wrote:
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

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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top