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.

On Tue, 28 Oct 2008 05:25:00 -0700, DaveKid
wrote:

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

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 mo

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" wrote:

On Tue, 28 Oct 2008 05:25:00 -0700, DaveKid
wrote:

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

On Wed, 29 Oct 2008 04:51:01 -0700, DaveKid
wrote:

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

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" wrote:

On Wed, 29 Oct 2008 04:51:01 -0700, DaveKid
wrote:

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

On Wed, 29 Oct 2008 09:24:01 -0700, DaveKid
wrote:

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