Help With Conditional 1

[PCHomepage]

I have a worksheet with column captions and the first data field in A2 is getting a date value from another Setup tab as:

=Setup!$A$2

This provides the starting date to seed the remainder of the values. The other cells in the Date column simply add a week to the seeded date using:

=A2+7 and so forth down the column.

The data is sorted by proper names and the list varies from time to time so I want to add a conditional that will use the Setup date only if the entry is in the first data row and to add the week for the rest of the successive rows after that.

Also, there is another column with a Boolean value of 0/1 or null/1. The date must not show unless the value is 1 but those dates that do show must each be a week apart without skipping any weeks.

I am at a loss as to how to do this. Any advice?

Don

 

[SkipVought]

hi,

It the dates are in column A and the booleans are in column B, then
[tt]
=OR(SUM(B1:B2)=2,SUM(B2:B3)=2)
[/tt]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PCHomepage]

Thanks, Skip! I tried it but it gives only a "TRUE" or "FALSE" in column A. The Boolean is in H so here is what I put:

=OR(SUM(H1:H2)=2,SUM(H2:H3)=2)

I also still need to figure out how to tell it to seed A2 regardless of the sort order.

Incidentally, this is Excel 2007.

Don

 

[Gavona]

With Heading in A2 then in A3 and copied Down
=IF(H3,(MAX(A$2:A2,setup!$A$2)+7)*H3,"")






Gavin

 

[Gavona]

Oops. I left in the legacy of my first idea. Won't hurt but does nothing. The solution in A3 should be:
=IF(H3,(MAX(A$2:A2,setup!$A$2)+7),"")

(The original idea also works to an extent but not so well:
=MAX(A$2:A2,setup!$A$2)+7)*H3)


Gavin

 

[SkipVought]

=A2+7 and so forth down the column.

I also still need to figure out how to tell it to seed A2 regardless of the sort order.

The "sort order" is, according to your first formula, DATE!!!

That is what this formula assumes.

And YES, it returns TRUE/FALSE, which is what a CF uses.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Skip, I don't think the question is about Conditional Format!

Gavin

 

[PCHomepage]

Thanks, Gavin! My understanding of conditionals is great in PHP and other scripting languages but I've done very little in Excel.

The one you supplied, as I understand it, looks like it should do exactly what I need and indeed it does if I paste it into A3 and then down the line. It skips those without a 1 in the H column and it shows the proper dates in those with it. Perfect!

Can something be added to take A2's value coming from another tab so that no matter the sort order, it is always A2 that is seeded?

Don

 

[Gavona]

The Formula assumed that the first date would go in A3 and you would have headings in A2. Easy for you to adjust. In A2 you would enter this and copy it down.
=IF(H2,(MAX(A$1:A1,setup!$A$2)+7),"")

As per your original post it will NOT seed A2 from the other sheet unless H2 is True/1

Gavin

 

[PCHomepage]

Thanks again, all! The solutions are perfect. An easy fix to the A2 question is to simply lock the column from being selected and, therefor, it can't accidentally be sorted!

Don