Bill --
If I understand your question, you want to copy this CHOOSE formula down a
bunch of rows, and have it use the information from that row when
appropriate, but pull information from the lookup table you created (which
doesn't move).
To do that, you'll need to understand absolute and relative references.
Read about it in Excel help. Basically, as you copy a formla, all the cell
references track along RELATIVE to the movement. So if your initial cell
makes reference to the cell one column to the left, wherever you copy the
formula will look to the cell one column to the left of THAT. The bigger
problem is to make the references not track along, remaining ABSOLUTEly fixed
on one location. To do that, put a dollar sign ($) in front of the letter
and number. So the reference A1 in a formula would track along as you copied
the cell elsewhere, while the reference $A$1 would always remain fixed,
aiming at $A$1 forever.
Is that what you wanted, I hope/
"bill ch" wrote:
pdberger,
The letter represents the first digit out of a three digit health insurance
payor code. So, I set-up the lookup table to assign an index number. The
CHOOSE is excellent and I have it set to choose a named formula based on the
index #. My only problem now is I have to paste the choose formula in a
column down multiple rows. How do I change the original formulas so, the row
that it is looking at '31' will correspond to the row that the choose formula
is in.
Hope this makes sense -Thanks
"pdberger" wrote:
Bill --
I think you ought to look at the CHOOSE function. The first argument is an
index number ('n'), and then there are 'n' different actions. CHOOSE picks
the nth action. So then the problem is that you've already set it up with
letters rather than index numbers (1, 2, 3, 4, 5). I 'd think you have two
choices, either find-and-replace all the letters with numbers, or create a
little lookup table where it looks up the index number based on the letter
you assigned.
HTH
"bill ch" wrote:
I would like to add multiple IF / VLOOKUPS to the formula below that
currently works. This site: http://www.cpearson.com/excel/nested.htm helps
but, I was planning on using the named formula to lookup '2006 IP Payer Mix
on Settled' vs '2005 IP Payer Mix on Settled'. Currently I have multiple
columns that search based on the IF(B31="*",) with "*" equalling various
letters. IS there a way I can combine these with an OR statement or am I
heading down the wrong path. Suggestions welcome, thanks. Please keep in
mind the file is large 28MB.
=IF(AC31=J31,J31*0.5,IF(D31=" ",VLOOKUP(C31,'2006 IP Payer Mix on
Settled'!H:I,2,FALSE)*K31,IF(B31="A",VLOOKUP(D31,' 2006 IP
Template'!B:C,2,FALSE),IF(B31="B",VLOOKUP(D31,'200 6 IP
Template'!B:G,6,FALSE),IF(B31="h",VLOOKUP(C31,'200 6 IP Payer Mix on
Settled'!H:I,2,FALSE)*K31,IF(B31="d",VLOOKUP(D31,' 2006 IP
Template'!B:Y,10,FALSE),IF(C31="D05",VLOOKUP(D31,' 2006 IP
Template'!B:Y,11,FALSE))))))))