Formula for updating a single cell from data entered into 12 other cells through the day

[Craigretired]

I am working with 2 sheets within a workbook. Hourly a value is entered into a new cell on sheet number 1. There are 12 entrees made daily. I want a single value in a single cell on sheet 2, to be the lasted hourly value entered on sheet 1. In other words, the first hour value on sheet one would be posted to the single cell in sheet 2. As the next hours value is posted to sheet 1, the value in the cell in sheet 2, would be updated to the new value entered on sheet 1. I have tried different IF formulas without success. For example: IF(D12>0,D12,0,IF(D13>0,D13,D12,IF(D14>0,D14,D13,IF(D15>0,D15,0)))). What I think this says is: if cell D12 has a value greater than 0, enter value of D12 in the cell, if not, then enter 0; if cell D13 has a value greater than 0, enter value of D13 in the cell, otherwise enter value of D12 in the cell, etc. There is a range of 23 cells that data is input to, but I only need the last data entree on an hourly basis to be update to sheet 2 single cell. Is this possible??
Summary: Sheet 1: Cells D12 through D23 start with a value of 0. After the 1st hour, a value is entered into cell D12. After the 2nd hour, a value is entered into cell D13. After the 3rd hour a value is entered into cell D14, and so on, until after the 12 hours, there is a data entree in each of the cells D12 through D23. Sheet 2: Cell C9 needs to be updated each hour with the new current entree to Sheet 1 cells D12 through D23. I have reviewed LOOKUP formulas, IF formulas, OR formulas, AND formulas, etc., and can not find anything that will accomplish what I need. Thanks for any help.

[FDibbins]

Hi and welcome to the forum

Try this....

	A	B
1	1	0
2	2
3	3
4	4
5	5
6	3
7	1
8	0

B1=VLOOKUP(99^99,$A$1:$A$19,1,1)

I did not use your ranges, so adjust them as needed

[Craigretired]

fDibbins, Thank you, that works great. I understand most of the formula, but what is the 1,1 at the end? I initially thought it meant it would look to the 1st column to the left that had information in it. But that was not the case. Also if I had 0 in any of the locked fields, it returned 0, but if they were blank, it did not and returned the last value entered. I also played with the 1,1 by putting a 2 as the last, so that it was 1,2. It still returned the last value entered in the locked fields. Just wondering. Thanks again for the help!! Craig

[FDibbins]

What that does, is search for a really really big number (99^99) - which it wont find. the 1,1 means look in the 1st column of the range (it only has 1 column anyway), and return the exact or next closest match.

So, effectively, it goes all the way down to the last cell in the range, l;ooking for that big number. When doesnt find it, it works back up the range until it finds the very 1st value that is the next closest - it then stop looking.

I tested with zero's and blanks inside that range, and it still gives me the very last entry. Below I have included 3 versions of the same thing, just changed the values inside the range

	A	B	C	D	E	F	G	H
1	1	5		1	10			5
2	2			2
3	3			3
4	4			4
5				0
6
7	1			50
8	5			10			5

[Craigretired]

FDibbins, Thank you again! Much appreciated! You explanation and exhibit was very helpful in my understanding of what the formula is doing. It is definitely easier than what I was attempting to do. Craig

[FDibbins]

Happy to help and glad it worked for you. VLOOKUP can be a very useful tool to avoid lengthy/involved nested IF statements.

It seems this answered your question. If so, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)