Need a macro code!

[bjornlindholm]

Hi! I need a macro code that will automaticly multiply a user defined cell value with a lists value. But the hook is that i need the cell's user defined value to be on the cell display but the value that the cell is defined as is the product of the list and the cells user defined value

 

[Joe4]

Welcome to the Board!

I think your question is a bit confusing and unclear. You may have more luck if you can walk us through an actual example.

 

[bjornlindholm]

Welcome to the Board!

I think your question is a bit confusing and unclear. You may have more luck if you can walk us through an actual example.

Okay!

So let's say that we have a list in cell C2 the list ranges from 1-4, then you have a cell D2, this cell D2's value (that is defined by the user of the document) should be multiplied with cell C2's value. The display of D2 should be the value that the user puts in the cell but the "data" from the cell should be C2*D2. So if you have 2 in the list in C2 and 400 as a user defined value in D2. D2 should display 400 but the output from the cell should be 800.

I hope you get my problem now!


// Björn Lindholm

 

[Joe4]

So let's say that we have a list in cell C2 the list ranges from 1-4, then you have a cell D2, this cell D2's value (that is defined by the user of the document) should be multiplied with cell C2's value. The display of D2 should be the value that the user puts in the cell but the "data" from the cell should be C2*D2. So if you have 2 in the list in C2 and 400 as a user defined value in D2. D2 should display 400 but the output from the cell should be 800.

I don't think that is possible. A cell can only hold one particular value (it can be a formula or a hard-coded value).
You can format the value in the cell to display in any format you choose, but it is formatting the current value. I don't know of any way you can format it to show a totally different value.
I think you will need to use a different cell to do that (you can always hide the cell so it is not visible).

So, what exactly is the reason you want it to behave this way?
If we have a clear understanding of why you want to do it this way, we may be able to provide alternative solutions.
For example, if you need the underlying value to be different, as it is being used in calculations somewhere, you can probably just adjust the calculation instead (to multiply or divide by 2).

 

[bjornlindholm]

I don't think that is possible. A cell can only hold one particular value (it can be a formula or a hard-coded value).
You can format the value in the cell to display in any format you choose, but it is formatting the current value. I don't know of any way you can format it to show a totally different value.
I think you will need to use a different cell to do that (you can always hide the cell so it is not visible).

So, what exactly is the reason you want it to behave this way?
If we have a clear understanding of why you want to do it this way, we may be able to provide alternative solutions.
For example, if you need the underlying value to be different, as it is being used in calculations somewhere, you can probably just adjust the calculation instead (to multiply or divide by 2).

Hi!
Do you have any of kind of skype or similar forms of communcation software because im not a english speaking native it's pretty hard for me to write down the function of the document. It would be really helpful if i could explain it with a live feed of my screen.

Thanks!
Björn Lindholm

 

[Joe4]

Do you have any of kind of skype or similar forms of communcation software

I am sorry, but I do not. I also do not have the ability to download any files that are posted to file sharing sites, due to my company's security protocol.

I don't know if it will help, but there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

I don't really need a detailed explanation at this point, I am just trying to understand why you want the displayed value to be different than the underlying value.
How is the displayed value being used?
How is the underlying value being used?

 

[bjornlindholm]

Alright. I'll try to explain the problem,, the display value is going to be used in a planning system that sums up a whole row of numbers (square meters), but the underlying value is going to be used to try to convert the cells square meters into hours according to different levels (the list of diffrent values (C2 in previus explaintion))of diffuculity of diffrent jobs. The problem is that if i change the value of C2 the planning of the square meters will be larger or smaller according to the jobs needs. So if i should plan for 400 square meters of job then i multiply that with 2, the hours will be correct but the sum is going to be twice as large as the planned amount.

 

[Joe4]

You should be able to adjust one calculation or the other to account for that.
It should just be a matter of amending one of the formulas to multiple or divide by the value in C2, right?

For example, if your SUM formula was something like:
=SUM(some range)
then I think all you need to do is change it to this:
=SUM(some range)/C2