PDA

View Full Version : Solved: lookup and populate



CCkfm2000
08-16-2012, 06:50 AM
I have 3 worksheets Tabs Page1, Page2 and Data.

I need some help with a formula, this is what I’ve got so far.

This Formula is in the Data sheet in column A

=IF(E2<>"full",IF(DATE(YEAR(Page1!$E$24),MONTH(Page1!$E$24),DAY(Page1!$E$24)+14)<=DATE(YEAR(Data!B2),MONTH(Data!B2),DAY(Data!B2)),MAX($A$1:A1)+1,0),0)

The idea is that when I enter a date in cell E24 on Page1 the formula will look at the data and populate Page2 with vlookups.

This works well but the problem is not to have duplicate workshops in Page2.

The data sheet will be populate as an when new workshops are added to the spreadsheet.

I have attached an example of the spreadsheet.

Thanks for your help

Bob Phillips
08-16-2012, 07:07 AM
How about this

=IF(OR(E2="full",Page1!$E$24+14>Data!B2,COUNTIFS($A$1:$A1,">0",$C$1:$C1,$C2)>0),0,MAX($A$1:A1)+1)

CCkfm2000
08-20-2012, 11:38 PM
thanks xld, looks good.

thanks again.