Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Just the Formulas?

    Hi,

    I have a spreadsheet where I've pasted-special some values from another spreadsheet. I would like to apply a formula to each cell in the receiving spreadsheet. The brute force way is to select each cell in turn and type the formula over and over.

    Is there a way to take the formula in a cell and apply it to other cells in a range without destroying the values in the other cells? Paste-special formulas seems to suggest it does the trick but it also pastes the value of the cell copied to the clipboard.

    Thks.

    Fred

  2. #2
    sailerbill
    Guest

    Re: Just the Formulas?

    Fred - there is only one item in a cell (not counting comments). Any value shown is the result of the formula.

    If you want to paste the same formula into a group of cells without changing the cell references (e.g. = A1 * B1) to C2 through E5, then lock make C2 "=$A$1 + &B$2. Now copy C2 to the clipboard, hi-light C2 through E5 and paste.

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Just the Formulas?

    Thks Bill.

    I probably did not express my question properly. I think I understand your answer but it does not seem to apply. If it does, pls explain.

    But first, let me clarify.

    I have a spreadsheet in which I've pasted, say, 10 values from another sheet. I used paste-special just to get the values. So now I have a range of 10 cells with constants in them.

    What I would like to do is create a formula in the first cell using the constant. For example, maybe take a square root of the constant. Then what I'd like to do is apply that same formula to the other 9 cells in the range w/o having to type the formula, parens, etc around the constant for each of the cells.

    Fred

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Just the Formulas?

    From your description, it still sounds like you want to have a constant and a formula in the same cell. If you have the number 4 in cell A1, are you actually entering =Sqrt(4) in cell A1 to get the square root? If that is what you are doing, then there is an easier way. If your constants are in cells A1 to A10, then enter the following formula in cell B1:

    <pre>=Sqrt(A1)
    </pre>


    That should display in cell B1 (or row 1 in the first empty column) the square root of the number in cell A1. Now, select cell B1. There should be a small dark square in the bottom of the cell. When you place the cursor over this square, it should change from a large cross to a thin cross. When it does, press and hold the left mouse button and drag down to cell B10. When you release the left mouse button, you should get the square root of all the values in A1 to A10 in B1 to B10. If you want the results of the formulas back in A1 to A10, you can copy B1 to B10 and Paste Special/Values back to A1 to A10 and then delete B1 to B10.
    Legare Coleman

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Just the Formulas?

    Legare,

    Thks for the info.

    I was perhaps being a little narrow minded. I wanted to do this in the cells that had the numbers and not have to repeat the formula as you and Bill suggested. That way, I would only have one "set" of the data and not lose the original data. I was not aiming to have the original in row 1 and the transformed version in row 2 (or lose the original in row 1 by "over" pasting). So where I was trying to get to was to have:
    - my pasted values from the other sheet in row 1
    - enter, on the formula bar, "=sqrt(" before the value and ")" after the value for A1
    - repeat this for B1... w/o the typing
    In this way, I still have my original data in the formula.

    An alternative would be to be able to select a range and then, perhaps thru user input, specify a formula to be applied to the range.

    Of course the "easy" way to do this is as you said. I can then choose to override the original values with the new values but that loses my originals (and I don't know that the new number was a sqrt of something else). Or I can just keep both sets of data.

    I'm very familiar with the fill capability. Guess I wasn't thinking out of the cell...the box.

    Fred

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Just the Formulas?

    Legare,

    And now that I hit the send button, I think I just realized some of the solution.

    If I have the range selected, then I could loop thru the range. I could then replace the contents, as represented by "x" with "=sqrt(" & x ")".

    My VBA probably isn't too good for being able to do this. Just need an input box to get the function. Not sure how to check that the input is suitable. I would have to check that the inputted function is a valid Excel function (is there a collection? what about user defined functions but I'm not worried about those for now). Once I have the function, I could apply the function to the cell in the range as mentioned above and just keep looping.

    If the function requires >1 argument, such as ROUND, I would assume that all cells in the range would be filled in the same way. But more user input would be needed to get these other arguments.

    Or am I still not thinking out of the cell?

    Fred

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Just the Formulas?

    You could do what you want with VBA like this:

    <pre>Public Sub InsSqrt()
    Dim oCell As Range
    For Each oCell In Selection
    oCell.Formula = "=Sqrt(" & oCell.Value & ")"
    Next oCell
    End Sub
    </pre>


    Select the cells you want to use Sqrt on and run the .
    Legare Coleman

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Just the Formulas?

    Legare,

    Thks. And you probably replied to the 1st of my almost simultaneous posts. This works altho my 2nd post suggested something more general.

    Great minds think alike. Now I just need to find a way to hold up my end of the deal.

    Fred

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Just the Formulas?

    As you can see from the macro I just sent to you, it is certainly possible. You could add in InputBox to it to get the function. However, trying to figure out if the entered function is valid could be a bit of a challenge. I also don't know of a good way to handle functions that require multiple arguments. Also, what if you wanted the cubed root of the number instead of the square root. There is no cube root function, instead what you do is =9^(1/3), in other words, nine to the one third power. That does not fit the function mold that you are looking at.

    I quess I am also wondering why you want to do this anyhow. It defeats one of the things that makes Excel so powerful, being able to calculate results and still have your opriginal data available for new calculations. What if you insert your square root formula around the data, and later discover that what you really wanted was the cube root. If you don't want to see the original data after geting the calculated values in another row or column, then just hide the row or column with the original data, or do the calculations on a different sheet in the same row or column.
    Legare Coleman

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Just the Formulas?

    Legare,

    You've helped as much as I needed. The VBA code was the answer I was looking for when I originally posted. Trying to get too general would probably get complicated very quickly. I think it could be done as I suggested in my last post but not sure it's worth it (check for a valid function and fill in the other args if needed).

    The general case I was aiming for was to take a cell that had a constant and some function applied to it by typing the necessary items on the formula bar. As you suggest, the function could be one that's not even built-in (altho I'd take that as a simplification). If the function only takes 1 arg, then it might be fairly easy to parse the string to see what the function was and then apply it to the other cells. However, if the function required multiple args [eg, =ROUND(9.8763485,2)], how would the parser know whether it was the 1st arg or the 2nd arg that should be substituted for from the other cells? Too complicated.

    Thks for the help.

    Fred

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Just the Formulas?

    If I wanted to do something like this, I would use an InputBox to read the desired formula in a format like this:

    Round(%C%,2)

    Then in my macro code I would substitute the current cell contents for the %C%.
    Legare Coleman

Posting Permissions

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