Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using the "IF" function?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using the "IF" function?
I am not sure I understand what you asking here. Would you mind expounding
on it a bit. "Ruff" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using the "IF" function?
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using the "IF" function?
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 "Gee-off" ¼¶¼g©ó¶l¥ó·s»D ... I am not sure I understand what you asking here. Would you mind expounding on it a bit. "Ruff" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using the "IF" function?
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 "vezerid" ¼¶¼g©ó¶l¥ó·s»D .com... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using the "IF" function?
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) "Ruff" wrote in message ... 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 "Gee-off" ¼¶¼g©ó¶l¥ó·s»D ... I am not sure I understand what you asking here. Would you mind expounding on it a bit. "Ruff" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using the "IF" function?
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) "Ruff" wrote in message ... 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 "vezerid" ¼¶¼g©ó¶l¥ó·s»D .com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wild Characters in "IF" function | Excel Discussion (Misc queries) | |||
Date & Time | New Users to Excel | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |