software

I would like to add multiple IF / VLOOKUPS to the formula below that
currently works. This site: 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=quot;*quot;,) with quot;*quot; 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=quot; quot;,VLOOKUP(C31,'2006 IP Payer Mix on
Settled'!H:I,2,FALSE)*K31,IF(B31=quot;Aquot;,VLOOKUP(D31,' 2006 IP
Template'!B:C,2,FALSE),IF(B31=quot;Bquot;,VLOOKUP(D31,'200 6 IP
Template'!B:G,6,FALSE),IF(B31=quot;hquot;,VLOOKUP(C31,'200 6 IP Payer Mix on
Settled'!H:I,2,FALSE)*K31,IF(B31=quot;dquot;,VLOOKUP(D31,' 2006 IP
Template'!B:Y,10,FALSE),IF(C31=quot;D05quot;,VLOOKUP(D31,' 2006 IP
Template'!B:Y,11,FALSE))))))))

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

quot;bill chquot; wrote:

gt; I would like to add multiple IF / VLOOKUPS to the formula below that
gt; currently works. This site: www.cpearson.com/excel/nested.htm helps
gt; but, I was planning on using the named formula to lookup '2006 IP Payer Mix
gt; on Settled' vs '2005 IP Payer Mix on Settled'. Currently I have multiple
gt; columns that search based on the IF(B31=quot;*quot;,) with quot;*quot; equalling various
gt; letters. IS there a way I can combine these with an OR statement or am I
gt; heading down the wrong path. Suggestions welcome, thanks. Please keep in
gt; mind the file is large 28MB.
gt;
gt;
gt; =IF(AC31=J31,J31*0.5,IF(D31=quot; quot;,VLOOKUP(C31,'2006 IP Payer Mix on
gt; Settled'!H:I,2,FALSE)*K31,IF(B31=quot;Aquot;,VLOOKUP(D31,' 2006 IP
gt; Template'!B:C,2,FALSE),IF(B31=quot;Bquot;,VLOOKUP(D31,'200 6 IP
gt; Template'!B:G,6,FALSE),IF(B31=quot;hquot;,VLOOKUP(C31,'200 6 IP Payer Mix on
gt; Settled'!H:I,2,FALSE)*K31,IF(B31=quot;dquot;,VLOOKUP(D31,' 2006 IP
gt; Template'!B:Y,10,FALSE),IF(C31=quot;D05quot;,VLOOKUP(D31,' 2006 IP
gt; Template'!B:Y,11,FALSE))))))))

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

quot;pdbergerquot; wrote:

gt; Bill --
gt;
gt; I think you ought to look at the CHOOSE function. The first argument is an
gt; index number ('n'), and then there are 'n' different actions. CHOOSE picks
gt; the nth action. So then the problem is that you've already set it up with
gt; letters rather than index numbers (1, 2, 3, 4, 5). I 'd think you have two
gt; choices, either find-and-replace all the letters with numbers, or create a
gt; little lookup table where it looks up the index number based on the letter
gt; you assigned.
gt;
gt; HTH
gt;
gt; quot;bill chquot; wrote:
gt;
gt; gt; I would like to add multiple IF / VLOOKUPS to the formula below that
gt; gt; currently works. This site: www.cpearson.com/excel/nested.htm helps
gt; gt; but, I was planning on using the named formula to lookup '2006 IP Payer Mix
gt; gt; on Settled' vs '2005 IP Payer Mix on Settled'. Currently I have multiple
gt; gt; columns that search based on the IF(B31=quot;*quot;,) with quot;*quot; equalling various
gt; gt; letters. IS there a way I can combine these with an OR statement or am I
gt; gt; heading down the wrong path. Suggestions welcome, thanks. Please keep in
gt; gt; mind the file is large 28MB.
gt; gt;
gt; gt;
gt; gt; =IF(AC31=J31,J31*0.5,IF(D31=quot; quot;,VLOOKUP(C31,'2006 IP Payer Mix on
gt; gt; Settled'!H:I,2,FALSE)*K31,IF(B31=quot;Aquot;,VLOOKUP(D31,' 2006 IP
gt; gt; Template'!B:C,2,FALSE),IF(B31=quot;Bquot;,VLOOKUP(D31,'200 6 IP
gt; gt; Template'!B:G,6,FALSE),IF(B31=quot;hquot;,VLOOKUP(C31,'200 6 IP Payer Mix on
gt; gt; Settled'!H:I,2,FALSE)*K31,IF(B31=quot;dquot;,VLOOKUP(D31,' 2006 IP
gt; gt; Template'!B:Y,10,FALSE),IF(C31=quot;D05quot;,VLOOKUP(D31,' 2006 IP
gt; gt; Template'!B:Y,11,FALSE))))))))

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/

quot;bill chquot; wrote:

gt; pdberger,
gt; The letter represents the first digit out of a three digit health insurance
gt; payor code. So, I set-up the lookup table to assign an index number. The
gt; CHOOSE is excellent and I have it set to choose a named formula based on the
gt; index #. My only problem now is I have to paste the choose formula in a
gt; column down multiple rows. How do I change the original formulas so, the row
gt; that it is looking at '31' will correspond to the row that the choose formula
gt; is in.
gt; Hope this makes sense -Thanks
gt;
gt; quot;pdbergerquot; wrote:
gt;
gt; gt; Bill --
gt; gt;
gt; gt; I think you ought to look at the CHOOSE function. The first argument is an
gt; gt; index number ('n'), and then there are 'n' different actions. CHOOSE picks
gt; gt; the nth action. So then the problem is that you've already set it up with
gt; gt; letters rather than index numbers (1, 2, 3, 4, 5). I 'd think you have two
gt; gt; choices, either find-and-replace all the letters with numbers, or create a
gt; gt; little lookup table where it looks up the index number based on the letter
gt; gt; you assigned.
gt; gt;
gt; gt; HTH
gt; gt;
gt; gt; quot;bill chquot; wrote:
gt; gt;
gt; gt; gt; I would like to add multiple IF / VLOOKUPS to the formula below that
gt; gt; gt; currently works. This site: www.cpearson.com/excel/nested.htm helps
gt; gt; gt; but, I was planning on using the named formula to lookup '2006 IP Payer Mix
gt; gt; gt; on Settled' vs '2005 IP Payer Mix on Settled'. Currently I have multiple
gt; gt; gt; columns that search based on the IF(B31=quot;*quot;,) with quot;*quot; equalling various
gt; gt; gt; letters. IS there a way I can combine these with an OR statement or am I
gt; gt; gt; heading down the wrong path. Suggestions welcome, thanks. Please keep in
gt; gt; gt; mind the file is large 28MB.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; =IF(AC31=J31,J31*0.5,IF(D31=quot; quot;,VLOOKUP(C31,'2006 IP Payer Mix on
gt; gt; gt; Settled'!H:I,2,FALSE)*K31,IF(B31=quot;Aquot;,VLOOKUP(D31,' 2006 IP
gt; gt; gt; Template'!B:C,2,FALSE),IF(B31=quot;Bquot;,VLOOKUP(D31,'200 6 IP
gt; gt; gt; Template'!B:G,6,FALSE),IF(B31=quot;hquot;,VLOOKUP(C31,'200 6 IP Payer Mix on
gt; gt; gt; Settled'!H:I,2,FALSE)*K31,IF(B31=quot;dquot;,VLOOKUP(D31,' 2006 IP
gt; gt; gt; Template'!B:Y,10,FALSE),IF(C31=quot;D05quot;,VLOOKUP(D31,' 2006 IP
gt; gt; gt; Template'!B:Y,11,FALSE))))))))

pdberger,
Sorry, I should've used an example to explain this. The index numbers are
in column B, it is set-up as a vlookup to the index table I created.
Example: now I have a CHOOSE formula of
'=IF(AC31=J31,J31*0.5,CHOOSE(B31,(quot;Aquot;,quot;Bquot;,quot;Hquot;,quot;Dquot;) ))
When I paste this down the rows it will do a relative reference for AC31,
J31 and B31 by changing them to AC32, J32, and B32 and so on. My problem is
it won't make the named formula referencesquot;Aquot;,quot;Bquot;,quot;Hquot;,quot;Dquot;) relative
references. The Named Formula's a
quot;Aquot; =VLOOKUP(D31,'2006 IP Template'!B:C,2,FALSE)
quot;Bquot; =VLOOKUP(D31,'2006 IP Template'!B:G,6,FALSE)
quot;Hquot; =VLOOKUP(C31,'2006 IP Payer Mix on Settled'!H:I,2,FALSE)*K31
quot;Dquot; =VLOOKUP(D31,'2006 IP Template'!B:Y,10,FALSE)
The CHOOSE formula will eventually use almost all 29 index values so, it
will be too big for me to paste all the formulas into the CHOOSE formula
=exceeds the cell limit. Thus, I've set them up as named formulas for the
CHOOSE formula to look-up. The ultimate goal is: How can I get these named
formulas to also be relative references?? When in row 32 I need these to
look at D32, C32 and K32.
Thanks

quot;pdbergerquot; wrote:

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

Bill --
It's getting too complicated for me to follow w/o actually seeing it, so I'm
going to have to bow out. I'd offer trying to move the CHOOSE function
outside of the named formula -- that's what I anticipated when I suggested
the function.

Good luck

quot;bill chquot; wrote:

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