VLOOKUP help returning multiple results

I am working with 2 sheets and need to be able find a data match on one sheet 
and display multiple results on another sheet - here's a simplified version.

sheet 1 named Activities, sheet 2 named Calendar  
Activities sheet will list 100+ activities which will all have a start date. 
 Some activities will have the same start date. And any of the dates may 
change.
Example of Activities sheet:
Col 1                             Col 2
Start                              01-Jan-07
Identify site                    05-Feb-07
Enter in database            05-Jan-07
Contact project ldr          01-Jan-07
Sign Lease                     15-Mar-07                 

Calendar sheet:
The calendar sheet will look like a calendar. 01-Jan-07 = A1:A8, 02-Jan-07 = 
B1:B8 and so on. I want to display all activities (from Activities sheet) to 
display on the corresponding date in the calendar.  I am not an Excel 
poweruser by any means - hopefully there's a more simple answer than what 
I've found so far.

Many thanks,
El 

0
EL (12)
11/5/2007 9:28:08 PM
excel 39879 articles. 2 followers. Follow

15 Replies
391 Views

Similar Articles

[PageSpeed] 17

Hello El,

On the Calendar tab you want to insert columns between A and B to
insert the results of your lookup.

For example, insert a column between A and B, then in B1 enter the
formula:

=INDEX(Activities!A1:B100,SMALL(IF(Activities!
A1:B100="Start",ROW(Activities!A1:B100)-ROW(Activities!
A1)+1,ROW(B100)+1),1),2)

Assuming your data is in Activities tab, A1 through B100. This formula
will return the first occurrence of "Start" on the Activities sheet.

Check out this site for more info: http://www.bettersolutions.com/excel/EUV214/LN031821611.htm


HTH,
JP


On Nov 5, 4:28 pm, El <E...@discussions.microsoft.com> wrote:
> I am working with 2 sheets and need to be able find a data match on one sheet
> and display multiple results on another sheet - here's a simplified version.
>
> sheet 1 named Activities, sheet 2 named Calendar  
> Activities sheet will list 100+ activities which will all have a start date.
>  Some activities will have the same start date. And any of the dates may
> change.
> Example of Activities sheet:
> Col 1                             Col 2
> Start                              01-Jan-07
> Identify site                    05-Feb-07
> Enter in database            05-Jan-07
> Contact project ldr          01-Jan-07
> Sign Lease                     15-Mar-07                
>
> Calendar sheet:
> The calendar sheet will look like a calendar. 01-Jan-07 = A1:A8, 02-Jan-07 =
> B1:B8 and so on. I want to display all activities (from Activities sheet) to
> display on the corresponding date in the calendar.  I am not an Excel
> poweruser by any means - hopefully there's a more simple answer than what
> I've found so far.
>
> Many thanks,
> El


0
jpena (25)
11/6/2007 2:05:21 AM
JP, thanks for responding so quickly.  However, I need this formula to return 
all occurences of a match - not just the first.  The date is my search 
criteria.  Example, I want to search the Activities sheet for all activities 
with a date of "01-Jan-07".  On the Calendar sheet I have 6 lines underneath 
a heading of 01-Jan-07 - so what I want to see listed under that heading is 
"Start" and "Contact project ldr"...and whatever else that may have a date of 
01-Jan-07 from the Activities sheet. Maybe I should be using another function 
besides VLOOKUP?

Thanks,
El

"JP" wrote:

> Hello El,
> 
> On the Calendar tab you want to insert columns between A and B to
> insert the results of your lookup.
> 
> For example, insert a column between A and B, then in B1 enter the
> formula:
> 
> =INDEX(Activities!A1:B100,SMALL(IF(Activities!
> A1:B100="Start",ROW(Activities!A1:B100)-ROW(Activities!
> A1)+1,ROW(B100)+1),1),2)
> 
> Assuming your data is in Activities tab, A1 through B100. This formula
> will return the first occurrence of "Start" on the Activities sheet.
> 
> Check out this site for more info: http://www.bettersolutions.com/excel/EUV214/LN031821611.htm
> 
> 
> HTH,
> JP
> 
> 
> On Nov 5, 4:28 pm, El <E...@discussions.microsoft.com> wrote:
> > I am working with 2 sheets and need to be able find a data match on one sheet
> > and display multiple results on another sheet - here's a simplified version.
> >
> > sheet 1 named Activities, sheet 2 named Calendar  
> > Activities sheet will list 100+ activities which will all have a start date.
> >  Some activities will have the same start date. And any of the dates may
> > change.
> > Example of Activities sheet:
> > Col 1                             Col 2
> > Start                              01-Jan-07
> > Identify site                    05-Feb-07
> > Enter in database            05-Jan-07
> > Contact project ldr          01-Jan-07
> > Sign Lease                     15-Mar-07                
> >
> > Calendar sheet:
> > The calendar sheet will look like a calendar. 01-Jan-07 = A1:A8, 02-Jan-07 =
> > B1:B8 and so on. I want to display all activities (from Activities sheet) to
> > display on the corresponding date in the calendar.  I am not an Excel
> > poweruser by any means - hopefully there's a more simple answer than what
> > I've found so far.
> >
> > Many thanks,
> > El
> 
> 
> 
0
EL (12)
11/6/2007 2:31:02 PM
Can you confirm that you want your dates going across in your Calendar
sheet, and that you want 6 (or is it 8?) cells below to contain the
activities?

I think it would be better to have the dates going down in column A,
with activities listed across in columns B to H (or however many you
think you need).

You can do this by having a COUNTIF formula in an extra column on your
Activities sheet, and then use MATCH with INDEX (rather than VLOOKUP)
on your Calendar sheet. I can give you further details of the actual
formula if you can confirm your preferred layout on the Calendar
sheet.

Pete

On Nov 6, 2:31 pm, El <E...@discussions.microsoft.com> wrote:
> JP, thanks for responding so quickly.  However, I need this formula to return
> all occurences of a match - not just the first.  The date is my search
> criteria.  Example, I want to search the Activities sheet for all activities
> with a date of "01-Jan-07".  On the Calendar sheet I have 6 lines underneath
> a heading of 01-Jan-07 - so what I want to see listed under that heading is
> "Start" and "Contact project ldr"...and whatever else that may have a date of
> 01-Jan-07 from the Activities sheet. Maybe I should be using another function
> besides VLOOKUP?
>
> Thanks,
> El
>
>
>
> "JP" wrote:
> > Hello El,
>
> > On the Calendar tab you want to insert columns between A and B to
> > insert the results of your lookup.
>
> > For example, insert a column between A and B, then in B1 enter the
> > formula:
>
> > =INDEX(Activities!A1:B100,SMALL(IF(Activities!
> > A1:B100="Start",ROW(Activities!A1:B100)-ROW(Activities!
> > A1)+1,ROW(B100)+1),1),2)
>
> > Assuming your data is in Activities tab, A1 through B100. This formula
> > will return the first occurrence of "Start" on the Activities sheet.
>
> > Check out this site for more info:http://www.bettersolutions.com/excel/EUV214/LN031821611.htm
>
> > HTH,
> > JP
>
> > On Nov 5, 4:28 pm, El <E...@discussions.microsoft.com> wrote:
> > > I am working with 2 sheets and need to be able find a data match on one sheet
> > > and display multiple results on another sheet - here's a simplified version.
>
> > > sheet 1 named Activities, sheet 2 named Calendar  
> > > Activities sheet will list 100+ activities which will all have a start date.
> > >  Some activities will have the same start date. And any of the dates may
> > > change.
> > > Example of Activities sheet:
> > > Col 1                             Col 2
> > > Start                              01-Jan-07
> > > Identify site                    05-Feb-07
> > > Enter in database            05-Jan-07
> > > Contact project ldr          01-Jan-07
> > > Sign Lease                     15-Mar-07                
>
> > > Calendar sheet:
> > > The calendar sheet will look like a calendar. 01-Jan-07 = A1:A8, 02-Jan-07 =
> > > B1:B8 and so on. I want to display all activities (from Activities sheet) to
> > > display on the corresponding date in the calendar.  I am not an Excel
> > > poweruser by any means - hopefully there's a more simple answer than what
> > > I've found so far.
>
> > > Many thanks,
> > > El- Hide quoted text -
>
> - Show quoted text -


0
pashurst (2576)
11/6/2007 3:14:56 PM
Hi Pete,
Yes, that's about it.  It looks like a calendar, A1=Sunday, B1=Monday, 
C1=Tuesday and on to G1=Saturday. A2=31-Dec-06, A3,A4,A5,A6,A7,A8 are 
blank... B2= 01-Jan-07, B3,B4...B8 are blank..and so on.  All the days of the 
year are like that and on this calendar.  What I need is to have all 
activities from the Activities sheet to be listed under the appropriate date 
on the calendar. Because I couldn't figure this out, what I did originally 
was to sort all the activities by date then copy/pasted the activities to the 
appropriate date on the calendar. Problem is, it becomes obsolete once the 
dates change. Sorry if it's confusing - I'm confusing me.

Thanks,
El

"Pete_UK" wrote:

