Hi Excel geniuses

I cant figure this one out. Can you help pls?

Imagine a time line left to right in monthly increments on a worksheet (A
say) and rows underneath.

I would like to set up a formula within the cells in worksheet A, that
dependent on whether that cell's date value lies within two date ranges held
on worksheet B (2 cells start/end date: StartDate1, EndDate1 : StartDate2,
End Date2), it returns either one of two cell references (2 cells holding
values) or a zero.

The idea being that changing the date ranges in worksheet B will update the
cells on Worksheet A..

Please help I said I thought it could be done and now Im not so sure..

Thanks guys! Youre fantantastic

Try something like this:

=IF(AND(cell_ref=B!StartDate1,cell_ref<=B!EndDate 1),B!
ref1,IF(AND(cell_ref=B!StartDate2,cell_ref<=B!End Date2),B!ref2,0))

where cell_ref is the date you want to test for, ref1 and ref2 are the
two cell references you mention. (It would be easier to quote the
actual cells you are using).

You might need to change = and <= to and < if you don't want to
include the start and end dates as part of the range.

Hope this helps.

Pete

On Jul 29, 12:31*pm, MarcusMac
wrote:
Hi Excel geniuses

I cant figure this one out. Can you help pls?

Imagine a time line left to right in monthly increments on a worksheet (A
say) and rows underneath.

I would like to set up a formula within the cells in worksheet A, that
dependent on whether that cell's date value lies within two date ranges held
on worksheet B (2 cells start/end date: StartDate1, EndDate1 : StartDate2,
End Date2), it returns either one of two cell references (2 cells holding
values) or a zero.

The idea being that changing the date ranges in worksheet B will update the
cells on Worksheet A..

Please help I said I thought it could be done and now Im not so sure..

Thanks guys! Youre fantantastic

Thanks Pete_UK!
Ok hasnt quite worked, can I give you the cell refs to make it a bit easier,
it might be me losing something in the translation to excel from the forum.

