+ Reply to Thread
Results 1 to 5 of 5

Pull cell from formula

  1. #1
    Registered User
    Join Date
    01-19-2010
    Location
    Manchester, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Pull cell from formula

    Hello,
    I have a spreadsheet with two worksheets which refer to each other in formulas.
    What I want to do is find the title of the column of which each formula refers.

    For example, I might have sheet 1 and 2. In sheet 2 I have a formula that refers to cell A12 in sheet 1, which contains a table of data. I want to, in another cell in sheet 2, pull out the title at the top of column A.

    The formulas are sporadically spread around the pages, not following any pattern and the data in sheet 1 is not unique, so lookups wouldnt necessarily work. I need to literally pull out the cell coordinates from the formula in sheet 2.

    =F64*'Sheet1'!$G$11

    Now, I can pull out the whole formula as a string using VBA, is it possible to just pull out the cell coordinates?

    Hmm, quite a complicated explanation so sorry if its confusing, but I hope someone can help me.

    Thanks!

    Steven

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Pull cell from formula

    Hi,

    If I've understood correctly, and assuming the formula you mention is in say A1 then the st variable from the following procedure will return the address G11. No doubt you already have a variable so you should be able to modify this to use the one you already have.

    Please Login or Register  to view this content.
    HTH
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    01-19-2010
    Location
    Manchester, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Pull cell from formula

    Nice thanks, so that gives me the value $A$12 (for example) as text.
    How can I put this into a formula for use with some kind of offset? for instance, now I have $A$12 as text, I want to pull the value from $A$8 because this is where the title of that column is. This means I'll need to pull the cell value as you have told me and put this is a formula with an offset in order to pull the column title.

    Thanks for the help and quick reply!

    Steven

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Pull cell from formula

    Hi,

    Not sure whether you're wanting this information in VBA or as a cell formula so I've catered for both in the following.
    Assumes the column title is 4 rows above the address found by the st variable. These additional two lines will put the column title in range("B1"), and the stTitle variable will return the same thing.

    Please Login or Register  to view this content.
    HTH

  5. #5
    Registered User
    Join Date
    01-19-2010
    Location
    Manchester, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Pull cell from formula

    thanks, I've managed to crack it myself only mine isnt as nice as yours! The table in sheet 1 goes horizontally across the sheet and is split at one point and continued below. For this reason I have to convert the cell from text to a number to test if im pulling the value from the top or bottom section of the table then apply that to a formula in my vba, here is what I've got:

    Please Login or Register  to view this content.
    Last edited by ministe2003; 01-19-2010 at 07:56 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1