• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Populate info with a date range

cparks

cparks

Member
So Ive been fooling around with some ideas, none of which would/won't work.

Maybe theres a reason I havent been getting much attention on this subject because either 1. It's too complex without needing some green to get an answer
2. Im not concise enough for people to understand what Im asking about

So Im going to do my best in asking and giving info...(Im going to attach a file to help visualize what Im explaining)

Ive made a 3 month calendar and Im using it to show upcoming events/activities for the fam (ball games, practices, tournaments, work schedules, vacation, meetings, etc). For each day there can be 7 events/activities. Within each of the 7 cells there is an array formula extracting information that i type in from another sheet which is concatenated and shoes up on the calendar.

I have formatted the calendar just as I want it and everything works just as I need it to. My only problem is, if an activity last longer than two days, it only shows the activity on the dates that I entered. (i.e. John- Camp @ Camp David 3 Feb - 10 Feb...Only shows below the 3 Feb and 10 Feb cell, not 3 Feb THROUGH 10 Feb).
 

Attachments

@Faseeh You're the BEST!!! If you only knew how long I've been trying to figure this out/search for an answer....YOU have definitely answered my question to the "T". Much kudos! I will pay it forward. Thanks again for your help, guidance, and quick response!!!
 
@Faseeh

Ok...have a problem. Not a very big one, just an aggravating problem. I had to tweak the array formula a lil bit because I needed more "room". In your formula it was 15 rows of data. So instead of it saying =...$I$2:$I$15...and so on, I had it say $I$2:$I$200 and so on due to having a three month calendar. Not that I need that much data to enter, I may only need 100-150 to be safe. The problem Im running into is towards the end of the formula where it says ROW($A$1:$A$15)...I tried to make it also to $A$1:$A$200 and when i copy and drag the formula, it gives me blank cells.

I sort of understand the formula (I'm still new to using arrays). I know where its getting its information from (similar to a SUMIFS) I just dont understand why I get the blank cells...or what else I need to do in order to extend the amount of information I need to put in (if I wanted to).

I also understand that arrays can slow down Excel...I did some experimenting the other day with your formula...thats why I was trying 200 instead of 15. Much thanks if you can help out once more.

Heres exactly what Im working with.
 

Attachments

Hi cpark,

The trick is simple. you need to adjust the ROW($A$1:$A$198) portion in accordance with the number of rows in your index() section. If there are 10 rows in INDEX()'s first argument, A1:A10 is sufficient, if there are 200 rows A1:A200 is sufficient. and so on. The entire SMALL(IF(IF(...))) construct provide the smallest row number that corresponds to your date criteria and that is be feed to the INDEX() function to lookup corresponding row.

Please find attached file. I feed something in row 92 and 160 and that is coming up in the table.
 

Attachments

Another "Awesome"! @Faseeh

I figured that much, but thats why I got back on here to see what I was doing wrong...I have no idea what I was doing wrong, but it confirmed I wasnt as crazy as I thought I was :) Much thanks again!
 
You must log in or register to reply here.
Back
Top