Im working within worksheet A named "A1_I" and aim to fill each cell with
this formula along row 7 left to right starting in column x (which represents
a monthly timescale, x is Jan-08, then y (Feb-08) etc up to DA, I want to
check this row x7:da7 against worksheet B named "Dynamic" which contains two
date ranges in cells N (start date) and O (end date - or 'Date range1') and U
and V Start and End date similarly - date range2. If the cell x7, y7 etc
falls within date range 1 on worksheet B "Dynamic" I want it to return the
fixed value from cell S7 (worksheet A) or return the value T7 (for each cell
along the row).

Does that make sense?

Great start though

Thanks

"Pete_UK" wrote:


Try something like this:

=IF(AND(cell_ref=B!StartDate1,cell_ref<=B!EndDate 1),B!
ref1,IF(AND(cell_ref=B!StartDate2,cell_ref<=B!End Date2),B!ref2,0))

where cell_ref is the date you want to test for, ref1 and ref2 are the
two cell references you mention. (It would be easier to quote the
actual cells you are using).

You might need to change = and <= to and < if you don't want to
include the start and end dates as part of the range.

Hope this helps.

Pete

On Jul 29, 12:31 pm, MarcusMac
wrote:
Hi Excel geniuses

I cant figure this one out. Can you help pls?

Imagine a time line left to right in monthly increments on a worksheet (A
say) and rows underneath.

I would like to set up a formula within the cells in worksheet A, that
dependent on whether that cell's date value lies within two date ranges held
on worksheet B (2 cells start/end date: StartDate1, EndDate1 : StartDate2,
End Date2), it returns either one of two cell references (2 cells holding
values) or a zero.

The idea being that changing the date ranges in worksheet B will update the
cells on Worksheet A..

Please help I said I thought it could be done and now Im not so sure..

Thanks guys! Youre fantantastic

Which row on worksheet B is the start date, end date contained in? Do
you want the formula to be in X8 (to compare with X7) and then be
copied across the row to DA8?

Pete

On Jul 30, 1:11*pm, MarcusMac
wrote:
Thanks Pete_UK!
Ok hasnt quite worked, can I give you the cell refs to make it a bit easier,
it might be me losing something in the translation to excel from the forum.

Im working within worksheet A named "A1_I" and aim to fill each cell with
this formula along row 7 left to right starting in column x (which represents
a monthly timescale, x is Jan-08, then y (Feb-08) etc up to DA, I want to
check this row x7:da7 against worksheet B named "Dynamic" which contains two
date ranges in cells N (start date) and O (end date - or 'Date range1') and U
and V Start and End date similarly - date range2. If the cell x7, y7 etc
falls within date range 1 on worksheet B "Dynamic" I want it to return the
fixed value from cell S7 (worksheet A) or return the value T7 (for each cell
along the row).

Does that make sense? *

Great start though

Thanks



"Pete_UK" wrote:
Try something like this:

=IF(AND(cell_ref=B!StartDate1,cell_ref<=B!EndDate 1),B!
ref1,IF(AND(cell_ref=B!StartDate2,cell_ref<=B!End Date2),B!ref2,0))

where cell_ref is the date you want to test for, ref1 and ref2 are the
two cell references you mention. (It would be easier to quote the
actual cells you are using).

You might need to change = and <= to and < if you don't want to
include the start and end dates as part of the range.

Hope this helps.

Pete

On Jul 29, 12:31 pm, MarcusMac
wrote:
Hi Excel geniuses

I cant figure this one out. Can you help pls?

Imagine a time line left to right in monthly increments on a worksheet (A
say) and rows underneath.

I would like to set up a formula within the cells in worksheet A, that
dependent on whether that cell's date value lies within two date ranges held
on worksheet B (2 cells start/end date: StartDate1, EndDate1 : StartDate2,
End Date2), it returns either one of two cell references (2 cells holding
values) or a zero.

The idea being that changing the date ranges in worksheet B will update the
cells on Worksheet A..

Please help I said I thought it could be done and now Im not so sure...

Thanks guys! Youre fantantastic- Hide quoted text -

- Show quoted text -

Hi Pete

Which row on worksheet B is the start date, end date contained in?
Row 6 in this case. Sorry I should have advised.

Do you want the formula to be in X8 (to compare with X7) and then be
copied across the row to DA8?
No not quite I need each cell in row7 to be an individual instance checking
itself (think of the row representing a monthly timeline) so each cell
compares itself against the date ranges in worksheet B Dynamic and return
either the value in the worksheet A S7 or T7 (values calculated from another
formula)..

Hope that makes sense

Thanks

"Pete_UK" wrote:

Which row on worksheet B is the start date, end date contained in? Do
you want the formula to be in X8 (to compare with X7) and then be
copied across the row to DA8?

Pete

On Jul 30, 1:11 pm, MarcusMac
wrote:
Thanks Pete_UK!
Ok hasnt quite worked, can I give you the cell refs to make it a bit easier,
it might be me losing something in the translation to excel from the forum.

Im working within worksheet A named "A1_I" and aim to fill each cell with
this formula along row 7 left to right starting in column x (which represents
a monthly timescale, x is Jan-08, then y (Feb-08) etc up to DA, I want to
check this row x7:da7 against worksheet B named "Dynamic" which contains two
date ranges in cells N (start date) and O (end date - or 'Date range1') and U
and V Start and End date similarly - date range2. If the cell x7, y7 etc
falls within date range 1 on worksheet B "Dynamic" I want it to return the
fixed value from cell S7 (worksheet A) or return the value T7 (for each cell
along the row).

Does that make sense?

Great start though

Thanks



"Pete_UK" wrote:
Try something like this:

=IF(AND(cell_ref=B!StartDate1,cell_ref<=B!EndDate 1),B!
ref1,IF(AND(cell_ref=B!StartDate2,cell_ref<=B!End Date2),B!ref2,0))

where cell_ref is the date you want to test for, ref1 and ref2 are the
two cell references you mention. (It would be easier to quote the
actual cells you are using).

You might need to change = and <= to and < if you don't want to
include the start and end dates as part of the range.

Hope this helps.

Pete

On Jul 29, 12:31 pm, MarcusMac
wrote:
Hi Excel geniuses

I cant figure this one out. Can you help pls?

Imagine a time line left to right in monthly increments on a worksheet (A
say) and rows underneath.

I would like to set up a formula within the cells in worksheet A, that
dependent on whether that cell's date value lies within two date ranges held
on worksheet B (2 cells start/end date: StartDate1, EndDate1 : StartDate2,
End Date2), it returns either one of two cell references (2 cells holding
values) or a zero.

The idea being that changing the date ranges in worksheet B will update the
cells on Worksheet A..

Please help I said I thought it could be done and now Im not so sure...

Thanks guys! Youre fantantastic- Hide quoted text -

- Show quoted text -