> Can you confirm that you want your dates going across in your Calendar
> sheet, and that you want 6 (or is it 8?) cells below to contain the
> activities?
> 
> I think it would be better to have the dates going down in column A,
> with activities listed across in columns B to H (or however many you
> think you need).
> 
> You can do this by having a COUNTIF formula in an extra column on your
> Activities sheet, and then use MATCH with INDEX (rather than VLOOKUP)
> on your Calendar sheet. I can give you further details of the actual
> formula if you can confirm your preferred layout on the Calendar
> sheet.
> 
> Pete
> 
> On Nov 6, 2:31 pm, El <E...@discussions.microsoft.com> wrote:
> > JP, thanks for responding so quickly.  However, I need this formula to return
> > all occurences of a match - not just the first.  The date is my search
> > criteria.  Example, I want to search the Activities sheet for all activities
> > with a date of "01-Jan-07".  On the Calendar sheet I have 6 lines underneath
> > a heading of 01-Jan-07 - so what I want to see listed under that heading is
> > "Start" and "Contact project ldr"...and whatever else that may have a date of
> > 01-Jan-07 from the Activities sheet. Maybe I should be using another function
> > besides VLOOKUP?
> >
> > Thanks,
> > El
> >
> >
> >
> > "JP" wrote:
> > > Hello El,
> >
> > > On the Calendar tab you want to insert columns between A and B to
> > > insert the results of your lookup.
> >
> > > For example, insert a column between A and B, then in B1 enter the
> > > formula:
> >
> > > =INDEX(Activities!A1:B100,SMALL(IF(Activities!
> > > A1:B100="Start",ROW(Activities!A1:B100)-ROW(Activities!
> > > A1)+1,ROW(B100)+1),1),2)
> >
> > > Assuming your data is in Activities tab, A1 through B100. This formula
> > > will return the first occurrence of "Start" on the Activities sheet.
> >
> > > Check out this site for more info:http://www.bettersolutions.com/excel/EUV214/LN031821611.htm
> >
> > > HTH,
> > > JP
> >
> > > On Nov 5, 4:28 pm, El <E...@discussions.microsoft.com> wrote:
> > > > I am working with 2 sheets and need to be able find a data match on one sheet
> > > > and display multiple results on another sheet - here's a simplified version.
> >
> > > > sheet 1 named Activities, sheet 2 named Calendar  
> > > > Activities sheet will list 100+ activities which will all have a start date.
> > > >  Some activities will have the same start date. And any of the dates may
> > > > change.
> > > > Example of Activities sheet:
> > > > Col 1                             Col 2
> > > > Start                              01-Jan-07
> > > > Identify site                    05-Feb-07
> > > > Enter in database            05-Jan-07
> > > > Contact project ldr          01-Jan-07
> > > > Sign Lease                     15-Mar-07                
> >
> > > > Calendar sheet:
> > > > The calendar sheet will look like a calendar. 01-Jan-07 = A1:A8, 02-Jan-07 =
> > > > B1:B8 and so on. I want to display all activities (from Activities sheet) to
> > > > display on the corresponding date in the calendar.  I am not an Excel
> > > > poweruser by any means - hopefully there's a more simple answer than what
> > > > I've found so far.
> >
> > > > Many thanks,
> > > > El- Hide quoted text -
> >
> > - Show quoted text -
> 
> 
> 
0
EL (12)
11/6/2007 6:29:01 PM
Hi Pete,	
Yes, that's about it.  It looks like a calendar, A1=Sunday, B1=Monday, 
C1=Tuesday and on to G1=Saturday. A2=31-Dec-06, A3,A4,A5,A6,A7,A8 are 
blank... B2= 01-Jan-07, B3,B4...B8 are blank..and so on.  All the days of the 
year are like that and on this calendar.  What I need is to have all 
activities from the Activities sheet to be listed under the appropriate date 
on the calendar. Because I couldn't figure this out, what I did originally 
was to sort all the activities by date then copy/pasted the activities to the 
appropriate date on the calendar. Problem is, it becomes obsolete once the 
dates change. 

Thanks,
El


"Pete_UK" wrote:

> Can you confirm that you want your dates going across in your Calendar
> sheet, and that you want 6 (or is it 8?) cells below to contain the
> activities?
> 
> I think it would be better to have the dates going down in column A,
> with activities listed across in columns B to H (or however many you
> think you need).
> 
> You can do this by having a COUNTIF formula in an extra column on your
> Activities sheet, and then use MATCH with INDEX (rather than VLOOKUP)
> on your Calendar sheet. I can give you further details of the actual
> formula if you can confirm your preferred layout on the Calendar
> sheet.
> 
> Pete
> 
> On Nov 6, 2:31 pm, El <E...@discussions.microsoft.com> wrote:
> > JP, thanks for responding so quickly.  However, I need this formula to return
> > all occurences of a match - not just the first.  The date is my search
> > criteria.  Example, I want to search the Activities sheet for all activities
> > with a date of "01-Jan-07".  On the Calendar sheet I have 6 lines underneath
> > a heading of 01-Jan-07 - so what I want to see listed under that heading is
> > "Start" and "Contact project ldr"...and whatever else that may have a date of
> > 01-Jan-07 from the Activities sheet. Maybe I should be using another function
> > besides VLOOKUP?
> >
> > Thanks,
> > El
> >
> >
> >
> > "JP" wrote:
> > > Hello El,
> >
> > > On the Calendar tab you want to insert columns between A and B to
> > > insert the results of your lookup.
> >
> > > For example, insert a column between A and B, then in B1 enter the
> > > formula:
> >
> > > =INDEX(Activities!A1:B100,SMALL(IF(Activities!
> > > A1:B100="Start",ROW(Activities!A1:B100)-ROW(Activities!
> > > A1)+1,ROW(B100)+1),1),2)
> >
> > > Assuming your data is in Activities tab, A1 through B100. This formula
> > > will return the first occurrence of "Start" on the Activities sheet.
> >
> > > Check out this site for more info:http://www.bettersolutions.com/excel/EUV214/LN031821611.htm
> >
> > > HTH,
> > > JP
> >
> > > On Nov 5, 4:28 pm, El <E...@discussions.microsoft.com> wrote:
> > > > I am working with 2 sheets and need to be able find a data match on one sheet
> > > > and display multiple results on another sheet - here's a simplified version.
> >
> > > > sheet 1 named Activities, sheet 2 named Calendar  
> > > > Activities sheet will list 100+ activities which will all have a start date.
> > > >  Some activities will have the same start date. And any of the dates may
> > > > change.
> > > > Example of Activities sheet:
> > > > Col 1                             Col 2
> > > > Start                              01-Jan-07
> > > > Identify site                    05-Feb-07
> > > > Enter in database            05-Jan-07
> > > > Contact project ldr          01-Jan-07
> > > > Sign Lease                     15-Mar-07                
> >
> > > > Calendar sheet:
> > > > The calendar sheet will look like a calendar. 01-Jan-07 = A1:A8, 02-Jan-07 =
> > > > B1:B8 and so on. I want to display all activities (from Activities sheet) to
> > > > display on the corresponding date in the calendar.  I am not an Excel
> > > > poweruser by any means - hopefully there's a more simple answer than what
> > > > I've found so far.
> >
> > > > Many thanks,
> > > > El- Hide quoted text -
> >
> > - Show quoted text -
> 
> 
> 
0
EL (12)
11/6/2007 6:31:05 PM
I thought you said you could have several activities on one day, so
you would need to display them going across (not days of the week).
Anyway, this is what I have done:

In the Activities sheet, with Activity in column A and date in Column
B and with a header row, I put this formula in C2:

=B2&"_"&COUNTIF(B$2:B2,B2)

and copied it down. It gives these results for your sample data:

01-Jan-07	     39083_1
05-Feb-07	     39118_1
05-Jan-07	     39087_1
01-Jan-07	     39083_2
15-Mar-07	     39156_1

i.e. it takes each serial number for the date and increments a counter
to keep track of duplicates (you can see this for the second event on
1st Jan).

Then in the Calendar sheet I put the numbers 1 to 7 in cells B1:H1,
and filled consecutive dates down from A2, starting with 1st Jan 2007.
Then I put this formula in B2 of this sheet:

=IF(ISNA(MATCH($A2&"_"&B$1,Activities!$C$2:$C
$1000,0)),"",INDEX(Activities!$A$2:$A$1000,MATCH($A2&"_"&B
$1,Activities!$C$2:$C$1000,0)))

The formula can be copied into C2:H2, and then the formulae in B2:H2
can be copied down for as many dates as you have. This will pick up
the activies and put them in appropriate cells on the row that the
date occurs. If you have more than one activity on a particular date,
these will be shown across the row in columns B to H, and if you are
likely to have more than 7 activities on any particular date, you can
just extend the numbering across the top and copy the formula into
more columns. You will have blanks where there are no activities.

I have assumed that you might have up to 1000 entries on the
activities sheet - adjust this as necessary three times in the
formula.

Hope this helps.

Pete


