How to have a number in a cell, but show a text

[maskinhenrik]

I,ve a worksheet, where names represent values. it have to show the names, but i, ve would like to use the names to sum up values... Does anybody knows how to ??

[sen_edp]

hello there

Give an example of your data please,
and give more details .

[maskinhenrik]

i have cells with names of several custumers in a time management plan. each custumer represent a value, a sale. to detemine the sum of the sale i now have to manual change every sum if there are any changes.
It would be great just to drag the cell to another time period, and then this weeks sale updated itself automatic.

[ This Message was edited by: maskinhenrik on 2002-05-21 04:49 ]

[PDuPre']

On 2002-05-21 03:58, maskinhenrik wrote:
I,ve a worksheet, where names represent values. it have to show the names, but i, ve would like to use the names to sum up values... Does anybody knows how to ??

I see two possible solutions.
First, use a vlookup table to substitute your names for values. For instance, Fred=1, Ginger=2, Rocky=3, Bulwinkle=4. What does Fred and Ginger equal?
Put the table (above) in the range X1:Y4.
A1 equals Fred
A2 equals Ginger
B1 equals =vlookup(A1,$X$1:$Y$4,2)
B2 equals =vlookup(A2,$X$1:$Y$4,2)
Now you can add the B column.
-------------------------------------
There's probably a way to do this using a CSE (CTRL-Shift-Enter) formula. Otherwise known as an array formula. It's too early in the morning for me.
-------------------------------------
I'll alter this to meet your latest post.


Philip

[sen_edp]

hello there

I think you can use pivot table, for this
it is much easier than formulas, and in addition you can get sums for periods and by customer.

[PDuPre']

OK. You could also use a DSUM formula. This would require having a table of numbers such that each customer has a name in a column. Another column in the table would have the week. A third column would have the sale.
To setup a DSUM formula you need to have a title for each column of the table. Here we would use Name, Week, and Sale. Then we will set a criteria range for the search. To see how many sales you have to Fred create a range in U1:V2.
U1=Name
V1=Week
U2=Fred
V2=1
V2 is the week. Here we are searching for all sales to Fred for week 1.
=Dsum(X1:Z100,3,U1:V2)
The formula will search the data table X1:Z100 and add up column 3 for the criteria found in U1:V2) So, for every row that has a name of Fred and a Week of 1 it will add up the third column.
DSUM are useful, but if the search criteria is deleted it may add up the entire column of values.

Philip

[maskinhenrik]

Thanks for all your help, it doesnt seem to work, do yuo have more suggestions?

Ill, see your answers tomorrow, i have to go home now

Thanks

[PDuPre']

Maybe if you give me some more details. How many names are involved? If only a few there might be some other options. If several hundred or more I'll try to explain how to set up the table better.

Philip