Problem Using Excel datenumber as Text reference
 

Hi, struggling here with Excels datenumber...

I have as source the following table:

2006 2007
Jan 10 12
Feb 8 9
Mar 9 11
etc...


As a destination I have a report stating the following:

Jan-07 12
Jan-06 10

Whereby my report uses the Microsoftdatenumber as a reference
throughout, where Jan-07 is 39083 with the use of Format Cells Date
...
This value will change according to the month we live in :-)

Now the problem is: how can I use this datenumber to look up the
according value in the crosstable?
Normally I use INDEX() or VLOOKUP for this.
However, if I try to get "Jan" as a lookupvalue, e.g. through (LEFT();
3), this obviously does not return "Jan" but "390".
Is there I way to do this, pref. without adding an extra, intermediate
worksheet?

Important backgroundinfo:
My sourcefile is not made the way I would have it, but due to
"userfriendlyness" (i.e. the user-skill-level) I cannot change that.

Hope my question is at least understandable ^^

Thanx in advance for any help!

Problem Using Excel datenumber as Text reference
 

Try:

put in F2:

=INDEX($B$2:$C$4,MATCH(TEXT(MONTH(E2),"mmm"),$A$2: $A$4,0),MATCH(YEAR(E2),$B$1:$C$1,0)) and copy down

Where E2=Jan-07, E3=Jan-06 etc

If data and report are on different sheets, then qualify ranges with sheet
name e.g. Sheet!$B$2:$C$4 and chane E/F as required.

HTH

"artisdepartis" wrote:

Hi, struggling here with Excels datenumber...

I have as source the following table:

2006 2007
Jan 10 12
Feb 8 9
Mar 9 11
etc...


As a destination I have a report stating the following:

Jan-07 12
Jan-06 10

Whereby my report uses the Microsoftdatenumber as a reference
throughout, where Jan-07 is 39083 with the use of Format Cells Date
...
This value will change according to the month we live in :-)

Now the problem is: how can I use this datenumber to look up the
according value in the crosstable?
Normally I use INDEX() or VLOOKUP for this.
However, if I try to get "Jan" as a lookupvalue, e.g. through (LEFT();
3), this obviously does not return "Jan" but "390".
Is there I way to do this, pref. without adding an extra, intermediate
worksheet?

Important backgroundinfo:
My sourcefile is not made the way I would have it, but due to
"userfriendlyness" (i.e. the user-skill-level) I cannot change that.

Hope my question is at least understandable ^^

Thanx in advance for any help!

Problem Using Excel datenumber as Text reference
 

Toppers, works like a charm... Did not know this syntax combo of TEXT
and MONTH (mmm) at all... wish i had known before, would've saved me
some braincrack(er)s... ;-)

Grz, AdeP


On Feb 12, 1:08 pm, Toppers wrote:
Try:

put in F2:

=INDEX($B$2:$C$4,MATCH(TEXT(MONTH(E2),"mmm"),$A$2: $A$4,0),MATCH(YEAR(E2),$B*$1:$C$1,0)) and copy down

Where E2=Jan-07, E3=Jan-06 etc

If data and report are on different sheets, then qualify ranges with sheet
name e.g. Sheet!$B$2:$C$4 and chane E/F as required.

HTH



"artisdepartis" wrote:
Hi, struggling here with Excels datenumber...

I have as source the following table:

2006 2007
Jan 10 12
Feb 8 9
Mar 9 11
etc...

As a destination I have a report stating the following:

Jan-07 12
Jan-06 10

Whereby my report uses the Microsoftdatenumber as a reference
throughout, where Jan-07 is 39083 with the use of Format Cells Date
...
This value will change according to the month we live in :-)

Now the problem is: how can I use this datenumber to look up the
according value in the crosstable?
Normally I use INDEX() or VLOOKUP for this.
However, if I try to get "Jan" as a lookupvalue, e.g. through (LEFT();
3), this obviously does not return "Jan" but "390".
Is there I way to do this, pref. without adding an extra, intermediate
worksheet?

Important backgroundinfo:
My sourcefile is not made the way I would have it, but due to
"userfriendlyness" (i.e. the user-skill-level) I cannot change that.

Hope my question is at least understandable ^^

Thanx in advance for any help!- Hide quoted text -

- Show quoted text -