Help with autopopulating worksheets with data from a main sheet (in same workbook)

I know his question has been asked before (many times), but I just can't seem to find the right formula/combination of formula to do what I need. I know tat I'm missing something, probably really simple, but for the life of me I can't figure it out.

I am trying to autopopulate worksheets with data contained in the first worksheet. Basically it's an annual calendar of meetings, all 12 months showing on sheet 2. I will then produce monthly sheets with further information (only pulling some information from the first sheet.

Whilst I've managed to get a straightforward formula to autopopulate a single entry, I can't get it to find the correct information to autopopulate the subsequent lines within that month!

I've attached my file for reference.

Many thanks in advance for any help!

Lynne

Originally Posted by dflak

First, I deleted the excess lines in the table on the FMPs Sheet. One of the advantages of tables is that they know how big they are and grow automatically and every time you add a row to a table it copies down formulas automatically. Since your first column is a formula, you can start entering with client forename. The formula for column A will fill in when you fill in columns E and F. I also replaced MONTH with a formula that calculates when you add the date.

Now comes the hairy part. There is an array formula (I have to look it up every time - I can almost sort of explain it) that gets the Nth occurrence of a value. I did this in a table on the Yearly Calendar Sheet in Columns U:AG. This tells me on what row the 1st, 2nd, 3rd, ... occurrence of the month is.

I then use this row number in conjunction with the index command in the monthly blocks. I did January, you can do the rest on your own.

The helper columns can be hidden.

Also see this wiki to learn a bit more about tables: http://www.utteraccess.com/wiki/Tables_in_Excel

Oh my goodness! Thank you so much. This is such a help. I've now copied the formula into the other months and hey presto the data all appears! Some months it seems to be pulling additional data though so I will check out the handy link you've provided to see if I can figure out how to resolve that. It's fantastic though!! I'd never in a million Sundays have been able to figure out that on my own.

You are an absolute gem

I wonder if you'd be able to assist again - sorry!

The calendar sheet appears to be pulling repeating data and some data which isn't relevant (ie wrong month). Any ideas on how I can resolve?

Other than this, it's doing exactly what I need it to do

I've attached a copy for reference.

Thanks in advance.

Lynne

I'm not understanding. All the entries in the source data ie sheet 1 (FPMs) are unique entries. For example, if I look up those who have a date to be set in February, there are 9 entries, however, on the Yearly Calendar, sheet 2, it is showing completed data for 10 entries in February ie it is pulling an additional entry from November.

Any ideas on how I can fix it?

Thank you so much in advance.

Lynne

It seems to be pulling erroneous data from the FPMs sheet where it hasn't found any data in the array table on the Yearly Calendar Sheet in Columns U:AG ie there is a zero return on the line (shown blank), however, the it is still populating information?

As you can tell I don't really understand arrays so I'm hoping I'm explaining this correctly. It may very well be something I've done, so feel free to shout at me

TIA

Regards

Lynne

Oh my word!! Or rather oh my Excel - you are fantastic! It's perfect.

If you were in front of me right now I'd hug you!!

Thank you, thank you, thank you!