Auto updating a cell..

[Excel_Gaz]

Hi,
I’ve tried to figure this problem out, but I’m at a dead end! I’ve used excel for a long time, but only basic functions. I think I might need a macro to solve the problem, but just incase there’s a function I don’t know about I thought I try posting the problem here first.

I’ve created a simple sheet to try and show the problem I’m facing. Sheet one has 5 columns Date, Time (manually added daily), Amount (manually added daily), Price (manually added daily), Diff (calculated). Columns B, C & D are inputted around the same time daily, give or take 15 minutes.

Sheet two has 4 columns Date (auto shows current date =today() ), Amount (from sheet 1, using =MAX(‘Sheet 1’!C3:C9) ) as the amount only increases in that column), Total (sum of column B x C) and Price. The yellow cell is the cell that I need to display the data from sheet 1 column D, updated daily automatically.

For example at 22:00 on the 16/07/23 sheet 2 Price yellow box will auto show the data from sheet 1 cell D6, then the following day at 22:00 it will change to display sheet 1 D7 and so on.

I’m assuming there’s a simple way of doing this that I’m not aware of!

Any help appreciated,

Gaz

 

[Peter_SSs]

Welcome to the MrExcel board!

To get the last price from column D of Sheet1, try this in your yellow cell

=LOOKUP(9^9,Sheet1!D:D)

 

[Excel_Gaz]

Welcome to the MrExcel board!

To get the last price from column D of Sheet1, try this in your yellow cell

=LOOKUP(9^9,Sheet1!D:D)

Thank you so much Peter!! This worked for me.

Just so I understand, what’s the first part 9^9 mean?

Gaz

 

[Peter_SSs]

what’s the first part 9^9 mean?

It is just a very big number (9 to the power of 9). When lookup cannot find that very big number in column D, it returns the last number that it can find in the column.

Thank you so much Peter!! This worked for me.

You're welcome. Glad it worked for you.