I have several sheets that I need to collect data from. Coloumb A has dates
Sunday thru Saturday, formated as: Tuesday, August 25, 2009 There
are twelve sheets like this....one for each month. On the "Report" sheet, I
need to collect certain 'days of the week' which are Tuesday and Thursdays
only (this may change later) and use the values associated with those days.
The cells that each day is in will change from month to month and year to
year. Is it possible to tell Excell to 'locate' a certain day in a
particular coloumb and bring the associated values in coloumbs B thru H?

I don't always find my way to this area, would you mind if you contact me
at: ?

Hi
You can add a column next to date column and put the formula below, copy it
down , This will give you the weekday ( SUNDAY =1)
you can use sum if to get totals for the desired days

=IF(WEEKDAY(D5,1)=1,"Sunday",IF(WEEKDAY(D5,1)=2,"M onday",IF(WEEKDAY(D5,1)=3,"Tuesday",IF(WEEKDAY(D5, 1)=4,"Wednesday",IF(WEEKDAY(D5,1)=5,"Thursday",IF( WEEKDAY(D5,1)=6,"Friday",IF(WEEKDAY(D5,1)=7,"Satur day","")))))))

Best regards

Rajesh Mehmi




"Gary" wrote in message
...
I have several sheets that I need to collect data from. Coloumb A has
dates
Sunday thru Saturday, formated as: Tuesday, August 25, 2009
There
are twelve sheets like this....one for each month. On the "Report" sheet,
I
need to collect certain 'days of the week' which are Tuesday and
Thursdays
only (this may change later) and use the values associated with those
days.
The cells that each day is in will change from month to month and year to
year. Is it possible to tell Excell to 'locate' a certain day in a
particular coloumb and bring the associated values in coloumbs B thru H?

I don't always find my way to this area, would you mind if you contact me
at: ?

Your entire formula can be replaced by this much shorter one...

=TEXT(D5,"dddd")

--
Rick (MVP - Excel)


"Rajesh Mehmi" wrote in message
...
Hi
You can add a column next to date column and put the formula below, copy
it down , This will give you the weekday ( SUNDAY =1)
you can use sum if to get totals for the desired days

=IF(WEEKDAY(D5,1)=1,"Sunday",IF(WEEKDAY(D5,1)=2,"M onday",IF(WEEKDAY(D5,1)=3,"Tuesday",IF(WEEKDAY(D5, 1)=4,"Wednesday",IF(WEEKDAY(D5,1)=5,"Thursday",IF( WEEKDAY(D5,1)=6,"Friday",IF(WEEKDAY(D5,1)=7,"Satur day","")))))))

Best regards

Rajesh Mehmi




"Gary" wrote in message
...
I have several sheets that I need to collect data from. Coloumb A has
dates
Sunday thru Saturday, formated as: Tuesday, August 25, 2009 There
are twelve sheets like this....one for each month. On the "Report"
sheet, I
need to collect certain 'days of the week' which are Tuesday and
Thursdays
only (this may change later) and use the values associated with those
days.
The cells that each day is in will change from month to month and year to
year. Is it possible to tell Excell to 'locate' a certain day in a
particular coloumb and bring the associated values in coloumbs B thru H?

I don't always find my way to this area, would you mind if you contact me
at: ?

I'm not completely sure what you are trying to do from your description. Can
you explain what you mean by "I need to collect certain days" and "bring the
associated value in columns B thru H"? Collect what, the values in B thru H?
Bring them where (I know the report sheet, but where on the report sheet)?
Also, how are the sheets you are collecting the information from named? Is a
VB coded solution acceptable?

--
Rick (MVP - Excel)


"Gary" wrote in message
...
I have several sheets that I need to collect data from. Coloumb A has
dates
Sunday thru Saturday, formated as: Tuesday, August 25, 2009
There
are twelve sheets like this....one for each month. On the "Report" sheet,
I
need to collect certain 'days of the week' which are Tuesday and
Thursdays
only (this may change later) and use the values associated with those
days.
The cells that each day is in will change from month to month and year to
year. Is it possible to tell Excell to 'locate' a certain day in a
particular coloumb and bring the associated values in coloumbs B thru H?

I don't always find my way to this area, would you mind if you contact me
at: ?

Hello Gary,

You seem to have collected your data separated by weekday already.
Aren't your 12 sheets showing Sunday data in column B, ... and
Saturday data in column H?

If that's the case you can easily apply a 3D sum:
All Tuesdays: =SUM(Sheet1:Sheet12!D1:D999)
All Thursdays: =SUM(Sheet1:Sheet12!F1:F999)

(You did not mention your sheet names. Make sure that Sheet1 is your
first, Sheet12 your last and that all the others BUT NO MORE sheets
are in between.)

If I did not guess your data structure correctly let us exactly know
how your data is organised, please.

OT: Don't ask for emails please. Others would like to participate from
all responses.

Regards,
Bernd

Hi Rick

Could the result of =TEXT(D5,"dddd")
then be used in a "sumif" function?


best regards
Rajesh Mehmi



"Rick Rothstein" wrote in message
...
Your entire formula can be replaced by this much shorter one...

=TEXT(D5,"dddd")

