Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Dynamic Range Names and multicriteria look up 3

Status
Not open for further replies.
johnturgoose

johnturgoose

Technical User
Jan 21, 2004
38
GB
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
 
Sort by date Sort by votes
Unfortunately, this isn't too simple - it requires either an array formula or a databse formula (DMAX)

My preference are array formulae (mainly 'cos I don't like setting up the criteria areas for database formulae) but you may need to use them depening on the number of Centre Codes you have

Lets say that the centre codes column is a named range called cCodes and your dates of randomisation are in a named range called DORs

Lets also assume that on your summary sheet, your centre codes are in col A, starting in row 3

in B3, enter
=TODAY()-MAX((cCodes=A3)*(DORs))
and instead of just hitting enter to enter the formula , use CTRL+SHIFT+ENTER
then just copy it down to the end of the Centre Codes

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
 
Upvote 0 Downvote
John,

Sort the Data table in DESCENDING sequence by Date of Randomization

Lookup the randomization date in the Data table for a given centre code
Code: 
=index(Date_of_Randomization,Match(LookupCentreCode,Centre_Code,0),1)
:)


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
ooooh - din't thinnk of that one - nice Skip. Could also use vlookup then - sort Ascending for this one and use

=today()-vlookup(CentreCode,DataRange,2,TRUE)

where Centrecode is your summary sheet centrecode cell ref and DataRange is the centrecodes and DORs from the data sheet

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
 
Upvote 0 Downvote
Many thanks guys. I am trying solution1 as people would moan if I sorted data in the DATA table.

It worked fine on a small test basis however when i graduated it to skips dynamic table name things go wrong and I get the #Name?

heres how I have done it.

DATA TABLE

Row 1&2 Header

Column D= Centre Code (there are approx 60 centre codes Xlbo)
Column E= Randomisation Date

so i have named 2 Ranges

CNTCODE =OFFSET(DATA!$D$3,0,0,COUNTD(DATA!$D:$D)-1,1)
DATER =OFFSET(DATA!$E$3,0,0,COUNTE(DATA!$E:$E)-1,1)

On the Summary Table RECRUITMENT TOTALS

row1 header

Column A is the Centre Code
Column L is the column I am entering the formula to calculate the date difference

So in L2 I have entered =TODAY()-MAX((CNTCODE=A2)*(DATER)) and done the CTRL+SHIFT+ENTER For the array

As I said unfortunately this brings up #NAME?

Am I doing something stupid?

Many thanks once again guys i think i would have lost all my hair trying to figure this one out.

John
 
Upvote 0 Downvote
THEN...

you need to subtract 2
Code: 
COUNTA(DATA!$D:$D)-2
AND...

the formula is COUNTA which counts every occurrence of data in the column.

BTW COUNTA only works if EVERY cell in the range has data.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Don't think there is any more I can add to that - the only thing I'd do is make sure the ranges stay the same size by doing the COUNTA on the same column for both of them:
CNTCODE =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)


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
 
Upvote 0 Downvote
Good point, xlbo!

If, perchance, you have Centre Codes WITHOUT a date, then do the counta on the Centre Code column

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
It's just something I always do - If I have a key that I know will always be populted, I do ALL my counta's on that column - just in case....

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
 
Upvote 0 Downvote
I am speechless. Thanks so much guys. I have retained my hair. A beautiful solution.

John
 
Upvote 0 Downvote
oh one final question. I have copied the formula down all 60 centres. It works great. However some of the centres have no patients randomised as yet and therefore show 38028, presumably the days since 01/01/1900. IS there any way to have some kind of conditional format that blanks out these results?

Thanks again

John
 
Upvote 0 Downvote
If (the Difference is greater than some threshhold number of days that would be considered out of bounds) then
whatever you want to happen in that case.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
=if(TODAY()-MAX((CNTCODE=A2)*(DATER))=today(),"",TODAY()-MAX((CNTCODE=A2)*(DATER)))

Array entered with CTRL+SHIFT+ENTER as before

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
 
Upvote 0 Downvote
Perhaps I worded that wrong. Its not really formatting I want. Basically they want to know an average number of days since last randomisation for the centres. So I could change text colour to make it dissapear but it would still be counted. Basically I need something to say if over eg. 10000 days then =0 while retaining the formula should a patient be randomised in the future.

Or would it be easier to just use conditional formatting to blank out the unwanted text and then worry about the large numbers during the average calculation. Perhaps with some sort of (SUMIF(rangename)<10000)/(COUNTIF(rangename)<10000)

Perhaps I have answered my own question.

Thanks

John
 
Upvote 0 Downvote
thanuveerrrymuch - and goodnight :)

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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Locked
  • Question
Word - Mail merge file names 1
Replies
2
Views
1K
Andrzejek
Andrzejek
N1GHTEYES
  • Locked
  • Question
Count range overlap cells
Replies
3
Views
671
N1GHTEYES
N1GHTEYES
N1GHTEYES
  • Locked
  • Question
Count areas within a range 1
Replies
14
Views
1K
SkipVought
S
PSchubert
  • Locked
  • Question
Excel - SUMIFS() works with one range, but doesn't with another nearly identical range 2
Replies
6
Views
525
Andrzejek
Andrzejek
Andrzejek
  • Locked
  • Question
Formula with & and a single quote 2
Replies
4
Views
1K
OzFoxy
OzFoxy

Part and Inventory Search

Sponsor

Back
Top