| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding values based on current month
Im using XL 2000
I have 48 columns, representing months Apr-06 thru Mar-07 (see January example below) Users enter a value under the Cashable1 column for the current month. In Column 49, r4, I need to show the value in the Cashable1 column, r4 for the current month. This is Januarys layout. It is repeated for each month across the worksheet. A B C D 1 April 2 Latest Annual Estimate Actual 3 Cashable1 Non Cashable1 Cashable2 Non Cashable2 4 £0.000 M £0.000 M £0.000 M £0.000 M r1 is ABCD merged and contains a month as text. (then the respective columns for each month) r2 is A+B merged & C+D merged (then the respective columns for each month) r3 are my column headings (then the respective columns for each month) r4 onwards are user data entry. Any advice on a solution appreciated. I'm assuming this is formula issue and not a VBA (formula preferred) Thanks, Paul |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding values based on current month
Enter this formula in cell AW4:
=HLOOKUP(TEXT(TODAY(),"mmmm"),A1:AV4,4,FALSE) Regards, Stefi Paulc ezt *rta: Im using XL 2000 I have 48 columns, representing months Apr-06 thru Mar-07 (see January example below) Users enter a value under the Cashable1 column for the current month. In Column 49, r4, I need to show the value in the Cashable1 column, r4 for the current month. This is Januarys layout. It is repeated for each month across the worksheet. A B C D 1 April 2 Latest Annual Estimate Actual 3 Cashable1 Non Cashable1 Cashable2 Non Cashable2 4 £0.000 M £0.000 M £0.000 M £0.000 M r1 is ABCD merged and contains a month as text. (then the respective columns for each month) r2 is A+B merged & C+D merged (then the respective columns for each month) r3 are my column headings (then the respective columns for each month) r4 onwards are user data entry. Any advice on a solution appreciated. I'm assuming this is formula issue and not a VBA (formula preferred) Thanks, Paul |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding values based on current month
Thanks for the advice Stefi.
I considered this but Lookups by default like the table data sorted alphanumerically, so lookups will always return August in data containing months like mine for example. Regards, Paul "Stefi" wrote: Enter this formula in cell AW4: =HLOOKUP(TEXT(TODAY(),"mmmm"),A1:AV4,4,FALSE) Regards, Stefi Paulc ezt *rta: Im using XL 2000 I have 48 columns, representing months Apr-06 thru Mar-07 (see January example below) Users enter a value under the Cashable1 column for the current month. In Column 49, r4, I need to show the value in the Cashable1 column, r4 for the current month. This is Januarys layout. It is repeated for each month across the worksheet. A B C D 1 April 2 Latest Annual Estimate Actual 3 Cashable1 Non Cashable1 Cashable2 Non Cashable2 4 £0.000 M £0.000 M £0.000 M £0.000 M r1 is ABCD merged and contains a month as text. (then the respective columns for each month) r2 is A+B merged & C+D merged (then the respective columns for each month) r3 are my column headings (then the respective columns for each month) r4 onwards are user data entry. Any advice on a solution appreciated. I'm assuming this is formula issue and not a VBA (formula preferred) Thanks, Paul |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding values based on current month
No! HLOOKUP will find any month name in an unsorted table with a FALSE as 4th
parameter! Try it! Regards, Stefi Paulc ezt *rta: Thanks for the advice Stefi. I considered this but Lookups by default like the table data sorted alphanumerically, so lookups will always return August in data containing months like mine for example. Regards, Paul "Stefi" wrote: Enter this formula in cell AW4: =HLOOKUP(TEXT(TODAY(),"mmmm"),A1:AV4,4,FALSE) Regards, Stefi Paulc ezt *rta: Im using XL 2000 I have 48 columns, representing months Apr-06 thru Mar-07 (see January example below) Users enter a value under the Cashable1 column for the current month. In Column 49, r4, I need to show the value in the Cashable1 column, r4 for the current month. This is Januarys layout. It is repeated for each month across the worksheet. A B C D 1 April 2 Latest Annual Estimate Actual 3 Cashable1 Non Cashable1 Cashable2 Non Cashable2 4 £0.000 M £0.000 M £0.000 M £0.000 M r1 is ABCD merged and contains a month as text. (then the respective columns for each month) r2 is A+B merged & C+D merged (then the respective columns for each month) r3 are my column headings (then the respective columns for each month) r4 onwards are user data entry. Any advice on a solution appreciated. I'm assuming this is formula issue and not a VBA (formula preferred) Thanks, Paul |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding values based on current month
Thanks stefi.
"Stefi" wrote: No! HLOOKUP will find any month name in an unsorted table with a FALSE as 4th parameter! Try it! Regards, Stefi Paulc ezt *rta: Thanks for the advice Stefi. I considered this but Lookups by default like the table data sorted alphanumerically, so lookups will always return August in data containing months like mine for example. Regards, Paul "Stefi" wrote: Enter this formula in cell AW4: =HLOOKUP(TEXT(TODAY(),"mmmm"),A1:AV4,4,FALSE) Regards, Stefi Paulc ezt *rta: Im using XL 2000 I have 48 columns, representing months Apr-06 thru Mar-07 (see January example below) Users enter a value under the Cashable1 column for the current month. In Column 49, r4, I need to show the value in the Cashable1 column, r4 for the current month. This is Januarys layout. It is repeated for each month across the worksheet. A B C D 1 April 2 Latest Annual Estimate Actual 3 Cashable1 Non Cashable1 Cashable2 Non Cashable2 4 £0.000 M £0.000 M £0.000 M £0.000 M r1 is ABCD merged and contains a month as text. (then the respective columns for each month) r2 is A+B merged & C+D merged (then the respective columns for each month) r3 are my column headings (then the respective columns for each month) r4 onwards are user data entry. Any advice on a solution appreciated. I'm assuming this is formula issue and not a VBA (formula preferred) Thanks, Paul |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding values based on current month
You are welcome! Thanks for the feedback!
Stefi Paulc ezt *rta: Thanks stefi. "Stefi" wrote: No! HLOOKUP will find any month name in an unsorted table with a FALSE as 4th parameter! Try it! Regards, Stefi Paulc ezt *rta: Thanks for the advice Stefi. I considered this but Lookups by default like the table data sorted alphanumerically, so lookups will always return August in data containing months like mine for example. Regards, Paul "Stefi" wrote: Enter this formula in cell AW4: =HLOOKUP(TEXT(TODAY(),"mmmm"),A1:AV4,4,FALSE) Regards, Stefi Paulc ezt *rta: Im using XL 2000 I have 48 columns, representing months Apr-06 thru Mar-07 (see January example below) Users enter a value under the Cashable1 column for the current month. In Column 49, r4, I need to show the value in the Cashable1 column, r4 for the current month. This is Januarys layout. It is repeated for each month across the worksheet. A B C D 1 April 2 Latest Annual Estimate Actual 3 Cashable1 Non Cashable1 Cashable2 Non Cashable2 4 £0.000 M £0.000 M £0.000 M £0.000 M r1 is ABCD merged and contains a month as text. (then the respective columns for each month) r2 is A+B merged & C+D merged (then the respective columns for each month) r3 are my column headings (then the respective columns for each month) r4 onwards are user data entry. Any advice on a solution appreciated. I'm assuming this is formula issue and not a VBA (formula preferred) Thanks, Paul |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return across Row Numeric Values Matching EXACT Month & Year for Criteria | Excel Worksheet Functions | |||
| Referencing data in different worksheet based on month name | Excel Worksheet Functions | |||
| Current Month Query | Excel Worksheet Functions | |||
| UserForm to select current month or earlier | Excel Discussion (Misc queries) | |||
| Selecting data from a list based on entered values | Excel Discussion (Misc queries) | |||
Linear Mode