On Nov 6, 6:31 pm, El <E...@discussions.microsoft.com> wrote:
> Hi Pete,        
> Yes, that's about it.  It looks like a calendar, A1=Sunday, B1=Monday,
> C1=Tuesday and on to G1=Saturday. A2=31-Dec-06, A3,A4,A5,A6,A7,A8 are
> blank... B2= 01-Jan-07, B3,B4...B8 are blank..and so on.  All the days of the
> year are like that and on this calendar.  What I need is to have all
> activities from the Activities sheet to be listed under the appropriate date
> on the calendar. Because I couldn't figure this out, what I did originally
> was to sort all the activities by date then copy/pasted the activities to the
> appropriate date on the calendar. Problem is, it becomes obsolete once the
> dates change.
>
> Thanks,
> El
>
>
>
> "Pete_UK" wrote:
> > Can you confirm that you want your dates going across in your Calendar
> > sheet, and that you want 6 (or is it 8?) cells below to contain the
> > activities?
>
> > I think it would be better to have the dates going down in column A,
> > with activities listed across in columns B to H (or however many you
> > think you need).
>
> > You can do this by having a COUNTIF formula in an extra column on your
> > Activities sheet, and then use MATCH with INDEX (rather than VLOOKUP)
> > on your Calendar sheet. I can give you further details of the actual
> > formula if you can confirm your preferred layout on the Calendar
> > sheet.
>
> > Pete
>
> > On Nov 6, 2:31 pm, El <E...@discussions.microsoft.com> wrote:
> > > JP, thanks for responding so quickly.  However, I need this formula to return
> > > all occurences of a match - not just the first.  The date is my search
> > > criteria.  Example, I want to search the Activities sheet for all activities
> > > with a date of "01-Jan-07".  On the Calendar sheet I have 6 lines underneath
> > > a heading of 01-Jan-07 - so what I want to see listed under that heading is
> > > "Start" and "Contact project ldr"...and whatever else that may have a date of
> > > 01-Jan-07 from the Activities sheet. Maybe I should be using another function
> > > besides VLOOKUP?
>
> > > Thanks,
> > > El
>
> > > "JP" wrote:
> > > > Hello El,
>
> > > > On the Calendar tab you want to insert columns between A and B to
> > > > insert the results of your lookup.
>
> > > > For example, insert a column between A and B, then in B1 enter the
> > > > formula:
>
> > > > =INDEX(Activities!A1:B100,SMALL(IF(Activities!
> > > > A1:B100="Start",ROW(Activities!A1:B100)-ROW(Activities!
> > > > A1)+1,ROW(B100)+1),1),2)
>
> > > > Assuming your data is in Activities tab, A1 through B100. This formula
> > > > will return the first occurrence of "Start" on the Activities sheet.
>
> > > > Check out this site for more info:http://www.bettersolutions.com/excel/EUV214/LN031821611.htm
>
> > > > HTH,
> > > > JP
>
> > > > On Nov 5, 4:28 pm, El <E...@discussions.microsoft.com> wrote:
> > > > > I am working with 2 sheets and need to be able find a data match on one sheet
> > > > > and display multiple results on another sheet - here's a simplified version.
>
> > > > > sheet 1 named Activities, sheet 2 named Calendar  
> > > > > Activities sheet will list 100+ activities which will all have a start date.
> > > > >  Some activities will have the same start date. And any of the dates may
> > > > > change.
> > > > > Example of Activities sheet:
> > > > > Col 1                             Col 2
> > > > > Start                              01-Jan-07
> > > > > Identify site                    05-Feb-07
> > > > > Enter in database            05-Jan-07
> > > > > Contact project ldr          01-Jan-07
> > > > > Sign Lease                     15-Mar-07                
>
> > > > > Calendar sheet:
> > > > > The calendar sheet will look like a calendar. 01-Jan-07 = A1:A8, 02-Jan-07 =
> > > > > B1:B8 and so on. I want to display all activities (from Activities sheet) to
> > > > > display on the corresponding date in the calendar.  I am not an Excel
> > > > > poweruser by any means - hopefully there's a more simple answer than what
> > > > > I've found so far.
>
> > > > > Many thanks,
> > > > > El- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -


0
pashurst (2576)
11/6/2007 7:05:02 PM
Yes, there could be several activities for one day...listed on one of the six 
lines under the day/date. In my example 2 activities have a date of 
01-Jan-07..."Start" and "Contact project ldr".  On the calendar, I want it to 
look like this:

01-Jan-07 <--row 2
Start  <--row3
Contact project ldr  <--row 4
rows 5 through 8 are blank because there are no other activities that have a 
date of 01-Jan-07.

I'm sure I'm not explaining this well but it sounds like it can't be done if 
I want the results/activities to show up on lines under the date instead of 
across.  Bottom line is it has to look like a calendar.  

But thanks for trying.  El.

"Pete_UK" wrote:

> I thought you said you could have several activities on one day, so
> you would need to display them going across (not days of the week).
> Anyway, this is what I have done:
> 
> In the Activities sheet, with Activity in column A and date in Column
> B and with a header row, I put this formula in C2:
> 
> =B2&"_"&COUNTIF(B$2:B2,B2)
> 
> and copied it down. It gives these results for your sample data:
> 
> 01-Jan-07	     39083_1
> 05-Feb-07	     39118_1
> 05-Jan-07	     39087_1
> 01-Jan-07	     39083_2
> 15-Mar-07	     39156_1
> 
> i.e. it takes each serial number for the date and increments a counter
> to keep track of duplicates (you can see this for the second event on
> 1st Jan).
> 
> Then in the Calendar sheet I put the numbers 1 to 7 in cells B1:H1,
> and filled consecutive dates down from A2, starting with 1st Jan 2007.
> Then I put this formula in B2 of this sheet:
> 
> =IF(ISNA(MATCH($A2&"_"&B$1,Activities!$C$2:$C
> $1000,0)),"",INDEX(Activities!$A$2:$A$1000,MATCH($A2&"_"&B
> $1,Activities!$C$2:$C$1000,0)))
> 
> The formula can be copied into C2:H2, and then the formulae in B2:H2
> can be copied down for as many dates as you have. This will pick up
> the activies and put them in appropriate cells on the row that the
> date occurs. If you have more than one activity on a particular date,
> these will be shown across the row in columns B to H, and if you are
> likely to have more than 7 activities on any particular date, you can
> just extend the numbering across the top and copy the formula into
> more columns. You will have blanks where there are no activities.
> 
> I have assumed that you might have up to 1000 entries on the
> activities sheet - adjust this as necessary three times in the
> formula.
> 
> Hope this helps.
> 
> Pete
> 
> 
> On Nov 6, 6:31 pm, El <E...@discussions.microsoft.com> wrote:
> > Hi Pete,        
> > Yes, that's about it.  It looks like a calendar, A1=Sunday, B1=Monday,
> > C1=Tuesday and on to G1=Saturday. A2=31-Dec-06, A3,A4,A5,A6,A7,A8 are
> > blank... B2= 01-Jan-07, B3,B4...B8 are blank..and so on.  All the days of the
> > year are like that and on this calendar.  What I need is to have all
> > activities from the Activities sheet to be listed under the appropriate date
> > on the calendar. Because I couldn't figure this out, what I did originally
> > was to sort all the activities by date then copy/pasted the activities to the
> > appropriate date on the calendar. Problem is, it becomes obsolete once the
> > dates change.
> >
> > Thanks,
> > El
> >
> >
> >
> > "Pete_UK" wrote:
> > > Can you confirm that you want your dates going across in your Calendar
> > > sheet, and that you want 6 (or is it 8?) cells below to contain the
> > > activities?
> >
> > > I think it would be better to have the dates going down in column A,
> > > with activities listed across in columns B to H (or however many you
> > > think you need).
> >
> > > You can do this by having a COUNTIF formula in an extra column on your
> > > Activities sheet, and then use MATCH with INDEX (rather than VLOOKUP)
> > > on your Calendar sheet. I can give you further details of the actual
> > > formula if you can confirm your preferred layout on the Calendar
> > > sheet.
> >
> > > Pete
> >
> > > On Nov 6, 2:31 pm, El <E...@discussions.microsoft.com> wrote:
> > > > JP, thanks for responding so quickly.  However, I need this formula to return
> > > > all occurences of a match - not just the first.  The date is my search
> > > > criteria.  Example, I want to search the Activities sheet for all activities
> > > > with a date of "01-Jan-07".  On the Calendar sheet I have 6 lines underneath
> > > > a heading of 01-Jan-07 - so what I want to see listed under that heading is
> > > > "Start" and "Contact project ldr"...and whatever else that may have a date of
> > > > 01-Jan-07 from the Activities sheet. Maybe I should be using another function
> > > > besides VLOOKUP?
> >
> > > > Thanks,
> > > > El
> >
> > > > "JP" wrote:
> > > > > Hello El,
> >
> > > > > On the Calendar tab you want to insert columns between A and B to
> > > > > insert the results of your lookup.
> >
> > > > > For example, insert a column between A and B, then in B1 enter the
> > > > > formula:
> >
> > > > > =INDEX(Activities!A1:B100,SMALL(IF(Activities!
> > > > > A1:B100="Start",ROW(Activities!A1:B100)-ROW(Activities!
> > > > > A1)+1,ROW(B100)+1),1),2)
> >
> > > > > Assuming your data is in Activities tab, A1 through B100. This formula
> > > > > will return the first occurrence of "Start" on the Activities sheet.
> >
> > > > > Check out this site for more info:http://www.bettersolutions.com/excel/EUV214/LN031821611.htm
> >
> > > > > HTH,
> > > > > JP
> >
> > > > > On Nov 5, 4:28 pm, El <E...@discussions.microsoft.com> wrote:
> > > > > > I am working with 2 sheets and need to be able find a data match on one sheet
> > > > > > and display multiple results on another sheet - here's a simplified version.
> >
> > > > > > sheet 1 named Activities, sheet 2 named Calendar  
> > > > > > Activities sheet will list 100+ activities which will all have a start date.
> > > > > >  Some activities will have the same start date. And any of the dates may
> > > > > > change.
> > > > > > Example of Activities sheet:
> > > > > > Col 1                             Col 2
> > > > > > Start                              01-Jan-07
> > > > > > Identify site                    05-Feb-07
> > > > > > Enter in database            05-Jan-07
> > > > > > Contact project ldr          01-Jan-07
> > > > > > Sign Lease                     15-Mar-07                
> >
> > > > > > Calendar sheet:
> > > > > > The calendar sheet will look like a calendar. 01-Jan-07 = A1:A8, 02-Jan-07 =
> > > > > > B1:B8 and so on. I want to display all activities (from Activities sheet) to
> > > > > > display on the corresponding date in the calendar.  I am not an Excel
> > > > > > poweruser by any means - hopefully there's a more simple answer than what
> > > > > > I've found so far.
> >
> > > > > > Many thanks,
> > > > > > El- Hide quoted text -
> >
> > > > - Show quoted text -- Hide quoted text -
> >
> > - Show quoted text -
> 
> 
> 
0
EL (12)
11/6/2007 8:04:00 PM
There are many styles of calendar, so there is not much point in
saying you want it to "look" like a calendar.

