= last value entered

[Prune3001]

I have a range of cells c5:t5 and c1919, information will be entered daily into the cells consecutively. I need a cell to equal the last value entered.

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C5:T5,C19:O19")) Is Nothing Then Range("Z1").Value = Target.Value
End Sub

then press ALT + Q to close the code window. Result in Z1.

 

[GK039]

Hello

Thanx to the help of Aladin that gave me the answer to a similar question i had the following formula will give you what you want:

=INDEX(A:A,MAX(MATCH("zzzzzzzzzzzzz",$A:$A),MATCH(9.9999E+307,A:A)))

Assuming your range is column A. Change this accordingly

George

I have a range of cells c5:t5 and c1919, information will be entered daily into the cells consecutively. I need a cell to equal the last value entered.

 

[Aladin Akyurek]

Hello

Thanx to the help of Aladin that gave me the answer to a similar question i had the following formula will give you what you want:

=INDEX(A:A,MAX(MATCH("zzzzzzzzzzzzz",$A:$A),MATCH(9.9999E+307,A:A)))

Assuming your range is column A. Change this accordingly

George

George,

The following would be less risky...

E1:

=MATCH(9.99999999999999E+307,A:A)

E2:

=MATCH(REPT("z",255),A:A)

E3:

=IF(COUNT(E1:E2)=2,MAX(E1:E2),SUMIF(E1:E2,">0"))

which is the result cell.

 

[GK039]

Hi Aladin,

Thanks for the additional input

So i look first for the relative position of a number in the array, then for the text, separately, and then apply the IF. Correct?

Could you please explain me the risk in the first approach?

Thanks a lot once more

George

George,

The following would be less risky...

E1:

=MATCH(9.99999999999999E+307,A:A)

E2:

=MATCH(REPT("z",255),A:A)

E3:

=IF(COUNT(E1:E2)=2,MAX(E1:E2),SUMIF(E1:E2,">0"))

which is the result cell.

 

[Scott Huish]

This could be simplified if all the data was one type, say either or numbers or text. I'm going to assume numbers.

None of the solutions so far, however, have solved the problem that you have two different ranges to work with.

=LOOKUP(9.99999999999999E+307,IF(COUNT(C19:O19),C19:O19,C5:T5))

 

[Aladin Akyurek]

Hi Aladin,

Thanks for the additional input

So i look first for the relative position of a number in the array, then for the text, separately, and then apply the IF. Correct?

Could you please explain me the risk in the first approach?

Thanks a lot once more

George

Hi George,

I was responding to your MAX bit, not to the OP, which seems to be a bit unclear (see Hotpepper's reply)...

When both text and numeric entries are allowed in a reference and you want the last value, it can sometimes be the case that the reference does not contain either text or numbers. That is, when just text, the MAX bit will return #N/A. It will also do so when the reference fails to include any text.

The suggestion I made will work under both conditions... That is:

When...

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64 align=right>2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>3</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>3</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>7</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>0.6</TD></TR></TBODY></TABLE>

When...

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>FAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>LAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>JAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>KAD</TD></TR></TBODY></TABLE>

And when...

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>FAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>8</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>2.4</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>JAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>0.9</TD></TR></TBODY></TABLE>

Hope this helps.

 

[GK039]

Yes, that made it clear.

Thanks.

Hi George,

I was responding to your MAX bit, not to the OP, which seems to be a bit unclear (see Hotpepper's reply)...

When both text and numeric entries are allowed in a reference and you want the last value, it can sometimes be the case that the reference does not contain either text or numbers. That is, when just text, the MAX bit will return #N/A. It will also do so when the reference fails to include any text.

The suggestion I made will work under both conditions... That is:

When...

<table style="width: 48pt; border-collapse: collapse;" width="64" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width: 48pt;" width="64"></colgroup><tbody><tr style="height: 14.4pt;" height="19"><td style="border: 0.5pt solid windowtext; background-color: transparent; width: 48pt; height: 14.4pt;" class="xl63" width="64" align="right" height="19">2</td></tr><tr style="height: 14.4pt;" height="19"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 14.4pt;" class="xl63" align="right" height="19">3</td></tr><tr style="height: 14.4pt;" height="19"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 14.4pt;" class="xl63" align="right" height="19">3</td></tr><tr style="height: 14.4pt;" height="19"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 14.4pt;" class="xl63" align="right" height="19">7</td></tr><tr style="height: 14.4pt;" height="19"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 14.4pt;" class="xl63" align="right" height="19">0.6</td></tr></tbody></table>

When...

<table style="width: 48pt; border-collapse: collapse;" width="64" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width: 48pt;" width="64"></colgroup><tbody><tr style="height: 14.4pt;" height="19"><td style="border: 0.5pt solid windowtext; background-color: transparent; width: 48pt; height: 14.4pt;" class="xl65" width="64" height="19">FAD</td></tr><tr style="height: 14.4pt;" height="19"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 14.4pt;" class="xl65" height="19">LAD</td></tr><tr style="height: 14.4pt;" height="19"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 14.4pt;" class="xl65" height="19">JAD</td></tr><tr style="height: 14.4pt;" height="19"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 14.4pt;" class="xl65" height="19">
</td></tr><tr style="height: 14.4pt;" height="19"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 14.4pt;" class="xl65" height="19">KAD</td></tr></tbody></table>

And when...

<table style="width: 48pt; border-collapse: collapse;" width="64" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width: 48pt;" width="64"></colgroup><tbody><tr style="height: 14.4pt;" height="19"><td style="border: 0.5pt solid windowtext; background-color: transparent; width: 48pt; height: 14.4pt;" class="xl65" width="64" height="19">FAD</td></tr><tr style="height: 14.4pt;" height="19"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 14.4pt;" class="xl65" align="right" height="19">8</td></tr><tr style="height: 14.4pt;" height="19"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 14.4pt;" class="xl65" align="right" height="19">2.4</td></tr><tr style="height: 14.4pt;" height="19"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 14.4pt;" class="xl65" height="19">JAD</td></tr><tr style="height: 14.4pt;" height="19"><td style="border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: windowtext; background-color: transparent; height: 14.4pt;" class="xl65" align="right" height="19">0.9</td></tr></tbody></table>

Hope this helps.