--
Rick (MVP - Excel)


"Rajesh Mehmi" wrote in message
...
Hi
You can add a column next to date column and put the formula below, copy
it down , This will give you the weekday ( SUNDAY =1)
you can use sum if to get totals for the desired days

=IF(WEEKDAY(D5,1)=1,"Sunday",IF(WEEKDAY(D5,1)=2,"M onday",IF(WEEKDAY(D5,1)=3,"Tuesday",IF(WEEKDAY(D5, 1)=4,"Wednesday",IF(WEEKDAY(D5,1)=5,"Thursday",IF( WEEKDAY(D5,1)=6,"Friday",IF(WEEKDAY(D5,1)=7,"Satur day","")))))))

Best regards

Rajesh Mehmi




"Gary" wrote in message
...
I have several sheets that I need to collect data from. Coloumb A has
dates
Sunday thru Saturday, formated as: Tuesday, August 25, 2009 There
are twelve sheets like this....one for each month. On the "Report"
sheet, I
need to collect certain 'days of the week' which are Tuesday and
Thursdays
only (this may change later) and use the values associated with those
days.
The cells that each day is in will change from month to month and year
to
year. Is it possible to tell Excell to 'locate' a certain day in a
particular coloumb and bring the associated values in coloumbs B thru H?

I don't always find my way to this area, would you mind if you contact
me
at: ?

I don't see why not (you could always try it and see). Let's put it this
way... if the result of the formula you posted can, then so can mine.... the
two formulas produce identical results.

--
Rick (MVP - Excel)


"Rajesh Mehmi" wrote in message
...
Hi Rick

Could the result of =TEXT(D5,"dddd")
then be used in a "sumif" function?


best regards
Rajesh Mehmi



"Rick Rothstein" wrote in message
...
Your entire formula can be replaced by this much shorter one...

=TEXT(D5,"dddd")

--
Rick (MVP - Excel)


"Rajesh Mehmi" wrote in message
...
Hi
You can add a column next to date column and put the formula below, copy
it down , This will give you the weekday ( SUNDAY =1)
you can use sum if to get totals for the desired days

=IF(WEEKDAY(D5,1)=1,"Sunday",IF(WEEKDAY(D5,1)=2,"M onday",IF(WEEKDAY(D5,1)=3,"Tuesday",IF(WEEKDAY(D5, 1)=4,"Wednesday",IF(WEEKDAY(D5,1)=5,"Thursday",IF( WEEKDAY(D5,1)=6,"Friday",IF(WEEKDAY(D5,1)=7,"Satur day","")))))))

Best regards

Rajesh Mehmi




"Gary" wrote in message
...
I have several sheets that I need to collect data from. Coloumb A has
dates
Sunday thru Saturday, formated as: Tuesday, August 25, 2009 There
are twelve sheets like this....one for each month. On the "Report"
sheet, I
need to collect certain 'days of the week' which are Tuesday and
Thursdays
only (this may change later) and use the values associated with those
days.
The cells that each day is in will change from month to month and year
to
year. Is it possible to tell Excell to 'locate' a certain day in a
particular coloumb and bring the associated values in coloumbs B thru
H?

I don't always find my way to this area, would you mind if you contact
me
at: ?

Hi Rick

I have tried the formula and it does work.
Thanks

"Rick Rothstein" wrote in message
...
I don't see why not (you could always try it and see). Let's put it this
way... if the result of the formula you posted can, then so can mine....
the two formulas produce identical results.

--
Rick (MVP - Excel)


"Rajesh Mehmi" wrote in message
...
Hi Rick

Could the result of =TEXT(D5,"dddd")
then be used in a "sumif" function?


best regards
Rajesh Mehmi



"Rick Rothstein" wrote in message
...
Your entire formula can be replaced by this much shorter one...

=TEXT(D5,"dddd")

--
Rick (MVP - Excel)


"Rajesh Mehmi" wrote in message
...
Hi
You can add a column next to date column and put the formula below,
copy it down , This will give you the weekday ( SUNDAY =1)
you can use sum if to get totals for the desired days

=IF(WEEKDAY(D5,1)=1,"Sunday",IF(WEEKDAY(D5,1)=2,"M onday",IF(WEEKDAY(D5,1)=3,"Tuesday",IF(WEEKDAY(D5, 1)=4,"Wednesday",IF(WEEKDAY(D5,1)=5,"Thursday",IF( WEEKDAY(D5,1)=6,"Friday",IF(WEEKDAY(D5,1)=7,"Satur day","")))))))

Best regards

Rajesh Mehmi




"Gary" wrote in message
...
I have several sheets that I need to collect data from. Coloumb A has
dates
Sunday thru Saturday, formated as: Tuesday, August 25, 2009 There
are twelve sheets like this....one for each month. On the "Report"
sheet, I
need to collect certain 'days of the week' which are Tuesday and
Thursdays
only (this may change later) and use the values associated with those
days.
The cells that each day is in will change from month to month and year
to
year. Is it possible to tell Excell to 'locate' a certain day in a
particular coloumb and bring the associated values in coloumbs B thru
H?

I don't always find my way to this area, would you mind if you contact
me
at: ?