Using VLOOKUP from within a pivot table

[vpoko]

Hi everyone,

I'm new to this forum and apologize that I haven't had time to read the
FAQ's. I'm working on a deadline and need some help ASAP. Now that I'm a
member, though, I will read through the FAQ's because I anticipate I'll
be needing more of your help in the near future. I'm pretty sure this
isn't the type of question that's been beaten to death in any case...

I'm working a with a PivotTable that pulls data from an ODBC
datasource. The table is a list of sales agents on the vertical axis
and a the number of purchases made by clients on the horrizontal axis.
The number in the "data" section of the table is the number of
customers each sales agent has that falls within the certain "number of
purchases bucket".

For example:


Code:
--------------------

1 2 3 4 5
BOB 15 12 7 9 10
BILL 33 10 8 4 2
JOHN 15 4 3 4 1

--------------------



Now I need to multiply the number in the data section of the table (15,
12, 7, etc) by a certain dollar value to give the different customer
types a weight. For example, 1 purchase = $2000, 2 purchases = $3500, 3
purchases = $4200, 4+ purchases = $6000.

What I would normally do (if I wasn't using a PivotTable) is create a
VLOOKUP table and use that to get my figures for each agent/#of
purchases combination. The problem is you can't refer to external
tables (such as my VLOOKUP table) from within a PivotTable's calculated
field.

I can accomplish the same thing by downloading my data, doing the
calculations, and then creating the PivotTable from the results, but
it's not nearly as easy to refresh the info from the datasource. Can
anyone help?

 

[vpoko]

Well, I was able to get it to work using nested if statements. Luckily
only had four values I needed to weigh, otherwise it could have bee
ugly. Does Excel have anything similar to C/C++'s switch/cas
statements as an alternative to nested if's

 

[aaron.kempf]

vba has a select/case statememt

select case OfficeProgramBeingUser
case "excel"
debug.print "lose the training wheels"
case "access"
debug.print "YAY welcome to the worlds most powerful reporting tool."
case else
debug.print "you really need help"
end select