Trying to get a data value when a certain name is selected.

[Macoil]

Hello: I am wanting to call up the last number saved to a certain cell when I chose a name from a drop down list.

In my workbook I have 5 tabs one is called input reading and at cell b2 I have a drop down list, when I select a name from that list I need the last saved number from cell c2 which resides in column c of the data tab. I have spent 2 days reading post and have tried to adapt code I thought would work but no joy.

I would be very thankful for help on this.

Attached please find my work book Tank Gauges.

Thanks
Mark

[stnkynts]

It is not clear to me which value you want to go where. Does the current c2 need to pasted after the last value in column C (data sheet). Does the last value in column C (data sheet) need to be pasted to c2 (input sheet). Do the peoples name have any impact?

[Leith Ross]

Hello Macoil,

The macro below has been added to worksheet "Input Reading" Worksheet_Change event in the attached workbook. This will fill in the "Old Gauge" cell based on the name selected in the drop down.

Please Login or Register  to view this content.

[Macoil]

In my workbook I have 5 tabs one is called input reading and at cell b2 I have a drop down list, when I select a name from that list I need the last saved number from cell c2 which resides in column c of the data tab. I have spent 2 days reading post and have tried to adapt code I thought would work but no joy.

Thank you for the fast reply and I am sorry for not explaining my need better and for messing up what cell I need where as the above paragraph is incorrect. Let me try again.

What I need is when I select a name from the drop down list in the input reading tab cell b2 then cell c2 will display from the data page the contents of column E as related to the last entry of that name, So as the work book stands now if I selected the name Schriener it would display in cell c2 of the input Reading tab the number 7.

When completed this workbook will be used to track over 100 names, Each name is a tank holding fluid.

thanks
Mark

[Macoil]

Ok After reading 100 pages and trying to make this do what I want here I am again asking for your help.

What I need is When I select a name from the Sheet Input Reading cell B2 it needs to enter into cell c2 the last saved Record from the Sheet Data With the same name from column E.

Fro example when I select Carpenter 1 it should display 20 in cell c2 of the input reading sheet. Right now it is displaying Column c and the oldest record not the newest.

Any help would appreciated.

[Leith Ross]

Hello Macoil,

If I understand this right, column "E" of the "Data" sheet is to be displayed in "C2" of "Input Reading". This strikes me as odd because "C2" is labeled "Old Gauge" and the column header for "E" is "After Drain".
"Carpenter 1" has two entries both on 3/20/2014. How do I tell which is the newest entry?

[Macoil]

Thanks Leith for responding, Very true the dates when the workbook is put to use would indeed be different dates, when put to use all the names in the drop down list would have values recorded on the same dates.

What is done is the following: Say that on 4/1/2014 the Carpenter 1 is gauged I start with the gauge from the prior week say it is 15" then I gauge and it might have increased in the last 7 days to 25" I then drain off any water content leaving only oil then I gauge again and it might be 20" . That 20" number becomes my old gauge for the next week when the tank is gauged again.
I want to track all of the tanks weekly and record the results in the data page, where I can use pivot sheets to call up different ranges of dates or tank names to compare results.

[Macoil]

OK using Leith's code I made some tweaks and this workbook does everything I want except for one so I need a little more help please.

When I select from my drop down list in sheet input reading a name the code returns the first saved incident to cell c2 , what I need is the last saved incident for that name to be displayed.
Here is my workbook and I do hope I explained what I need ok.

[Macoil]

Bump: Anyone ? I Just can not figure this out

[Macoil]

OK instead of continuing this route Should I try a different approach ?
Is What I am trying to do not possible ?

Mark

[Leith Ross]

Hello Mark,

The attached workbook has a new macro. This will pull the last entry matching the well name and date in the list and use this as the old gauge value. I hope this what you need.

Here is the macro...

Please Login or Register  to view this content.

Worksheet Change Event Code

Please Login or Register  to view this content.

[Macoil]

Leith: That works and I getting the last saved record, But not the record I want.

I need it to get the After Drain not the Old Gauge, So it would go get Column 5 from the data sheet and display that in column 3 of the input Reading sheet.

Thank you
PS I am trying to fiddle with the code but I am getting errors.

Also I just found a 2nd issue, unless I use the last know date each time it returns a 0 , I need it to show me the value on the last date it was gauged even though I am using todays date