Excel 2003 SP3 on WinXPPro 2002 SP3

I have a spreadsheet A:AA
A Customer Name
B Customer Address 3/Street
C Customer Address 4/City/State
D Product Name - MARS
E Serial Number
F Price Plan Exp Date
G Monthly Lease Payment
H 12 Month Average Volume
I B&W Click Rate
J Allowance Included
K Overage
L Overage cost
M Total Current Monthly Cost
N Dist#
O Locn#
P Equipment Type
Q Canon B/W
R Xerox B/W
S OM B/W
T HP B/W
U Oce B/W
V Canon Color
W Xerox Color
X OM Color
Y HP Color
Z Oce Color
AA Sales Force Count

The spreadsheet is equipment centric, so there's one record for every unique
piece in col E totalling 145. I want to do a District centric analysis (Col
N), where I show total current cost vs projected cost per District grouped by
equip type in Col P based on criteria. Range Q:Z shows pricing from 5
different vendors for both color and BW. Based on which locations have a
sales force in col AA I only want to count color pricing for those, and BW
for the rest. One problem with Col AA is that I did an index(match) function
to assign sales count based on Dist# from another file, but I can't figure a
way to only placed the headcount on the first instance of the Dist#, so right
now since you can have more than one piece of equipment in the same district,
I'm duplicating count. If Dist#1234 has 3 pieces of equipment and 3 sales
people, each of the records has a 3 in ColAA totalling 9 if I pivot that data.

Thanks for bearing with me if you're still reading. Here's my dilemma, I
know a pivot can help me group by district and equipment type and show me
cost. However, how do I also automate selecting the right sales count per
district where it applies and only picking up cost for BW or color based on
whether or not the district has sales people. Of course, mgmt wants this
yesterday and this can take a day if not more to massage manually before I
can do my pivot. Or does anyone esle know of a better way to analyze the data
automatically. I don't mind adding additional columns to pick data based on
formulas I'm just drained at this point and out of ideas. Putting together
the spreadsheet already required a lot of querying and massaging since the
data was not all in one place or easy to match up.

I'll be happy to provide the spreadsheet if you tell me how you want to
receive it.

--
Hile

Try this

=INDIRECT("Belfast!B20")

Mike

"Hile" wrote:

Excel 2003 SP3 on WinXPPro 2002 SP3

I have a spreadsheet A:AA
A Customer Name
B Customer Address 3/Street
C Customer Address 4/City/State
D Product Name - MARS
E Serial Number
F Price Plan Exp Date
G Monthly Lease Payment
H 12 Month Average Volume
I B&W Click Rate
J Allowance Included
K Overage
L Overage cost
M Total Current Monthly Cost
N Dist#
O Locn#
P Equipment Type
Q Canon B/W
R Xerox B/W
S OM B/W
T HP B/W
U Oce B/W
V Canon Color
W Xerox Color
X OM Color
Y HP Color
Z Oce Color
AA Sales Force Count

The spreadsheet is equipment centric, so there's one record for every unique
piece in col E totalling 145. I want to do a District centric analysis (Col
N), where I show total current cost vs projected cost per District grouped by
equip type in Col P based on criteria. Range Q:Z shows pricing from 5
different vendors for both color and BW. Based on which locations have a
sales force in col AA I only want to count color pricing for those, and BW
for the rest. One problem with Col AA is that I did an index(match) function
to assign sales count based on Dist# from another file, but I can't figure a
way to only placed the headcount on the first instance of the Dist#, so right
now since you can have more than one piece of equipment in the same district,
I'm duplicating count. If Dist#1234 has 3 pieces of equipment and 3 sales
people, each of the records has a 3 in ColAA totalling 9 if I pivot that data.

Thanks for bearing with me if you're still reading. Here's my dilemma, I
know a pivot can help me group by district and equipment type and show me
cost. However, how do I also automate selecting the right sales count per
district where it applies and only picking up cost for BW or color based on
whether or not the district has sales people. Of course, mgmt wants this
yesterday and this can take a day if not more to massage manually before I
can do my pivot. Or does anyone esle know of a better way to analyze the data
automatically. I don't mind adding additional columns to pick data based on
formulas I'm just drained at this point and out of ideas. Putting together
the spreadsheet already required a lot of querying and massaging since the
data was not all in one place or easy to match up.

I'll be happy to provide the spreadsheet if you tell me how you want to
receive it.

--
Hile

I'm not sure I follow which of my questions you are addressing with this
function. I read its description since I've never used it and I'm still not
quite sure what this is solving. Am I using this to select pricing based on
sales count? If so, how does it know what to pick? Does it address placing
headcount on only one instance of the District #?
--
Hile


"Mike H" wrote:

Try this

=INDIRECT("Belfast!B20")

Mike

"Hile" wrote:

Excel 2003 SP3 on WinXPPro 2002 SP3

I have a spreadsheet A:AA
A Customer Name
B Customer Address 3/Street
C Customer Address 4/City/State
D Product Name - MARS
E Serial Number
F Price Plan Exp Date
G Monthly Lease Payment
H 12 Month Average Volume
I B&W Click Rate
J Allowance Included
K Overage
L Overage cost
M Total Current Monthly Cost
N Dist#
O Locn#
P Equipment Type
Q Canon B/W
R Xerox B/W
S OM B/W
T HP B/W
U Oce B/W
V Canon Color
W Xerox Color
X OM Color
Y HP Color
Z Oce Color
AA Sales Force Count

The spreadsheet is equipment centric, so there's one record for every unique
piece in col E totalling 145. I want to do a District centric analysis (Col
N), where I show total current cost vs projected cost per District grouped by
equip type in Col P based on criteria. Range Q:Z shows pricing from 5
different vendors for both color and BW. Based on which locations have a
sales force in col AA I only want to count color pricing for those, and BW
for the rest. One problem with Col AA is that I did an index(match) function
to assign sales count based on Dist# from another file, but I can't figure a
way to only placed the headcount on the first instance of the Dist#, so right
now since you can have more than one piece of equipment in the same district,
I'm duplicating count. If Dist#1234 has 3 pieces of equipment and 3 sales
people, each of the records has a 3 in ColAA totalling 9 if I pivot that data.

Thanks for bearing with me if you're still reading. Here's my dilemma, I
know a pivot can help me group by district and equipment type and show me
cost. However, how do I also automate selecting the right sales count per
district where it applies and only picking up cost for BW or color based on
whether or not the district has sales people. Of course, mgmt wants this
yesterday and this can take a day if not more to massage manually before I
can do my pivot. Or does anyone esle know of a better way to analyze the data
automatically. I don't mind adding additional columns to pick data based on
formulas I'm just drained at this point and out of ideas. Putting together
the spreadsheet already required a lot of querying and massaging since the
data was not all in one place or easy to match up.

I'll be happy to provide the spreadsheet if you tell me how you want to
receive it.

--
Hile

Hello Hile,

That's what I wrote my UDF Sfreq for:
http://www.sulprobil.com/html/sfreq.html

Regards,
Bernd