Dynamic Range Names and multicriteria look up 3

[johnturgoose]

With great thanks to skip for previous assistance I present a new and to me impossible challenge.

I have 2 tables. DATA and RECRUITMENT TOTALS.

DATA holds many columns of patient data. It has one table row 1 and 2 are headings. The 2 key columns are (Centre Code) and (Date of Randomisation).

RECRUITMENT TOTALS is a summary of the patients and reports on a Centre Code(a three digit number) basis.

What I am trying to do is have a column in RECRUITMENT TOTALS which shows the number of days elapsed since the most recent patient randomisation date.

so i need a function to look at RECRUITMENT TOTALS(a list that is growing (approx 3500 rows)) find the most recent patient randomisation date specific to that Centre code and then calculate the difference between that and today.

Any ideas gratefully accepted,

John Turgoose

 

[xlbo]

oooooh - yeh
Been a tough week so far - datawarehouse crash = Crazy Geoff

but cheers

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[SkipVought]

D*&%,

...and it's only mid way to the weekend!

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xlbo]

John - just seen a post I didn't see before - did my IF formula work for you ?? - I'm guessing it was just a post / timing issue

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[johnturgoose]

Geoff,

It worked fine thanks. I haven't tried to do the average calculation required as yet. Not sure how the empty cells will affect that. But thats is a problem for tmrw.

Cheers

John

 

[johnturgoose]

hey guys.

There seems to be a problem with the dynamic range names. They seem to exclude the bottom line of the data. It only seems to move down as the next line is added so always missing out the most recent randomisation.

Any clues

I have the 2 ranges as above

CNT CODE =OFFSET(DATA!$D$3,0,0,COUNTA(DATA!$D:$D)-2,1)
DATER =OFFSET(DATA!$E$3,0,0,COUNTA(DATA!$D:$D)-2,1)

Noting there are 2 header rows at the top of the list.

Thanks guys

John

 

[SkipVought]

First "CNT CODE" is not a valid name. "CNT_CODE" is!

I entered your offset formula and it works perfectly.

Are you sure that there is data in D1 & D2? If not, there is NOTHING TO COUNT in column D in these two rows!

ie in the formula COUNTA(DATA!$D:$D), you want the return value to be the number of DATA ROWS in the table. So IF there is are one or more rows of OTHER INFORMATION like HEADINGS in that column, you need to subtract that number from the count.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xlbo]

o you have data in BOTH your header rows or only 1 ??
If you only have data in 1 of 'em then change the -2 to -1

Generally, the -ve offset to the counta formula would be 1 less than the number of rows of extraneous data above the dataset you are counting - if that makes any sense. eg if you have 16 rows of data above the range then you would use -15 BUT if you had 15 rows of data and a blank line, you would use -14....

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[johnturgoose]

thanks guys. Once again I was the idiot. Although a number of coloumns have 2 rows of header data Coloumn D doesn't.

John.