Do you want seven columns, one for each day of the week, and blocks of
7 rows representing each week? So, if the first column represents
Sunday then the first date will be 31st Dec 2006 in B2, then in B9 the
date would be 7th Jan 2007, then in B16 the date would be 14th Jan
2007, and so on?

Pete

By the way, did you try the solution I offered before?


On Nov 6, 8:04 pm, El <E...@discussions.microsoft.com> wrote:
> Yes, there could be several activities for one day...listed on one of the six
> lines under the day/date. In my example 2 activities have a date of
> 01-Jan-07..."Start" and "Contact project ldr".  On the calendar, I want it to
> look like this:
>
> 01-Jan-07 <--row 2
> Start  <--row3
> Contact project ldr  <--row 4
> rows 5 through 8 are blank because there are no other activities that have a
> date of 01-Jan-07.
>
> I'm sure I'm not explaining this well but it sounds like it can't be done if
> I want the results/activities to show up on lines under the date instead of
> across.  Bottom line is it has to look like a calendar.  
>
> But thanks for trying.  El.
>
>
>
> "Pete_UK" wrote:
> > I thought you said you could have several activities on one day, so
> > you would need to display them going across (not days of the week).
> > Anyway, this is what I have done:
>
> > In the Activities sheet, with Activity in column A and date in Column
> > B and with a header row, I put this formula in C2:
>
> > =B2&"_"&COUNTIF(B$2:B2,B2)
>
> > and copied it down. It gives these results for your sample data:
>
> > 01-Jan-07       39083_1
> > 05-Feb-07       39118_1
> > 05-Jan-07       39087_1
> > 01-Jan-07       39083_2
> > 15-Mar-07       39156_1
>
> > i.e. it takes each serial number for the date and increments a counter
> > to keep track of duplicates (you can see this for the second event on
> > 1st Jan).
>
> > Then in the Calendar sheet I put the numbers 1 to 7 in cells B1:H1,
> > and filled consecutive dates down from A2, starting with 1st Jan 2007.
> > Then I put this formula in B2 of this sheet:
>
> > =IF(ISNA(MATCH($A2&"_"&B$1,Activities!$C$2:$C
> > $1000,0)),"",INDEX(Activities!$A$2:$A$1000,MATCH($A2&"_"&B
> > $1,Activities!$C$2:$C$1000,0)))
>
> > The formula can be copied into C2:H2, and then the formulae in B2:H2
> > can be copied down for as many dates as you have. This will pick up
> > the activies and put them in appropriate cells on the row that the
> > date occurs. If you have more than one activity on a particular date,
> > these will be shown across the row in columns B to H, and if you are
> > likely to have more than 7 activities on any particular date, you can
> > just extend the numbering across the top and copy the formula into
> > more columns. You will have blanks where there are no activities.
>
> > I have assumed that you might have up to 1000 entries on the
> > activities sheet - adjust this as necessary three times in the
> > formula.
>
> > Hope this helps.
>
> > Pete
>
> > On Nov 6, 6:31 pm, El <E...@discussions.microsoft.com> wrote:
> > > Hi Pete,        
> > > Yes, that's about it.  It looks like a calendar, A1=Sunday, B1=Monday,
> > > C1=Tuesday and on to G1=Saturday. A2=31-Dec-06, A3,A4,A5,A6,A7,A8 are
> > > blank... B2= 01-Jan-07, B3,B4...B8 are blank..and so on.  All the days of the
> > > year are like that and on this calendar.  What I need is to have all
> > > activities from the Activities sheet to be listed under the appropriate date
> > > on the calendar. Because I couldn't figure this out, what I did originally
> > > was to sort all the activities by date then copy/pasted the activities to the
> > > appropriate date on the calendar. Problem is, it becomes obsolete once the
> > > dates change.
>
> > > Thanks,
> > > El
>
> > > "Pete_UK" wrote:
> > > > Can you confirm that you want your dates going across in your Calendar
> > > > sheet, and that you want 6 (or is it 8?) cells below to contain the
> > > > activities?
>
> > > > I think it would be better to have the dates going down in column A,
> > > > with activities listed across in columns B to H (or however many you
> > > > think you need).
>
> > > > You can do this by having a COUNTIF formula in an extra column on your
> > > > Activities sheet, and then use MATCH with INDEX (rather than VLOOKUP)
> > > > on your Calendar sheet. I can give you further details of the actual
> > > > formula if you can confirm your preferred layout on the Calendar
> > > > sheet.
>
> > > > Pete
>
> > > > On Nov 6, 2:31 pm, El <E...@discussions.microsoft.com> wrote:
> > > > > JP, thanks for responding so quickly.  However, I need this formula to return
> > > > > all occurences of a match - not just the first.  The date is my search
> > > > > criteria.  Example, I want to search the Activities sheet for all activities
> > > > > with a date of "01-Jan-07".  On the Calendar sheet I have 6 lines underneath
> > > > > a heading of 01-Jan-07 - so what I want to see listed under that heading is
> > > > > "Start" and "Contact project ldr"...and whatever else that may have a date of
> > > > > 01-Jan-07 from the Activities sheet. Maybe I should be using another function
> > > > > besides VLOOKUP?
>
> > > > > Thanks,
> > > > > El
>
> > > > > "JP" wrote:
> > > > > > Hello El,
>
> > > > > > On the Calendar tab you want to insert columns between A and B to
> > > > > > insert the results of your lookup.
>
> > > > > > For example, insert a column between A and B, then in B1 enter the
> > > > > > formula:
>
> > > > > > =INDEX(Activities!A1:B100,SMALL(IF(Activities!
> > > > > > A1:B100="Start",ROW(Activities!A1:B100)-ROW(Activities!
> > > > > > A1)+1,ROW(B100)+1),1),2)
>
> > > > > > Assuming your data is in Activities tab, A1 through B100. This formula
> > > > > > will return the first occurrence of "Start" on the Activities sheet.
>
> > > > > > Check out this site for more info:http://www.bettersolutions.com/excel/EUV214/LN031821611.htm
>
> > > > > > HTH,
> > > > > > JP
>
> > > > > > On Nov 5, 4:28 pm, El <E...@discussions.microsoft.com> wrote:
> > > > > > > I am working with 2 sheets and need to be able find a data match on one sheet
> > > > > > > and display multiple results on another sheet - here's a simplified version.
>
> > > > > > > sheet 1 named Activities, sheet 2 named Calendar  
> > > > > > > Activities sheet will list 100+ activities which will all have a start date.
> > > > > > >  Some activities will have the same start date. And any of the dates may
> > > > > > > change.
> > > > > > > Example of Activities sheet:
> > > > > > > Col 1                             Col 2
> > > > > > > Start                              01-Jan-07
> > > > > > > Identify site                    05-Feb-07
> > > > > > > Enter in database            05-Jan-07
> > > > > > > Contact project ldr          01-Jan-07
> > > > > > > Sign Lease                     15-Mar-07                
>
> > > > > > > Calendar sheet:
> > > > > > > The calendar sheet will look like a calendar. 01-Jan-07 = A1:A8, 02-Jan-07 =
> > > > > > > B1:B8 and so on. I want to display all activities (from Activities sheet) to
> > > > > > > display on the corresponding date in the calendar.  I am not an Excel
> > > > > > > poweruser by any means - hopefully there's a more simple answer than what
> > > > > > > I've found so far.
>
> > > > > > > Many thanks,
> > > > > > > El- Hide quoted text -
>
> > > > > - Show quoted text -- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -


0
pashurst (2576)
11/6/2007 10:41:50 PM
I've put a sample workbook together based on a calendar format. If you
would like to see it then send me an email to:

pashurst <at> auditel.net

(change the obvious), and I can post it to you.

Pete

