Month Text as a value

[DaveKid]

Basically..... I have 10,000 columns of data each defined by the month they
are in with the month name November, December etc. I have a list of the
months in a drop down menu which when selected will give the amount of data
for that month. I am trying to give a year to date value which will calculate
the month and the months prior to the month selected but I can not do this.

 

[Ron Rosenfeld]

Basically..... I have 10,000 columns of data each defined by the month they
are in with the month name November, December etc. I have a list of the
months in a drop down menu which when selected will give the amount of data
for that month. I am trying to give a year to date value which will calculate
the month and the months prior to the month selected but I can not do this.

That is a different question from what you initially posted.

But perhaps you could be a bit more specific as to how things are laid out.

Since you write 10,000 columns, I will assume you have Excel 2007, since
earlier versions only had 256 columns.

Also, how do you define the year in your columns? After all, 10,000 columns,
if each is a separate month, would encompass about 833 years.

Where is your list of months for the drop-down menu? What is the range
location? How are those months named? (i.e. is it a month number, month
abbreviation like J or Jan; the full name like January; a formatted date?)

--ron

 

[DaveKid]

Sorry my mistake I meant rows. I do not define years only months and weeks.
They are months within one year so it could be 5,000 November's and 3,0000
January's etc. List of months is in a seperate sheet and the months are
written as text "Novemeber" etc.

I will explain a little more:

Each row contains data in seprate columns as follows: customer name, sales
persons name, product, value, date, week, month. I have different months out
of the year in each depending on when the sale was made. This data is linked
to another sheet which gives the sales figures for each sales person by week
or month or quarter. In this sheet I have a column called Year to date. This
represents the sales that have been made year to date depending on the week
or month you have selected. So if you choose April, this will calculate the
figures from Jan, Feb, Mar and April giving you a Year to Date postion for
that month. This works fine with week which contains a figure and am able to
write the a formula that recognises the figure and any figures prior to it
but I am not able ot do this with the month written as text.

 

[Ron Rosenfeld]

In this sheet I have a column called Year to date. This
represents the sales that have been made year to date depending on the week
or month you have selected. So if you choose April, this will calculate the
figures from Jan, Feb, Mar and April giving you a Year to Date postion for
that month. This works fine with week which contains a figure and am able to
write the a formula that recognises the figure and any figures prior to it
but I am not able ot do this with the month written as text.

OK, we're getting somewhere.

On the first sheet, is "date" entered as a real date?
How are week and month entered? Or are they calculated?

But on the second sheet, is there more than the one column?
what is in the individual rows?
Is there one "second sheet" per salesperson?
How do you choose the week or month?

What do you mean by "month written as text".
--ron

 

[DaveKid]

No. The column "Year to Date" contains numeric values.

In the drop down list Week is "Week 01", "Week 02" etc
Month is "November", "December" etc. In the columns these are represented by
numeric values.

The second sheet contains sales data. Each row represents details of an
individual sale.

No. All the salespeople are on the second sheet. This is represented on the
first sheet by all the sales persons names with the figures against them.

The week or month is chosen in the first sheet and is selected through a
drop down menu and a sub drop down menu. The first drop down menu lets u make
a choice between month or week and the second sub menu lets you choose either
the month name i.e. November or the week number i.e. Week 52.

Month written as text means that I have the month displayed in the sub menu
as November, december etc. I cannot link this sub menu to the Year to Date
formula as it needs to read a value to calculate this. So what I need to know
is a way of giving the month text a value.

 

[Ron Rosenfeld]

No. The column "Year to Date" contains numeric values.

How do the numeric values get there? (I would have thought that column
contained a formula).

In the drop down list Week is "Week 01", "Week 02" etc
Month is "November", "December" etc. In the columns these are represented by
numeric values.

The second sheet contains sales data. Each row represents details of an
individual sale.

No. All the salespeople are on the second sheet. This is represented on the
first sheet by all the sales persons names with the figures against them.

The week or month is chosen in the first sheet and is selected through a
drop down menu and a sub drop down menu. The first drop down menu lets u make
a choice between month or week and the second sub menu lets you choose either
the month name i.e. November or the week number i.e. Week 52.

Month written as text means that I have the month displayed in the sub menu
as November, december etc. I cannot link this sub menu to the Year to Date
formula as it needs to read a value to calculate this. So what I need to know
is a way of giving the month text a value.

Since you did not write otherwise, I assumed the value you wanted to give the
month text was a number between 1 and 12 corresponding to the month's place in
the year.

When I gave you a method in your previous thread:

=MATCH(A1,List_of_Months,0)

You were not clear as to the nature of the problem.

If you check HELP for the MATCH worksheet function, you will note that A1 is
the lookup value. You should substitute the cell reference that contains the
name of the month that you selected from the dropdown list.

List_of_Months, as I wrote, is the range where you have listed the individual
months to be used to generate your month drop-down list.

You should be able to figure out the reason for the "0" once you read HELP for
the MATCH function.

That function will return the month number, so you would use it wherever you
need the number of the month.
--ron

 

[DaveKid]

Yes it does have a formaula in there which returns a mumeric value. Please if
you cannot help then please tell me as you seem to be asking questions which
have nothing to do with my problem!

 

[Ron Rosenfeld]

Yes it does have a formaula in there which returns a mumeric value. Please if
you cannot help then please tell me as you seem to be asking questions which
have nothing to do with my problem!

Well, I have twice, in two different threads, given you a formula which, given
the date in text, will return a numeric value equal to the position of the
month within the year (i.e. Jan=1, Feb=2 ... Dec=12).

I have also explained, in this thread, some ways in which you could apply that
formula.

Apparently that is not what you want.

My questions were designed to try to figure out what you want, but clearly have
been unsuccessful.

Perhaps someone else will better understand what you mean when you request
"Month text as value" but reject solutions which return the month number given
the month text.

Sorry I could not help you.

Good luck.
--ron