Using the "IF" function?

[Ruff]

I setup a worksheet like below

A B
1 Jan 123
2 Feb 543
3 Mar 654
4 Apr 456
5 May 645
6 Jun 543
7 Jul 0
8 Aug 0
9 Sep 0
10 Oct 0
11 Nov 0
12 Dec 0
13

When the end of the month, the user will enter some value in the B column,
And now I would to setup a formula at A13, if the user enter the value in
the B7, then the A13 will show Jul, when the user carry on to enter the
value in the B8, then the A13 will change to show Aug, is it possible to
setup a if formula or other formula rather than use the macro to doing this?
Many Thanks.

Ruff

 

[Guest]

I am not sure I understand what you asking here. Would you mind expounding

 

[vezerid]

Ruff,
I understand that the months will be filled in chronological order.
Hence you want the last month entered.
The formula I am suggesting for A13 is an array formula, i.e. you need
to enter it with Shift+Ctrl+Enter
=INDEX(B1:B12,MAX(ROW(1:12)*--(B1:B12<>0)))

HTH
Kostis V

 

[Ruff]

Gee-off,

Thanks for your reply, actually I setup this worksheet and want to set a
formula at the A13, I will enter the value at each month end, the example
show below:

Example:
Now, I enter the value at the B7, A13 detect the B7 is not equal to 0, then
the A13 will show cell A7 (Jul)
This time I enter the value at the B8, A13 detect the B8 is not equal to 0,
then the A13 will show cell A8 (Aug)
..
..
..
Until last month I enter the value at the B12, the cell A13 will show the
A12 (Dec)

Thank you so much.

Ruff

 

[Ruff]

hi vezerid,

Thanks for your reply, maybe not detail enough I wrote the mail before.
Actually I want the cell A13 automatically detect the B1:B12, each month I
the value in the B column, A13 will detect if the cell not equal to 0, then
it show the A column text. That mean last step B7 get the value it show A7
value in A13, if I carry on to enter the value in B8, then A13 show the A8
value.

Thank you so much.
Ruff

 

[Bob Phillips]

Ruff,

Try

=INDEX(A1:A12,MAX(IF(B1:B12<>0,ROW(B1:B12))))

which is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Ruff,

Just amend Vezerid's formula to

=INDEX(A1:A12,MAX(ROW(1:12)*--(B1:B12<>0)))

or try mine

--

HTH

RP
(remove nothere from the email address if mailing direct)