On Nov 6, 10:41 pm, Pete_UK <pashu...@auditel.net> wrote:
> There are many styles of calendar, so there is not much point in
> saying you want it to "look" like a calendar.
>
> Do you want seven columns, one for each day of the week, and blocks of
> 7 rows representing each week? So, if the first column represents
> Sunday then the first date will be 31st Dec 2006 in B2, then in B9 the
> date would be 7th Jan 2007, then in B16 the date would be 14th Jan
> 2007, and so on?
>
> Pete
>
> By the way, did you try the solution I offered before?
>
> On Nov 6, 8:04 pm, El <E...@discussions.microsoft.com> wrote:
>
>
>
> > Yes, there could be several activities for one day...listed on one of the six
> > lines under the day/date. In my example 2 activities have a date of
> > 01-Jan-07..."Start" and "Contact project ldr".  On the calendar, I want it to
> > look like this:
>
> > 01-Jan-07 <--row 2
> > Start  <--row3
> > Contact project ldr  <--row 4
> > rows 5 through 8 are blank because there are no other activities that have a
> > date of 01-Jan-07.
>
> > I'm sure I'm not explaining this well but it sounds like it can't be done if
> > I want the results/activities to show up on lines under the date instead of
> > across.  Bottom line is it has to look like a calendar.  
>
> > But thanks for trying.  El.
>
> > "Pete_UK" wrote:
> > > I thought you said you could have several activities on one day, so
> > > you would need to display them going across (not days of the week).
> > > Anyway, this is what I have done:
>
> > > In the Activities sheet, with Activity in column A and date in Column
> > > B and with a header row, I put this formula in C2:
>
> > > =B2&"_"&COUNTIF(B$2:B2,B2)
>
> > > and copied it down. It gives these results for your sample data:
>
> > > 01-Jan-07       39083_1
> > > 05-Feb-07       39118_1
> > > 05-Jan-07       39087_1
> > > 01-Jan-07       39083_2
> > > 15-Mar-07       39156_1
>
> > > i.e. it takes each serial number for the date and increments a counter
> > > to keep track of duplicates (you can see this for the second event on
> > > 1st Jan).
>
> > > Then in the Calendar sheet I put the numbers 1 to 7 in cells B1:H1,
> > > and filled consecutive dates down from A2, starting with 1st Jan 2007.
> > > Then I put this formula in B2 of this sheet:
>
> > > =IF(ISNA(MATCH($A2&"_"&B$1,Activities!$C$2:$C
> > > $1000,0)),"",INDEX(Activities!$A$2:$A$1000,MATCH($A2&"_"&B
> > > $1,Activities!$C$2:$C$1000,0)))
>
> > > The formula can be copied into C2:H2, and then the formulae in B2:H2
> > > can be copied down for as many dates as you have. This will pick up
> > > the activies and put them in appropriate cells on the row that the
> > > date occurs. If you have more than one activity on a particular date,
> > > these will be shown across the row in columns B to H, and if you are
> > > likely to have more than 7 activities on any particular date, you can
> > > just extend the numbering across the top and copy the formula into
> > > more columns. You will have blanks where there are no activities.
>
> > > I have assumed that you might have up to 1000 entries on the
> > > activities sheet - adjust this as necessary three times in the
> > > formula.
>
> > > Hope this helps.
>
> > > Pete
>
> > > On Nov 6, 6:31 pm, El <E...@discussions.microsoft.com> wrote:
> > > > Hi Pete,        
> > > > Yes, that's about it.  It looks like a calendar, A1=Sunday, B1=Monday,
> > > > C1=Tuesday and on to G1=Saturday. A2=31-Dec-06, A3,A4,A5,A6,A7,A8 are
> > > > blank... B2= 01-Jan-07, B3,B4...B8 are blank..and so on.  All the days of the
> > > > year are like that and on this calendar.  What I need is to have all
> > > > activities from the Activities sheet to be listed under the appropriate date
> > > > on the calendar. Because I couldn't figure this out, what I did originally
> > > > was to sort all the activities by date then copy/pasted the activities to the
> > > > appropriate date on the calendar. Problem is, it becomes obsolete once the
> > > > dates change.
>
> > > > Thanks,
> > > > El
>
> > > > "Pete_UK" wrote:
> > > > > Can you confirm that you want your dates going across in your Calendar
> > > > > sheet, and that you want 6 (or is it 8?) cells below to contain the
> > > > > activities?
>
> > > > > I think it would be better to have the dates going down in column A,
> > > > > with activities listed across in columns B to H (or however many you
> > > > > think you need).
>
> > > > > You can do this by having a COUNTIF formula in an extra column on your
> > > > > Activities sheet, and then use MATCH with INDEX (rather than VLOOKUP)
> > > > > on your Calendar sheet. I can give you further details of the actual
> > > > > formula if you can confirm your preferred layout on the Calendar
> > > > > sheet.
>
> > > > > Pete
>
> > > > > On Nov 6, 2:31 pm, El <E...@discussions.microsoft.com> wrote:
> > > > > > JP, thanks for responding so quickly.  However, I need this formula to return
> > > > > > all occurences of a match - not just the first.  The date is my search
> > > > > > criteria.  Example, I want to search the Activities sheet for all activities
> > > > > > with a date of "01-Jan-07".  On the Calendar sheet I have 6 lines underneath
> > > > > > a heading of 01-Jan-07 - so what I want to see listed under that heading is
> > > > > > "Start" and "Contact project ldr"...and whatever else that may have a date of
> > > > > > 01-Jan-07 from the Activities sheet. Maybe I should be using another function
> > > > > > besides VLOOKUP?
>
> > > > > > Thanks,
> > > > > > El
>
> > > > > > "JP" wrote:
> > > > > > > Hello El,
>
> > > > > > > On the Calendar tab you want to insert columns between A and B to
> > > > > > > insert the results of your lookup.
>
> > > > > > > For example, insert a column between A and B, then in B1 enter the
> > > > > > > formula:
>
> > > > > > > =INDEX(Activities!A1:B100,SMALL(IF(Activities!
> > > > > > > A1:B100="Start",ROW(Activities!A1:B100)-ROW(Activities!
> > > > > > > A1)+1,ROW(B100)+1),1),2)
>
> > > > > > > Assuming your data is in Activities tab, A1 through B100. This formula
> > > > > > > will return the first occurrence of "Start" on the Activities sheet.
>
> > > > > > > Check out this site for more info:http://www.bettersolutions.com/excel/EUV214/LN031821611.htm
>
> > > > > > > HTH,
> > > > > > > JP
>
> > > > > > > On Nov 5, 4:28 pm, El <E...@discussions.microsoft.com> wrote:
> > > > > > > > I am working with 2 sheets and need to be able find a data match on one sheet
> > > > > > > > and display multiple results on another sheet - here's a simplified version.
>
> > > > > > > > sheet 1 named Activities, sheet 2 named Calendar  
> > > > > > > > Activities sheet will list 100+ activities which will all have a start date.
> > > > > > > >  Some activities will have the same start date. And any of the dates may
> > > > > > > > change.
> > > > > > > > Example of Activities sheet:
> > > > > > > > Col 1                             Col 2
> > > > > > > > Start                              01-Jan-07
> > > > > > > > Identify site                    05-Feb-07
> > > > > > > > Enter in database            05-Jan-07
> > > > > > > > Contact project ldr          01-Jan-07
> > > > > > > > Sign Lease                     15-Mar-07                
>
> > > > > > > > Calendar sheet:
> > > > > > > > The calendar sheet will look like a calendar. 01-Jan-07 = A1:A8, 02-Jan-07 =
> > > > > > > > B1:B8 and so on. I want to display all activities (from Activities sheet) to
> > > > > > > > display on the corresponding date in the calendar.  I am not an Excel
> > > > > > > > poweruser by any means - hopefully there's a more simple answer than what
> > > > > > > > I've found so far.
>
> > > > > > > > Many thanks,
> > > > > > > > El- Hide quoted text -
>
> > > > > > - Show quoted text -- Hide quoted text -
>
> > > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -


0
pashurst (2576)
11/6/2007 11:57:07 PM
One alternative formulas play which might deliver it here ...

Illustrated in this sample at:
http://www.flypicture.com/download/NTEwNTE=
Listing multiple activities under date cols.xls

(If reading this in MS webpage, do a copy n paste of the entire link above 
into your browser, inclusive of the "=" at the end. Do not click direct on 
the link)

Source data is assumed in a sheet: A, 
within cols A and B, 
col A = activities descriptions, col B = dates, 
data from row1 down

In a helper sheet: x,

List the calendar dates in A1 down, eg:
01-Jan-07
02-Jan-07
03-Jan-07
etc

Put in B1:
=IF(A!$B1="","",IF(A!$B1=INDEX($A:$A,COLUMNS($A:A)),ROW(),""))
Copy B1 across by as many cols as there are calendar dates listed in col A, 
then fill down to cover the max expected extent of source data in A.

Then in your results sheet: R 
(this is your "calendar" sheet)

In A1:
=IF(INDEX(x!$A:$A,COLUMNS($A:A))=0,"",INDEX(x!$A:$A,COLUMNS($A:A)))
Copy A1 across as far as required to return the calendar dates from col A in 
sheet: x.

