Results 1 to 9 of 9

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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....

  2. #2
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,336
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Quote Originally Posted by Muke View Post
    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....
    Nocando with formulas. You'll need VBA

  3. #3
    Quote Originally Posted by Pecoflyer View Post
    Nocando with formulas. You'll need VBA
    Thanks very much for the response. Is it a very complicated task to set this up in VBA do you know?

    Thanks in advance

  4. #4
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    172
    Articles
    0
    Excel Version
    2010
    Can you explain what you are trying to do and why you can't put a formula in A3?
    Circumference of a circle = 2πrē



    ēthe circle's radius

  5. #5
    Quote Originally Posted by JoePublic View Post
    Can you explain what you are trying to do and why you can't put a formula in A3?
    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?

  6. #6
    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

  7. #7
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    172
    Articles
    0
    Excel Version
    2010
    Which cell needs the formula and what is the formula?
    Circumference of a circle = 2πrē



    ēthe circle's radius

  8. #8
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    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.Book1.xlsb

  9. #9
    Quote Originally Posted by JeffreyWeir View Post
    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.Book1.xlsb

    That's a great help, thank-you very much for the example and the explanation, it's much appreciated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •