I have a Spreadsheet which contains Weekly Dates from Cell E2 thru AJ2.

If I place an "X" in any of the Cells below these Cells I would like to copy
the date to Column D corrisponding to the Row which has the "X" in it.

i.e. If Cell G5 has an "X" in it I would like to copy the date in G3
(11/7/2008) to Cell D5 (7-Nov-2008)

Is there a standard formular or function to do this? If not could you lead
me down the right path?

You either have 0 X's or exactly 1 X' in each row of columns E:AJ?

If yes, then you could use a formula like:

=SUMIF(E3:AJ3,"x",$E$2:$AJ$2)
And format the results as a date (d-mmm-yyyy)

But you'll see 0 (0-Jan-1900) if there are no X's and you'll be adding the dates
if there are more than 1 X.

I'd use something like:

=IF(COUNTIF(E3:AJ3,"X")=0,"",
IF(COUNTIF(E3:AJ3,"x")1,"Too many X's",
SUMIF(E3:AJ3,"x",$E$2:$AJ$2)))

The cell will appear empty if there are no x's and give a warning if there is
more than a single X.



Dr. Darrell wrote:

I have a Spreadsheet which contains Weekly Dates from Cell E2 thru AJ2.

If I place an "X" in any of the Cells below these Cells I would like to copy
the date to Column D corrisponding to the Row which has the "X" in it.

i.e. If Cell G5 has an "X" in it I would like to copy the date in G3
(11/7/2008) to Cell D5 (7-Nov-2008)

Is there a standard formular or function to do this? If not could you lead
me down the right path?

--

Dave Peterson

Please clarify. Give us a better idea of what is in the worksheet (just
enough rows and columns for us to get the picture). Are the dates in E2:AJ2
or E2:AJ(N) where N 2?

In your example, could the X by in G10 and you want date from G8?

Or should the example read: i.e. If Cell G5 has an "X" in it I would like to
copy the date in G2 (NOT G3)? I will assume this to be true
Then in D5 use =SUMPRODUCT(--(E5:AJ5="X"),$E$2:$AJ$2) copy this to other D
cells as needed
It will work if there is only one X in a row
You will need to format D5 first as it will display a serial number like
39449
best wishes

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Dr. Darrell" wrote in message
...
I have a Spreadsheet which contains Weekly Dates from Cell E2 thru AJ2.

If I place an "X" in any of the Cells below these Cells I would like to
copy
the date to Column D corrisponding to the Row which has the "X" in it.

i.e. If Cell G5 has an "X" in it I would like to copy the date in G3
(11/7/2008) to Cell D5 (7-Nov-2008)

Is there a standard formular or function to do this? If not could you lead
me down the right path?

Thank you, I have inserted the first formula and it works very well.

"Dave Peterson" wrote:

You either have 0 X's or exactly 1 X' in each row of columns E:AJ?

If yes, then you could use a formula like:

=SUMIF(E3:AJ3,"x",$E$2:$AJ$2)
And format the results as a date (d-mmm-yyyy)

But you'll see 0 (0-Jan-1900) if there are no X's and you'll be adding the dates
if there are more than 1 X.

I'd use something like:

=IF(COUNTIF(E3:AJ3,"X")=0,"",
IF(COUNTIF(E3:AJ3,"x")1,"Too many X's",
SUMIF(E3:AJ3,"x",$E$2:$AJ$2)))

The cell will appear empty if there are no x's and give a warning if there is
more than a single X.



Dr. Darrell wrote:

I have a Spreadsheet which contains Weekly Dates from Cell E2 thru AJ2.

If I place an "X" in any of the Cells below these Cells I would like to copy
the date to Column D corrisponding to the Row which has the "X" in it.

i.e. If Cell G5 has an "X" in it I would like to copy the date in G3
(11/7/2008) to Cell D5 (7-Nov-2008)

Is there a standard formular or function to do this? If not could you lead
me down the right path?

--

Dave Peterson