In A2:
=IF(ROWS($1:1)>COUNT(x!B:B),"",INDEX(A!$A:$A,SMALL(x!B:B,ROWS($1:1))))
Copy A2 across/fill down as far as required to return the activities listed 
in col A in sheet: A under the appropriate calendar date
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"El" wrote:
> Yes, there could be several activities for one day...listed on one of the six 
> lines under the day/date. In my example 2 activities have a date of 
> 01-Jan-07..."Start" and "Contact project ldr".  On the calendar, I want it to 
> look like this:
> 
> 01-Jan-07 <--row 2
> Start  <--row3
> Contact project ldr  <--row 4
> rows 5 through 8 are blank because there are no other activities that have a 
> date of 01-Jan-07.
> 
> I'm sure I'm not explaining this well but it sounds like it can't be done if 
> I want the results/activities to show up on lines under the date instead of 
> across.  Bottom line is it has to look like a calendar.  
0
demechanik (4694)
11/7/2007 2:00:01 AM
Max,
This is very very close! But on the calendar sheet (your sheet R) I want to 
see "seven columns, one for each day of the week, and blocks of 7 rows 
representing each week? So, if the first column represents Sunday then the 
first date will be 31st Dec 2006 in B2, then in B9 the date would be 7th Jan 
2007, then in B16 the date would be 14th Jan 2007, and so on"...like Pete_UK 
said - except the first col representing Sunday would be A.  Whereas you have 
all the dates going across. 

Is it possible to do what I want? If yes, I will work with what you have 
already done and figure it out. 

Thanks so much for your time and help!
El

"Max" wrote:

> One alternative formulas play which might deliver it here ...
> 
> Illustrated in this sample at:
> http://www.flypicture.com/download/NTEwNTE=
> Listing multiple activities under date cols.xls
> 
> (If reading this in MS webpage, do a copy n paste of the entire link above 
> into your browser, inclusive of the "=" at the end. Do not click direct on 
> the link)
> 
> Source data is assumed in a sheet: A, 
> within cols A and B, 
> col A = activities descriptions, col B = dates, 
> data from row1 down
> 
> In a helper sheet: x,
> 
> List the calendar dates in A1 down, eg:
> 01-Jan-07
> 02-Jan-07
> 03-Jan-07
> etc
> 
> Put in B1:
> =IF(A!$B1="","",IF(A!$B1=INDEX($A:$A,COLUMNS($A:A)),ROW(),""))
> Copy B1 across by as many cols as there are calendar dates listed in col A, 
> then fill down to cover the max expected extent of source data in A.
> 
> Then in your results sheet: R 
> (this is your "calendar" sheet)
> 
> In A1:
> =IF(INDEX(x!$A:$A,COLUMNS($A:A))=0,"",INDEX(x!$A:$A,COLUMNS($A:A)))
> Copy A1 across as far as required to return the calendar dates from col A in 
> sheet: x.
> 
> In A2:
> =IF(ROWS($1:1)>COUNT(x!B:B),"",INDEX(A!$A:$A,SMALL(x!B:B,ROWS($1:1))))
> Copy A2 across/fill down as far as required to return the activities listed 
> in col A in sheet: A under the appropriate calendar date
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "El" wrote:
> > Yes, there could be several activities for one day...listed on one of the six 
> > lines under the day/date. In my example 2 activities have a date of 
> > 01-Jan-07..."Start" and "Contact project ldr".  On the calendar, I want it to 
> > look like this:
> > 
> > 01-Jan-07 <--row 2
> > Start  <--row3
> > Contact project ldr  <--row 4
> > rows 5 through 8 are blank because there are no other activities that have a 
> > date of 01-Jan-07.
> > 
> > I'm sure I'm not explaining this well but it sounds like it can't be done if 
> > I want the results/activities to show up on lines under the date instead of 
> > across.  Bottom line is it has to look like a calendar.  
0
EL (12)
11/7/2007 3:55:04 PM
Awaiting your email if you want a copy of my calendar version - it is
laid out how you asked for it.

Pete

On Nov 7, 3:55 pm, El <E...@discussions.microsoft.com> wrote:
> Max,
> This is very very close! But on the calendar sheet (your sheet R) I want to
> see "seven columns, one for each day of the week, and blocks of 7 rows
> representing each week? So, if the first column represents Sunday then the
> first date will be 31st Dec 2006 in B2, then in B9 the date would be 7th Jan
> 2007, then in B16 the date would be 14th Jan 2007, and so on"...like Pete_UK
> said - except the first col representing Sunday would be A.  Whereas you have
> all the dates going across.
>
> Is it possible to do what I want? If yes, I will work with what you have
> already done and figure it out.
>
> Thanks so much for your time and help!
> El
>
>
>
> "Max" wrote:
> > One alternative formulas play which might deliver it here ...
>
> > Illustrated in this sample at:
> >http://www.flypicture.com/download/NTEwNTE=
> > Listing multiple activities under date cols.xls
>
> > (If reading this in MS webpage, do a copy n paste of the entire link above
> > into your browser, inclusive of the "=" at the end. Do not click direct on
> > the link)
>
> > Source data is assumed in a sheet: A,
> > within cols A and B,
> > col A = activities descriptions, col B = dates,
> > data from row1 down
>
> > In a helper sheet: x,
>
> > List the calendar dates in A1 down, eg:
> > 01-Jan-07
> > 02-Jan-07
> > 03-Jan-07
> > etc
>
> > Put in B1:
> > =IF(A!$B1="","",IF(A!$B1=INDEX($A:$A,COLUMNS($A:A)),ROW(),""))
> > Copy B1 across by as many cols as there are calendar dates listed in col A,
> > then fill down to cover the max expected extent of source data in A.
>
> > Then in your results sheet: R
> > (this is your "calendar" sheet)
>
> > In A1:
> > =IF(INDEX(x!$A:$A,COLUMNS($A:A))=0,"",INDEX(x!$A:$A,COLUMNS($A:A)))
> > Copy A1 across as far as required to return the calendar dates from col A in
> > sheet: x.
>
> > In A2:
> > =IF(ROWS($1:1)>COUNT(x!B:B),"",INDEX(A!$A:$A,SMALL(x!B:B,ROWS($1:1))))
> > Copy A2 across/fill down as far as required to return the activities listed
> > in col A in sheet: A under the appropriate calendar date
> > --
> > Max
> > Singapore
> >http://savefile.com/projects/236895
> > xdemechanik
> > ---
> > "El" wrote:
> > > Yes, there could be several activities for one day...listed on one of the six
> > > lines under the day/date. In my example 2 activities have a date of
> > > 01-Jan-07..."Start" and "Contact project ldr".  On the calendar, I want it to
> > > look like this:
>
> > > 01-Jan-07 <--row 2
> > > Start  <--row3
> > > Contact project ldr  <--row 4
> > > rows 5 through 8 are blank because there are no other activities that have a
> > > date of 01-Jan-07.
>
> > > I'm sure I'm not explaining this well but it sounds like it can't be done if
> > > I want the results/activities to show up on lines under the date instead of
> > > across.  Bottom line is it has to look like a calendar.  - Hide quoted text -
>
> - Show quoted text -


0
pashurst (2576)
11/7/2007 4:01:12 PM
Pete,
Your calendar version is exactly what I am looking for.

Thanks again for your help,
El

"Pete_UK" wrote:

> Awaiting your email if you want a copy of my calendar version - it is
> laid out how you asked for it.
> 
> Pete
> 
> On Nov 7, 3:55 pm, El <E...@discussions.microsoft.com> wrote:
> > Max,
> > This is very very close! But on the calendar sheet (your sheet R) I want to
> > see "seven columns, one for each day of the week, and blocks of 7 rows
> > representing each week? So, if the first column represents Sunday then the
> > first date will be 31st Dec 2006 in B2, then in B9 the date would be 7th Jan
> > 2007, then in B16 the date would be 14th Jan 2007, and so on"...like Pete_UK
> > said - except the first col representing Sunday would be A.  Whereas you have
> > all the dates going across.
> >
> > Is it possible to do what I want? If yes, I will work with what you have
> > already done and figure it out.
> >
> > Thanks so much for your time and help!
> > El
> >
> >
> >
> > "Max" wrote:
> > > One alternative formulas play which might deliver it here ...
> >
> > > Illustrated in this sample at:
> > >http://www.flypicture.com/download/NTEwNTE=
> > > Listing multiple activities under date cols.xls
> >
> > > (If reading this in MS webpage, do a copy n paste of the entire link above
> > > into your browser, inclusive of the "=" at the end. Do not click direct on
> > > the link)
> >
> > > Source data is assumed in a sheet: A,
> > > within cols A and B,
> > > col A = activities descriptions, col B = dates,
> > > data from row1 down
> >
> > > In a helper sheet: x,
> >
> > > List the calendar dates in A1 down, eg:
> > > 01-Jan-07
> > > 02-Jan-07
> > > 03-Jan-07
> > > etc
> >
> > > Put in B1:
> > > =IF(A!$B1="","",IF(A!$B1=INDEX($A:$A,COLUMNS($A:A)),ROW(),""))
> > > Copy B1 across by as many cols as there are calendar dates listed in col A,
> > > then fill down to cover the max expected extent of source data in A.
> >
> > > Then in your results sheet: R
> > > (this is your "calendar" sheet)
> >
> > > In A1:
> > > =IF(INDEX(x!$A:$A,COLUMNS($A:A))=0,"",INDEX(x!$A:$A,COLUMNS($A:A)))
> > > Copy A1 across as far as required to return the calendar dates from col A in
> > > sheet: x.
> >
> > > In A2:
> > > =IF(ROWS($1:1)>COUNT(x!B:B),"",INDEX(A!$A:$A,SMALL(x!B:B,ROWS($1:1))))
> > > Copy A2 across/fill down as far as required to return the activities listed
> > > in col A in sheet: A under the appropriate calendar date
> > > --
> > > Max
> > > Singapore
> > >http://savefile.com/projects/236895
> > > xdemechanik
> > > ---
> > > "El" wrote:
> > > > Yes, there could be several activities for one day...listed on one of the six
> > > > lines under the day/date. In my example 2 activities have a date of
> > > > 01-Jan-07..."Start" and "Contact project ldr".  On the calendar, I want it to
> > > > look like this:
> >
> > > > 01-Jan-07 <--row 2
> > > > Start  <--row3
> > > > Contact project ldr  <--row 4
> > > > rows 5 through 8 are blank because there are no other activities that have a
> > > > date of 01-Jan-07.
> >
> > > > I'm sure I'm not explaining this well but it sounds like it can't be done if
> > > > I want the results/activities to show up on lines under the date instead of
> > > > across.  Bottom line is it has to look like a calendar.  - Hide quoted text -
> >
> > - Show quoted text -
> 
> 
> 
0
EL (12)
11/7/2007 4:52:02 PM
Glad to hear it - thanks for feeding back.

