The Formula below works, but feels quite sloppy, and I'm sure it can be
accomplished in a much cleaner fashion. In addition, I attempted to add 1
more condition, but realized that I cannot add yet an 8th condition without
receiving an error message (I believe the limit is 7). Any suggestions to
clean the formula below up, and add the following 8th condition:

=IF(A5="PD Reports Not Provided","$100.00"

=IF(A5="Administrative Discharge Fee","$150.00",IF(A5="Medication Non
Compliance Fee","$50.00",IF(A5="Taxi Fees","$10.00",IF(A5="Phase Program No
Show Fee","$150.00",IF(A5="Record(s) Not Provided Fee","$100.00",IF(A5="BC
Not Provided Fee","$50.00",IF(A5="File Re-Opening Fee","$150.00",""))))))))

Setup a lookup table and use VLOOKUP function


"Danny Boy" wrote:

The Formula below works, but feels quite sloppy, and I'm sure it can be
accomplished in a much cleaner fashion. In addition, I attempted to add 1
more condition, but realized that I cannot add yet an 8th condition without
receiving an error message (I believe the limit is 7). Any suggestions to
clean the formula below up, and add the following 8th condition:

=IF(A5="PD Reports Not Provided","$100.00"

=IF(A5="Administrative Discharge Fee","$150.00",IF(A5="Medication Non
Compliance Fee","$50.00",IF(A5="Taxi Fees","$10.00",IF(A5="Phase Program No
Show Fee","$150.00",IF(A5="Record(s) Not Provided Fee","$100.00",IF(A5="BC
Not Provided Fee","$50.00",IF(A5="File Re-Opening Fee","$150.00",""))))))))

I'm somewhat of an Excel "newbie", so attempting to setup a lookup table and
use VLOOKUP function would be difficult for me. Thanks for the suggestion
however.

"Teethless mama" wrote:

Setup a lookup table and use VLOOKUP function


"Danny Boy" wrote:

The Formula below works, but feels quite sloppy, and I'm sure it can be
accomplished in a much cleaner fashion. In addition, I attempted to add 1
more condition, but realized that I cannot add yet an 8th condition without
receiving an error message (I believe the limit is 7). Any suggestions to
clean the formula below up, and add the following 8th condition:

=IF(A5="PD Reports Not Provided","$100.00"

=IF(A5="Administrative Discharge Fee","$150.00",IF(A5="Medication Non
Compliance Fee","$50.00",IF(A5="Taxi Fees","$10.00",IF(A5="Phase Program No
Show Fee","$150.00",IF(A5="Record(s) Not Provided Fee","$100.00",IF(A5="BC
Not Provided Fee","$50.00",IF(A5="File Re-Opening Fee","$150.00",""))))))))

Lookup tables are a lot easier than the If statement you created. Don't be
so hard on yourself. Millions of people have used Vlookup successfully. You
can too.

You should also learn the difference between text and numbers. Using
"$150.00" makes this text, which can't be used in other formulas. You should
use 150.00, and format it for currency.

Regards,
Fred

"Danny Boy" wrote in message
...
I'm somewhat of an Excel "newbie", so attempting to setup a lookup table
and
use VLOOKUP function would be difficult for me. Thanks for the suggestion
however.

"Teethless mama" wrote:

Setup a lookup table and use VLOOKUP function


"Danny Boy" wrote:

The Formula below works, but feels quite sloppy, and I'm sure it can be
accomplished in a much cleaner fashion. In addition, I attempted to add
1
more condition, but realized that I cannot add yet an 8th condition
without
receiving an error message (I believe the limit is 7). Any suggestions
to
clean the formula below up, and add the following 8th condition:

=IF(A5="PD Reports Not Provided","$100.00"

=IF(A5="Administrative Discharge Fee","$150.00",IF(A5="Medication Non
Compliance Fee","$50.00",IF(A5="Taxi Fees","$10.00",IF(A5="Phase
Program No
Show Fee","$150.00",IF(A5="Record(s) Not Provided
Fee","$100.00",IF(A5="BC
Not Provided Fee","$50.00",IF(A5="File Re-Opening
Fee","$150.00",""))))))))

attempting to setup a lookup table and use
VLOOKUP function would be difficult for me.

No it wouldn't. It's a lot easier than you think.

Enter these strings in the range AA1:AA7 -

Administrative Discharge Fee
Medication Non Compliance Fee
Taxi Fees
Phase Program No Show Fee
Record(s) Not Provided Fee
BC Not Provided Fee
File Re-Opening Fee

Enter the corresponding numeric values in the range AB1:AB7 -

150
50
10
150
100
50
150

Now you have a lookup table!

=VLOOKUP(A5,AA1:AB7,2,0)

You don't even need to use a "lookup" function in this case:

=SUMIF(AA1:AA7,A5,AB1:AB7)

--
Biff
Microsoft Excel MVP


"Danny Boy" wrote in message
...
I'm somewhat of an Excel "newbie", so attempting to setup a lookup table
and
use VLOOKUP function would be difficult for me. Thanks for the suggestion
however.

"Teethless mama" wrote:

Setup a lookup table and use VLOOKUP function


"Danny Boy" wrote:

The Formula below works, but feels quite sloppy, and I'm sure it can be
accomplished in a much cleaner fashion. In addition, I attempted to add
1
more condition, but realized that I cannot add yet an 8th condition
without
receiving an error message (I believe the limit is 7). Any suggestions
to
clean the formula below up, and add the following 8th condition:

=IF(A5="PD Reports Not Provided","$100.00"

=IF(A5="Administrative Discharge Fee","$150.00",IF(A5="Medication Non
Compliance Fee","$50.00",IF(A5="Taxi Fees","$10.00",IF(A5="Phase
Program No
Show Fee","$150.00",IF(A5="Record(s) Not Provided
Fee","$100.00",IF(A5="BC
Not Provided Fee","$50.00",IF(A5="File Re-Opening
Fee","$150.00",""))))))))

Wow, easier than I thought. Thanks Biff and everyone else.

TGIF!

Dan

"T. Valko" wrote:

attempting to setup a lookup table and use
VLOOKUP function would be difficult for me.

No it wouldn't. It's a lot easier than you think.

Enter these strings in the range AA1:AA7 -

Administrative Discharge Fee
Medication Non Compliance Fee
Taxi Fees
Phase Program No Show Fee
Record(s) Not Provided Fee
BC Not Provided Fee
File Re-Opening Fee

Enter the corresponding numeric values in the range AB1:AB7 -

150
50
10
150
100
50
150

Now you have a lookup table!

=VLOOKUP(A5,AA1:AB7,2,0)

You don't even need to use a "lookup" function in this case:

=SUMIF(AA1:AA7,A5,AB1:AB7)

--
Biff
Microsoft Excel MVP


"Danny Boy" wrote in message
...
I'm somewhat of an Excel "newbie", so attempting to setup a lookup table
and
use VLOOKUP function would be difficult for me. Thanks for the suggestion
however.

"Teethless mama" wrote:

Setup a lookup table and use VLOOKUP function


"Danny Boy" wrote:

The Formula below works, but feels quite sloppy, and I'm sure it can be
accomplished in a much cleaner fashion. In addition, I attempted to add
1
more condition, but realized that I cannot add yet an 8th condition
without
receiving an error message (I believe the limit is 7). Any suggestions
to
clean the formula below up, and add the following 8th condition:

=IF(A5="PD Reports Not Provided","$100.00"

=IF(A5="Administrative Discharge Fee","$150.00",IF(A5="Medication Non
Compliance Fee","$50.00",IF(A5="Taxi Fees","$10.00",IF(A5="Phase
Program No
Show Fee","$150.00",IF(A5="Record(s) Not Provided
Fee","$100.00",IF(A5="BC
Not Provided Fee","$50.00",IF(A5="File Re-Opening
Fee","$150.00",""))))))))


.

Hi Biff!

I tried your suggestion, and I'm still a bit confused (sorry)! My first
question is about your formula.

=VLOOKUP(A5,AA1:AB7,2,0)

1-What does cell A5 reference? Given that the strings and corresponding
numbers are in AA1-AA7 and AB1-AB7.

2-In what cell do I put the VLOOKUP formula so that it runs?

What I'm trying to (as you may have guessed), is to use a drop down menu
with the various charge types (Administrative Discharge Fee, Medication Non
Compliance Fee, Taxi Fees, etc) in one column, and then have the charges
themselves appear in the corresponding columns. If the drop down menu is
blank, than no charges would appear.

Again, Thank You

"T. Valko" wrote:

attempting to setup a lookup table and use
VLOOKUP function would be difficult for me.

No it wouldn't. It's a lot easier than you think.

Enter these strings in the range AA1:AA7 -

Phase Program No Show Fee
Record(s) Not Provided Fee
BC Not Provided Fee
File Re-Opening Fee

Enter the corresponding numeric values in the range AB1:AB7 -

150
50
10
150
100
50
150

Now you have a lookup table!

=VLOOKUP(A5,AA1:AB7,2,0)

You don't even need to use a "lookup" function in this case:

=SUMIF(AA1:AA7,A5,AB1:AB7)

--
Biff
Microsoft Excel MVP


"Danny Boy" wrote in message
...
I'm somewhat of an Excel "newbie", so attempting to setup a lookup table
and
use VLOOKUP function would be difficult for me. Thanks for the suggestion
however.

"Teethless mama" wrote:

Setup a lookup table and use VLOOKUP function


"Danny Boy" wrote:

The Formula below works, but feels quite sloppy, and I'm sure it can be
accomplished in a much cleaner fashion. In addition, I attempted to add
1
more condition, but realized that I cannot add yet an 8th condition
without
receiving an error message (I believe the limit is 7). Any suggestions
to
clean the formula below up, and add the following 8th condition:

=IF(A5="PD Reports Not Provided","$100.00"

=IF(A5="Administrative Discharge Fee","$150.00",IF(A5="Medication Non
Compliance Fee","$50.00",IF(A5="Taxi Fees","$10.00",IF(A5="Phase
Program No
Show Fee","$150.00",IF(A5="Record(s) Not Provided
Fee","$100.00",IF(A5="BC
Not Provided Fee","$50.00",IF(A5="File Re-Opening
Fee","$150.00",""))))))))


.

VLOOKUP

=VLOOKUP(lookup item,lookup table,column index number,true or false)

A5 would be the cell with the chosen item


Gord Dibben MS Excel MVP


On Fri, 19 Mar 2010 05:38:05 -0700, Danny Boy
wrote:

Hi Biff!

I tried your suggestion, and I'm still a bit confused (sorry)! My first
question is about your formula.

=VLOOKUP(A5,AA1:AB7,2,0)

1-What does cell A5 reference? Given that the strings and corresponding
numbers are in AA1-AA7 and AB1-AB7.

2-In what cell do I put the VLOOKUP formula so that it runs?

What I'm trying to (as you may have guessed), is to use a drop down menu
with the various charge types (Administrative Discharge Fee, Medication Non
Compliance Fee, Taxi Fees, etc) in one column, and then have the charges
themselves appear in the corresponding columns. If the drop down menu is
blank, than no charges would appear.

Again, Thank You

"T. Valko" wrote:

attempting to setup a lookup table and use
VLOOKUP function would be difficult for me.

No it wouldn't. It's a lot easier than you think.

Enter these strings in the range AA1:AA7 -

Phase Program No Show Fee
Record(s) Not Provided Fee
BC Not Provided Fee
File Re-Opening Fee

Enter the corresponding numeric values in the range AB1:AB7 -

150
50
10
150
100
50
150

Now you have a lookup table!

=VLOOKUP(A5,AA1:AB7,2,0)

You don't even need to use a "lookup" function in this case:

=SUMIF(AA1:AA7,A5,AB1:AB7)

--
Biff
Microsoft Excel MVP


"Danny Boy" wrote in message
...
I'm somewhat of an Excel "newbie", so attempting to setup a lookup table
and
use VLOOKUP function would be difficult for me. Thanks for the suggestion
however.

"Teethless mama" wrote:

Setup a lookup table and use VLOOKUP function


"Danny Boy" wrote:

The Formula below works, but feels quite sloppy, and I'm sure it can be
accomplished in a much cleaner fashion. In addition, I attempted to add
1
more condition, but realized that I cannot add yet an 8th condition
without
receiving an error message (I believe the limit is 7). Any suggestions
to
clean the formula below up, and add the following 8th condition:

=IF(A5="PD Reports Not Provided","$100.00"

=IF(A5="Administrative Discharge Fee","$150.00",IF(A5="Medication Non
Compliance Fee","$50.00",IF(A5="Taxi Fees","$10.00",IF(A5="Phase
Program No
Show Fee","$150.00",IF(A5="Record(s) Not Provided
Fee","$100.00",IF(A5="BC
Not Provided Fee","$50.00",IF(A5="File Re-Opening
Fee","$150.00",""))))))))


.