Output result of a formula to a different cell?

I want to output the result of a formula to a different cell.

ie: formula in a1, but result of formula in a2. (Not a problem if the result is duplicated in a1 and a2).

The important thing is that the cell to which the result is sent, must be an unedited (must not contain any formula's) cell.

Any help would be greatly appretiated.

Thanks.

Hi,

At first sight, you request is a bit mysterious ...

1. in cell A2 type =A1
2. or do you want an automatic copy of the result ... but why ?


HTH
Carim

Hi,

Thanks for the reply.

I am familiar with the =A1 type commands.

I need the formula in A1 to dictate what will be displayed in A2, without actually putting any formula in A2.

The point is that A1 might be in a hidden column and that A2 will be visible.

I know that the formula can be hidden by protecting the worksheet, but this will not solve my porblem.

I have a macro that needs to be able to "read" the displayed value in order to change the formatting of the target cell.

I am not sure if it is even possible to send the result of a formula or calculation to a cell other than the one in which the formula is, but that is what I need.

Thanks again for your help.

Well it can be done with an event macro ...

Is it just for cell A1 "to be sent" in cell A2 ...? or the whole row 1 to row 2 ?

Carim

An event macro will do the job ...

Please Login or Register  to view this content.

on the worksheet tab right click view code
copy code ...

HTH
Carim

Sorry ...
but my proposal is stupid since you said the row or column would be hidden...

I need to know which cells are part of your hidden formula in cell A1, in order to properly write the event macro ...

Thanks
Carim

Hi,

Sorry for the confusion.

If the problem can be solved with a macro, there will be no need to hide anything.

It is a simple calculation.

I am making a BMI (Body Mass Index) calculator.

The tricky bit is getting the cell formatting to work properly.

I have the following code in my target cell (F5):

=IF(D5<18.5,"UNDERWEIGHT",IF(D5<25,"NORMAL",IF(D5<30,"OVERWEIGHT",IF(D5>29.9,"OBESE"))))

The problem is, that if I change the value of D5, F5 does not "refresh" and does not change the cell formatting unless you double-click on F5 after each value change.

I am controlling my cell formatting with the following macro:




Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

If Not Intersect(Target, Range("F5:F7")) Is Nothing Then
Select Case Target
Case "UNDERWEIGHT"
icolor = 3
Case "NORMAL"
icolor = 10
Case "OVERWEIGHT"
icolor = 3
Case "OBESE"
icolor = 3
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub


Thanks.

=IF(D5<18.5,"UNDERWEIGHT",IF(D5<25,"NORMAL",IF(D5< 30,"OVERWEIGHT",IF(D5>29.9,"OBESE"))))

The problem is, that if I change the value of D5, F5 does not "refresh" and does not change the cell formatting unless you double-click on F5 after each value change.

As I read this statement of the problem, it sounds to me like calculation mode is set to manual where you want it set to automatic, unless I missed something??? As I look at the formula for F5, any change in D5 should be reflected in F5 if calculation is set to automatic (and Excel is properly building the dependency tree). Before this gets to complicated, check to make sure you've got calculation set to automatic.

Originally Posted by Bryan Hessey

Hi,

Without setting that up to test I would guess that the 'change' is actioned when the D5 value changes, so why not widen your trigger to include D?

If Not Intersect(Target, Range("D5:F7")) Is Nothing Then

and Select Cells(target.row,5)

This should do the trick without the need for further adjustment.

hth
---

Tried that, but still does not work.

Also made sure that my calculations are set to automatic.

My calculation in F5 works perfectly and the value in the cell changes without problem.

The issue is that the cell formatting that is supposed to be done by the macro is not done when the calculation result is displayed.

The user inputs his/her weight and lenght.
The result of this is displayed in D5 as their BMI.

F5 then references D5 in order to display what their BMI means. Either "underweight", "normal", "overweight" or "obese".

F5 also is supposed to change background color: green for "normal" and red for the other 3.

Everything works, except the cell formatting on F5.

I can't use conditional formatting as there are 4 options.

Now I understand a little better, the problem isn't in the formula for F5, but in getting the formatting to automatically change.

t seems to me you should be able to use conditional formatting, as there are really only two options (again, unless I missed something important): Green if
F5=normal, red if F5<>normal. Maybe a third option: F5 is white if F5 has an error value.

If you still want to use on of the event macros that has been offered, it might work better associated with the calculate event rather than the change event.

Originally Posted by nawu

Hi,

Thanks for the reply.

I am familiar with the =A1 type commands.

I need the formula in A1 to dictate what will be displayed in A2, without actually putting any formula in A2.

The point is that A1 might be in a hidden column and that A2 will be visible.

I know that the formula can be hidden by protecting the worksheet, but this will not solve my porblem.

I have a macro that needs to be able to "read" the displayed value in order to change the formatting of the target cell.

I am not sure if it is even possible to send the result of a formula or calculation to a cell other than the one in which the formula is, but that is what I need.

Thanks again for your help.

Hello Fellows,
I am new in this forum, and this question has brouthgth me here. What I am trying to do is to save an excelsheet with the name of a cell, that is increasing every time a document is saved. I am using a macro to do it, copying the result of the cell (file name) into the "save" box. I hope it make sense and some one can help me.

Thanks and Greetings

adolfo