Pete

On Nov 7, 4:52 pm, El <E...@discussions.microsoft.com> wrote:
> Pete,
> Your calendar version is exactly what I am looking for.
>
> Thanks again for your help,
> El
>
>
>
> "Pete_UK" wrote:
> > Awaiting your email if you want a copy of my calendar version - it is
> > laid out how you asked for it.
>
> > Pete
>
> > On Nov 7, 3:55 pm, El <E...@discussions.microsoft.com> wrote:
> > > Max,
> > > This is very very close! But on the calendar sheet (your sheet R) I want to
> > > see "seven columns, one for each day of the week, and blocks of 7 rows
> > > representing each week? So, if the first column represents Sunday then the
> > > first date will be 31st Dec 2006 in B2, then in B9 the date would be 7th Jan
> > > 2007, then in B16 the date would be 14th Jan 2007, and so on"...like Pete_UK
> > > said - except the first col representing Sunday would be A.  Whereas you have
> > > all the dates going across.
>
> > > Is it possible to do what I want? If yes, I will work with what you have
> > > already done and figure it out.
>
> > > Thanks so much for your time and help!
> > > El
>
> > > "Max" wrote:
> > > > One alternative formulas play which might deliver it here ...
>
> > > > Illustrated in this sample at:
> > > >http://www.flypicture.com/download/NTEwNTE=
> > > > Listing multiple activities under date cols.xls
>
> > > > (If reading this in MS webpage, do a copy n paste of the entire link above
> > > > into your browser, inclusive of the "=" at the end. Do not click direct on
> > > > the link)
>
> > > > Source data is assumed in a sheet: A,
> > > > within cols A and B,
> > > > col A = activities descriptions, col B = dates,
> > > > data from row1 down
>
> > > > In a helper sheet: x,
>
> > > > List the calendar dates in A1 down, eg:
> > > > 01-Jan-07
> > > > 02-Jan-07
> > > > 03-Jan-07
> > > > etc
>
> > > > Put in B1:
> > > > =IF(A!$B1="","",IF(A!$B1=INDEX($A:$A,COLUMNS($A:A)),ROW(),""))
> > > > Copy B1 across by as many cols as there are calendar dates listed in col A,
> > > > then fill down to cover the max expected extent of source data in A.
>
> > > > Then in your results sheet: R
> > > > (this is your "calendar" sheet)
>
> > > > In A1:
> > > > =IF(INDEX(x!$A:$A,COLUMNS($A:A))=0,"",INDEX(x!$A:$A,COLUMNS($A:A)))
> > > > Copy A1 across as far as required to return the calendar dates from col A in
> > > > sheet: x.
>
> > > > In A2:
> > > > =IF(ROWS($1:1)>COUNT(x!B:B),"",INDEX(A!$A:$A,SMALL(x!B:B,ROWS($1:1))))
> > > > Copy A2 across/fill down as far as required to return the activities listed
> > > > in col A in sheet: A under the appropriate calendar date
> > > > --
> > > > Max
> > > > Singapore
> > > >http://savefile.com/projects/236895
> > > > xdemechanik
> > > > ---
> > > > "El" wrote:
> > > > > Yes, there could be several activities for one day...listed on one of the six
> > > > > lines under the day/date. In my example 2 activities have a date of
> > > > > 01-Jan-07..."Start" and "Contact project ldr".  On the calendar, I want it to
> > > > > look like this:
>
> > > > > 01-Jan-07 <--row 2
> > > > > Start  <--row3
> > > > > Contact project ldr  <--row 4
> > > > > rows 5 through 8 are blank because there are no other activities that have a
> > > > > date of 01-Jan-07.
>
> > > > > I'm sure I'm not explaining this well but it sounds like it can't be done if
> > > > > I want the results/activities to show up on lines under the date instead of
> > > > > across.  Bottom line is it has to look like a calendar.  - Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -


0
pashurst (2576)
11/7/2007 6:56:45 PM
Looks like you've got exactly what you wanted from Pete.
Reserve the earlier offering for use in another scenario which suits.
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
--- 
"El" <El@discussions.microsoft.com> wrote in message 
news:08AA9849-402D-4474-98A9-08AF0372579B@microsoft.com...
> Max,
> This is very very close! But on the calendar sheet (your sheet R) I want 
> to
> see "seven columns, one for each day of the week, and blocks of 7 rows
> representing each week? So, if the first column represents Sunday then the
> first date will be 31st Dec 2006 in B2, then in B9 the date would be 7th 
> Jan
> 2007, then in B16 the date would be 14th Jan 2007, and so on"...like 
> Pete_UK
> said - except the first col representing Sunday would be A.  Whereas you 
> have
> all the dates going across.
>
> Is it possible to do what I want? If yes, I will work with what you have
> already done and figure it out.
>
> Thanks so much for your time and help!
> El


0
demechanik (4694)
11/8/2007 12:27:23 AM
Reply:

Similar Artilces:

HELP!!!! RMS
Could anyone tell me if there is any requirement by Microsoft to first become a "certified partner" before being elligible for sitting in RMS - store operations certification exam? in simple words, Is there any Autorization code (given by micorosft to its certifed partners) required at the time of registring forthis course's exam? Or whether I can give this exam as an Independant IT professional? just like MCSD etc. I cant find this information anywhere and prometric testing centres at my locality do not have this information. Your timely help would indeed be appreciat...

Help Help!!! RPC over HTTP
My computer has all the certificate authority trusted in my root certification authorities. I still cant get it to work it will only work when I have the computer in the same network. I have Win20003 server, Exchange SP1 and Windows XP SP1. can someone help me this is a stand alone server. I checked everything and everything looks ok. I must be missing something, Note that when I do a https://server/remote it takes me direct to the page because the certificate is already installed. thanks for your help Hi there, i hope you don't mind im joining in your conversation. actually im trying to...

