How can I do this ?

[Eric_in_EVV]

I have a spreadsheet that looks like this:

C D E F G
H I
row 29 18-Jan 19-Jan 20-Jan 21-Jan 22-Jan 23-Jan
24- Jan
row 30 DISC
row 31 ed
row 32 25-Jan 26-Jan 27-Jan 28-Jan 29-Jan 30-Jan
31- Jan
row 33 DISC
row 34 ed

What I need to be able to do is search for each occurance of the "DISC" and
then use the date (the cell above the "DISC") on a different worksheet in the
workbook that contains summary information. I need to be able to report each
date that the "DISC" code occurs.

I hope this all makes sense !

Thanks in advance for any suggestions !

 

[Squeaky]

Hi Eric,

How deep and wide is this spreadsheet with the dates and DISC info?

 

[Eric_in_EVV]

It is 7 columns wide (one for each day of the week) by about 500 rows

 

[Max]

Here's a formulas play which presumes that there will be only a single date
with "DISC" per week

Assuming your source data as posted is in Sheet1, with dates commencing in
C29:I29, followed by C32:I32, etc (spaced at intervals of 3 rows)

In another sheet,
Place in any start cell, say in B2, normal ENTER, format B2 as date to taste:
=INDEX(OFFSET(Sheet1!C$29:I$29,ROWS($1:1)*3-3,,),MATCH(TRUE,INDEX(OFFSET(Sheet1!C$29:I$29,ROWS($1:1)*3-2,,)="DISC",),0))
Copy B2 down as far as required. B2 returns the 1st week's date for the
DISC, B3 returns the 2nd week's date, and so on.

Perhaps better with an IF(ISNA(..) error trap to return neat looking blanks
for any week without a DISC, use this instead in B2, normal ENTER:
=IF(ISNA(MATCH(TRUE,INDEX(OFFSET(Sheet1!C$29:I$29,ROWS($1:1)*3-2,,)="DISC",),0)),"",INDEX(OFFSET(Sheet1!C$29:I$29,ROWS($1:1)*3-3,,),MATCH(TRUE,INDEX(OFFSET(Sheet1!C$29:I$29,ROWS($1:1)*3-2,,)="DISC",),0)))

Above helps? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Eric_in_EVV]

Thanks Max -

This is a good start for me. The one issue still to work out is to
accomodate for multiple occurances of "DISC" per week. I doubt it will
happen, but can not rule it out with 100% certainty, so I need to allow for
it in the calculations.

Any thoughts on that would be greatly appreciated !

 

[Max]

Maybe try transforming the source data into a regular data table (data in
columns), then we can simply use autofilter

In another sheet,
In B2:
=OFFSET(Sheet1!$C$29,INT((ROWS($1:1)-1)/7)*3,MOD(ROWS($1:1)-1,7))

In C2:
=OFFSET(Sheet1!$C$30,INT((ROWS($1:1)-1)/7)*3,MOD(ROWS($1:1)-1,7))
Copy B2:C2 down as far as required. Format col B as dates to taste. This
tranforms the source data in Sheet1 into 2 continuous columns of data: col B
= dates, col C = where the corresponding "DISC" text may appear. Now you
could apply autofilter on col C, filter for: DISC in C1, then just copy the
filtered rows in col B (these will be all the dates that you seek) to paste
special as values elsewhere.

 

[Don Guillett]

You coud use a FINDNEXT macro to do this. See the vba HELP index.
msgbox c.offset(-1)

 

[Eric_in_EVV]

Thanks Don - I'll check out FINDNEXT too !

You coud use a FINDNEXT macro to do this. See the vba HELP index.
msgbox c.offset(-1)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)


.