How do I put a formula in one cell, to send a value to another cell?

M

Muke

New member
Joined
Aug 12, 2012
Messages
28
Reaction score
0
Points
0
Hi there, I wonder if anyone can help. I am trying to send a value to a cell, without having to put the formula in that cell, is it possible.

Just to try and be clear, here's an example : I'd like to put a formula in cell a2, referencing cell a1 and have the result display in a3.

If anyone can help, I would be very grateful. I've spent quite some time using the excel help function, but to no avail.

Thanks in advance....
 
Muke said:
Hi there, I wonder if anyone can help. I am trying to send a value to a cell, without having to put the formula in that cell, is it possible.

Just to try and be clear, here's an example : I'd like to put a formula in cell a2, referencing cell a1 and have the result display in a3.

If anyone can help, I would be very grateful. I've spent quite some time using the excel help function, but to no avail.

Thanks in advance....
Click to expand...

Nocando with formulas. You'll need VBA
 
Can you explain what you are trying to do and why you can't put a formula in A3?
 
JoePublic said:
Can you explain what you are trying to do and why you can't put a formula in A3?
Click to expand...

The spreadsheet is actually a form that we are trying to develop. We are trying to use the result from another cell to either populate this cell with a n/a, or to leave it blank so that data can be entered. We cannot put the formula in that cell in case it needs to be overwritten dependent on the status. Does that make sense?
 
You could also contemplate putting in a formula and then protect your sheet. that way formulas can be protected as well. Make sure the right cells are "locked" and/or "hidden". Good luck
 
Which cell needs the formula and what is the formula?
 
Muke: See the attached workbook by way of example of Eric's suggestion, if you don't want to use VBA. (VBA will work just as well, as JoePublic suggests).

Note this file is protected, but there is no password. So to unprotect and see what's going on, just hit the Unprotect Sheet icon from the Review tab (assuming you have Excel 2007 or 2010).

The reason for password protecting the worksheet is that when a cell's Protection property is set to "Hidden" and "Unlocked" then any formula in that cell isn't displayed to the user if the sheet is protected. Meaning you have a formula in the cell that displays a message based on some condition, while also allowing the user to overwrite that cell if required. The user won't even realize there's a formula behind it. So they can click once on the cell and overwrite it with something else, without being 'freaked out' by seeing a formula pop up in the formula bar.

You set access these "Hidden" and "Unlocked" settings by pushing Ctrl + 1, then selecting the Protection tab.View attachment Book1.xlsb
 
JeffreyWeir said:
Muke: See the attached workbook by way of example of Eric's suggestion, if you don't want to use VBA. (VBA will work just as well, as JoePublic suggests).

Note this file is protected, but there is no password. So to unprotect and see what's going on, just hit the Unprotect Sheet icon from the Review tab (assuming you have Excel 2007 or 2010).

The reason for password protecting the worksheet is that when a cell's Protection property is set to "Hidden" and "Unlocked" then any formula in that cell isn't displayed to the user if the sheet is protected. Meaning you have a formula in the cell that displays a message based on some condition, while also allowing the user to overwrite that cell if required. The user won't even realize there's a formula behind it. So they can click once on the cell and overwrite it with something else, without being 'freaked out' by seeing a formula pop up in the formula bar.

You set access these "Hidden" and "Unlocked" settings by pushing Ctrl + 1, then selecting the Protection tab.View attachment 1271
Click to expand...


That's a great help, thank-you very much for the example and the explanation, it's much appreciated.
 
You must log in or register to reply here.
Back
Top