Help with LoadImage() API pls
Hi, I'm running in a MFC extension DLL and trying to extract a bitmap resource Here's what I've got HANDLE hBmp =LoadImage( NULL, MAKEINTRESOURCE(IDB_BMPC1), IMAGE_BITMAP, 0, 0, LR_CREATEDIBSECTION | LR_DEFAULTSIZE ); GetLastError returns 1814 1814 The specified resource name cannot be found in the image file. ERROR_RESOURCE_NAME_NOT_FOUND This is also true of FindResource() For the first parameter I've tried AfxGetResourceHandle() AfxGetApp()->m_hInstance HANDLE LoadImage( HINSTANCE hinst, // handle to instance LPCTSTR lpszName, // ...

using a date in vlookup
i want to perform a vlookup using the Now() function to generate the lookup value (A1), the 1st column in the table [col B] array will be all the dates in a year listed consequtive,, and the 2nd column being a value assigned to each day in the 1st column [B]. My formula is vlookup(A1,B1:C367,2). The result I get is "#N/A. What am i doing wrong? Thanks Tonso NOW() returns both the date and the time, so you would be better off using TODAY(), which only returns the date. Another problem might be that your dates in column B are really text values that just look like dates - see what happe...

Year End Close
When a Year End Close is performed, the documentation states (paraphrasing) that the P&L accounts are closed to Retained Earnings. What is the exact result? Does it mean in the old year a JE is created that shows a Credit to the Expense accounts (if they have a debit balance), a Debit to the Revenue Accounts (if they have a credit balance) and a Net Debit/Credit to the Retained Earnings account? This JE shows in the History Detail Inquiry for that historical year? It is a separate JE # from the Beginning Balance entry in the New Year? There is actually a Period 0 (shown on the...

CHTMLView help needed
I have a CHTMLView and I want to save the output from it as a bitmap (or some other image type) file. Does anyone know how to render the content of the view to an image file (IE5.5 and up)? The normal standard Windows GDI based approach doesn't seem to work because it only gets the area of the view that is visible and includes the view's scroll bars in the output. Is there away to get cleaner output rendered to an impage file that doesn't have the scroll bars and shows the whole content, not just what is currently visible onscreen? ---------------------------- I know one of you ...

My Outlook 2002 Rules have disappeared. Help
My rules have disappeared. And the Tools menu Rules options have disappeared also. Plus, when I click on right click on a piece of mail, and pick the create rule option, nothing happens. So I have three problems: 1. Outlook has lost all of my rules. Not I big deal I did not have many. 2. It will not create a rule. A big deal as I can not enter any new rules. 3. The Tools menu does not have and will not take a Run Rules option. So basically all commands, options, features related to rules are inoperable. What kind of cs program is this. By the way, my Outlook is on one computer. ...

Multiple Search Criteria/ Index Match
I am using the following formula to retrieve data that matches tw specific criteria. =INDEX(C2:C80,MATCH(1,(A2:A80="Liverpool")*(B2:B80="January"),0)) using ctrl,shift, enter to give curly brackets to make it work. This formula is then repeated in the cell directly below, but searche for "February", below that "March" and so on. However when I copy my formula, I have to change the month for eac cell and therefore have to put in the curly brackets again. Is there anyway around this -- Message posted from http://www.ExcelForum.com Have a list with the ...

Help #48
How do I stop the automatic download of messages in Outlook In OL2003, tools>send/receive>send/receive settings>disable scheduled send/receive. Other versions, not sure. I think it's in options. HTH Mark -----Original Message----- From: Andrea [mailto:Andrea@discussions.microsoft.com] Posted At: 30 September 2005 08:36 Posted To: microsoft.public.outlook Conversation: Help Subject: Help How do I stop the automatic download of messages in Outlook -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.8/114 - Release...

Need Help #2
Hi This is Rehan Akhter from Delhi india. I want to detect any external device(like USB, CD/VCD, Ethernet, Modem, Serial Port, Parallel Port etc) and the same time i want protect copying the data from our local drive to any external drive. Thanks in advance R e h a n You might want to post this question on microsoft.public.win32.programmer.kernel where the device driver experts hang out. joe On 11 Feb 2007 22:18:46 -0800, "Guddu" <rehan.akhter@gmail.com> wrote: >Hi >This is Rehan Akhter from Delhi india. I want to detect any external >device(like USB, CD/VCD...

Songs overlap. Please help!
I've done this same slide show numerous times with no problems. I now have a different group of songs inserted into the show and the program seems to recognize the songs length as 0. If I have one song playing per slide, there is no problem. If I have 2 songs and have them automatically to play one after another, it starts them at the same time (unless I put in a delay start with the 2nd song and it has to be longer than the 1st song). If I go to custom animation, right click timing, then go to sound settings tab and look under information/total playing time, it shows 0 on...

HELP: Running two Macros, one before the other #2
I have this Excel spreadsheet in which I want to automate, I have two macros once called Macro_GetData, and Macro_FormatData. Both these Macros work fine, but when I try to automate them it runs the 2nd Macro before the 1st Macro is even finish. I tried to use the "Application.Wait" command but that just stopped the macros. What I want the macro to do is to run the first Macro (Macro_GetData) and when that's finished then run the second Macro (Macro_FormatData). How can I go about doing this? ...

keybd_event and labels HELP!!!
Hi, I'm writing an "on screen keyboard" like the windows one, with Visual C++ 6 and MFC support. I'm looking at SendInput and keybd_event functions to simulate keyboard events. I saw there are many problems (maybe focus derived) to send character messages to other windows like Internet Explorer... and in general with all the applications in which the cursor is active in a text box. Is there a solution to this problem? Thanks a lot! ;) ...

Column help please
I have addresses in one column in an excel worksheet. When I highlite the address, it shows up correctly in the fx box (showing name on one line, address on the next, city, state and zip on last “line). However, when I pick the any address in the drop down box I made, the address shows up as one long line, not as the multiple lines I need. How can I have this show up as I need? Ignore this post, already figured out "galgolfer63" wrote: > I have addresses in one column in an excel worksheet. When I highlite the > address, it shows up correctly in the fx box (showing na...

vlookup excel and access...
assuming i have this code, is possible to use this vlookup withnthe adta into mdb access?... old scenario: Private Sub TextBox25_Change() Dim CODICE As Integer Select Case Me.TextBox25 Case "" Me.TextBox4 = "" Case 1 To 8 CODICE = Val(Me.TextBox25) Me.TextBox4 = Application.WorksheetFunction.VLookup _ (CODICE, Worksheets("TABELLA").Range("Q2:R9"), 2, False) Case Else Call MULTI_LINE_BOX End Select End Sub new scenario: Inested column Q and R in excel i have created a mdb into: \\my server\myserverdir\USER.MDB and into this mdb have inserte a table U...

Multiple charts in ChartSpace; problems with double Categories
I want to show two or more charts with different categories and data in one chartspace, so I did the following: 1. Create Chartspace 2. Add Chart1 in Chartspace 3. Add Series in Chart1 4. Series.SetData chDimCategories 'A,B,C' 5. Series.SetData chDimValues '5,2,6' Now I see a chart with categories A (value=5), B (value=2) and C (value=6). So everything okay. Next thing I do: 6. Add Chart2 in Chartspace Now I see a second empty chart, BUT WITH already filled categories A,B and C!! I don't want this, because I want to use other categories in this second chart2. And whe...

HELP My Inventory has disappeared
Dear Readers Now you see it - now you don't !! I have been using Money for over 1 year and all of a sudden my home inventory has disappeared. The box is still checked to include in the net worth summary, but it no longer appears in the report? My Money version is Version 14.0.120.1105 ( a UK version, perhaps?) I've spent a lot of time to develop all the accounts only to loose the inventory at the last hurdle...please help In microsoft.public.money, josh8176 wrote: >Dear Readers >Now you see it - now you don't !! >I have been using Money for over 1 year and all of ...

Vlookup within a vlookup
I am trying to lookup a cell within a table - but the table to use is found in another table. =VLOOKUP(B3,VLOOKUP(B2,F3:G9,2,2),2) Cell B2 is a dropdown box allowing one of the choices in colum f below. Column G represents which table to use for the initial lookup based on your choice in the drop down dox. column f column g Alt A 30 Yr fixed30 Alt A 15 Yr fixed15 All I get is an error - can someone help ? Thanks, Yosef It sounds like you would need to use INDIRECT within the lookup formula http://tinyurl.com/czxtt that thread shows the way to do it exce...

Please Help Me Secure My Form Results!!!
I have a FP 2003 form on a SSL page that contains confidential information. I would like to send it to a password protected page that my other employees can access with a unique username/password and then print the form results. I currently send it to the _private folder in formatted text– but I am the only one who can access it-- through FP. I tried to setup a subsite – but the Browse button in FrontPage Form Results does not see the subsite. I have tried other forums and paid for advice from “experts” on other sites – but no one seems to be able to tell me exactly how this ...

Lost on Vlookup, match, etc....
Can someone walk me through this please? I a workbook that imports a years worth of data from filemaker to be analyzed and charted in excel. It contains several sheets, but I am concerned with worksheet 1 (daily data) and worksheet 2 (bodyweight). Daily data contains the raw data I pull in from Filemaker. It results in a table with a row for each day of the year. It has 12 columns, but in this instance, I am only interested in 2 of the colums Column F, (Date), and Column R (Bodyweight). There is only one entry per week for body weight. The bodyweight sheet has 3 columns (week #, date, and w...

power options properties help me understand
I have recently re done my laptop, running windows xp, sp3, I am unsure what to set in the power options properties. My laptop is always plugged into power sourse, so I do not keep battery in all the time. I was told to , by dell, to shut down over night when not using it. Ok, but in the daytime if I am away where do I leave it in- do I log off, go to standby or hibernate?I do not see a sleep button on my keyboard- very confusing- at the moment this is what I have done-turn off moniter-never-plugged in-turn off hard disks-never- system standby-never- system hibernates-never-unde...

Forwarding Multiple Emails to AOL
I have about 2,400 email messages saved on Outlook that I need to transfer to an AOL Email account. How can I do this without emailing each individually? "awerhun" <awerhun@discussions.microsoft.com> wrote in message news:B2B78760-FADA-47C9-BF21-1597E345CF71@microsoft.com... >I have about 2,400 email messages saved on Outlook that I need to transfer to > an AOL Email account. How can I do this without emailing each individually? Simply create your AOL account in Outlook, then drag the messages to the AOL folders. -- Brian Tillman [MVP-Outlook] O...

Help with dlookup please.
Can someone please help me determine what is wrong with the following code: Me.txtPalletNo = DLookup("[PalletBarcode]", "[dbo_StockLog]", "[BoxBarcode]=" & [Forms]![frmTraceBox]!txtBoxCode) Where... PalletBarcode is the value I want to retrieve. dbo_StockLog is the table in the data is stored in. This table is a linked table from an SQL server. I can open, add/delete data to the table, use it in querries, etc. BoxBarcode that I need to match from my form (frmTraceBox) txtBoxCode is the control on my form that contains the value I am try...

Location of Right Click Start Logo Items
Hi, I have changed the openning location for Windows Explorer at the default location under accessaries and is all good load to new location, however when I use the right click start logo shortcuts "Explore, Open all users, and Explore all users" its not starting at he new location. I want to change the right click start logo shortcuts starting locations but when I right click the shortcuts instead of getting a list so I can go to properties it actually runs the shortcut. I would like to know where to find the actual shortcuts so I can change the starting locatio...

VLOOKUP in VBA
On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...