Results 1 to 5 of 5
  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

    Cell Source (Any)

    Hi,

    We have files that assign IP addresses for certain purposes. Those files typically have multiple sheets with IP address assignments for various purposes.

    We'd like to create a kind of summary sheet (see "variables-values" sheet) that has 3 cols:
    - Col A has the purpose of the address (variable)
    - Col B has the IP address used to fulfill that purpose. This is easy enough to create by just setting the value to the corresponding cell on the appropriate sheet
    - Col C has the source of the value in col B.

    I am attaching a stripped down workbook to illustrate what I am trying to do.

    For Col C, I've used the ADDRESS built-in function to generate the source of the cell in Col B for the first 3 rows. However, I may as well just type the whole string; I don't think the ADDRESS function is saving me much work. What I'd like to do for cell C2 on the variables-values sheet is just be able to click on cell B2 of that sheet. In other words, something like C2 has the formula
    =some function(B2)

    I found JK Pieterse's solution in <post#=372095>post 372095</post#> and replicated that in rows 6-8. I'm just a little concerned about the warning at the end about copying this to another sheet. I tried copying the cells in Excel 2003 without a crash but I'm not sure what versions people might be using. Also I'm not sure about the universality of the GET.CELL function - was this something from the Excel 4 macro package or where does this come from? I certainly didn't see it in the Built-in function list. Possibly to avoid some of these issues, is there a way to convert the result in Col C to just a text string?

    TIA

    Fred

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Source (Any)

    GET.CELL is an Excel 4 macro function. These macro functions are not available as worksheet functions, but they can be used in defined names in all later versions of Excel.

    As an alternative, you could create a custom VBA function in a standard module:

    Public Function GetFormula(oCell As Range) As String
    GetFormula = oCell.Formula
    End Function

    and use a formula such as

    =GetFormula(B2)

    If you place the function in your Personal.xls workbook so that it is available in all workbooks, use

    =Personal.xls!GetFormula(B2)

  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: Cell Source (Any)

    Hans,

    Thanks for the really quick response.

    I forgot to mention that I had also looked at some functions similar to what you posted.

    One of my questions, applying to either the GET.CELL or the VBA approach you posted, was how to convert the result of the formula into a constant. Since we ship the workbook to many people that may use it, I thought that converting the result of the formula into a constant would avoid the issues of Excel 4 or VBA.

    TIA

    Fred

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Source (Any)

    If us change the formula you use to
    <code>
    ="'"&GetFormulaLeft
    </code>
    or with the VBA function
    <code>
    ="'"&GetFormula(B2)
    </code>
    you can copy the cells, then paste special with the Values option. This will replace the formulas with their values. The apostrophe is necessary to prevent Excel from evaluating the formulas.

  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: Cell Source (Any)

    Thanks Hans.

    I'm not sure I'm getting it. Without adding the " ' " & part, I can still copy the cells in col C and paste special | values in col D (or elsewhere) to just get the text of the formula. The difference is that your extra concatenation produces a ' in front of the = sign.

    But I think I have what I need one way or the other.

    Fred

Posting Permissions

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