mekus31 said:
Thanks Max, appreciate the formula.
welcome. glad that got you going here.
I'm now in the process of figuring out what all the things are that you
used. Match, Offset, etc. so I may come back at you w/ a couple questions if
that's ok. It's important for me to figure out what you did so I can repeat it with
other options.
Usually it's best to put this kind of request in as a new posting.
But I'll try explain it here for you ..
The core challenging part here is to be able grab the correct column from
the reference table "as-is" (ie the table in sheet: Critical Care Endors) and
then use this in a INDEX(Correct Col,Correct Row) construct to return the
required cost value within the Correct Col.
The Correct Col is returned via using: OFFSET(RefRange,,Col param,)
A study of the ref table reveals fortunately that we can use the col labels
in the first row, ie that there is regularity, a sort of pattern which would
simplify things. The key pattern here would be that the "Male N Tobacco",
"Male Y Tobacco", "Female N Tobacco" "divider" cols are consistently named in
themselves, and importantly, the "Face Amt" col labels which are adjacent to
each of the divider cols are also consistently structured in name, number and
placement. There's 10 of these "Face Amt" col labels to the right of each
divider col, eg: 5,000, 10,000, ... 50,000.
OFFSET(RefRange,,Col param,)
where RefRange = 'Critical Care Endors'!$A:$A
will return a corresponding col range defined by the Col Param
If Col param is say = 1, OFFSET('Critical Care Endors'!$A:$A,,2,) returns
the col range 'Critical Care Endors'!$B:$B, ie 1 col to the right of the
RefRange.
Col param =
MATCH(concat string,1st row of reference table,0)+ an arithmetic adjustment
where
concat string
= IF(B7="M",B7&"ale "&F5&" Tobacco",IF(B7="F",B7&"emale "&F5&" Tobacco",""))
The concat string IF formula essentially composes the various DV inputs in
"Client Info Sheet" for an exact match with one of the divider cols, eg:"Male
Y Tobacco", "Female N Tobacco" etc within the 1st row of the reference table,
ie: 'Critical Care Endors'!$1:$1.
The number returned by the MATCH() is the position of the label. The
arithmetic adjustment part: -1+F16/5000 applied then adjusts this label
position number returned by the MATCH() to yield the final, correct number
for the Col param depending on the Face Amt DV input (the input in F16).
With the Correct Col grabbed/returned by the OFFSET(RefRange,,Col param,),
INDEX(Correct Col,Correct Row)
where Correct Row = MATCH(B5,'Critical Care Endors'!$A:$A,0))
then simply matches the Age DV input in B5 with the Issue Age col: 'Critical
Care Endors'!$A:$A to return the correct row, and hence the required
intersection point value (the Cost)
Finally, the front IF part of it:
=IF(COUNTA(B5,B7,F5,F16)<4,"", ...)
simply ensures that all 4 the DV inputs in B5,B7,F5,F16 are selected before
proceeding to calc INDEX(Correct Col,Correct Row). If any one DV is still
empty (ie cleared with the Delete key, not selected yet by user) then you
have a neat blank:"" appearing in the formula cell.
Hope the above helps to explain it ok for your easy cross application
elsewhere.
