+ Reply to Thread
Results 1 to 3 of 3

check formula integrity: replace dynamic cell reference with fixed value?

  1. #1
    Registered User
    Join Date
    04-20-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    1

    check formula integrity: replace dynamic cell reference with fixed value?

    I have a formula in sheet 1 cells A1:A10 like '=2+(8/3)-B1', '=2+(8/3)-B2', etc.

    Now, in sheet 2 I want to check / validate the 'fixed' part of the formulas on sheet 1, that is '=2+(8/3)-'. I would prefer to replace the cell reference in sheet 1 (B1, B2, etc) with a fixed value like 1. This way I am sure that within a column, all formulas are dragged down properly etc.

    Any idea on how to achieve this?! I was thinking of the indirect function, but I am not sure if that may be the best solution...

    Thanks for any consideration given!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,117

    Re: check formula integrity: replace dynamic cell reference with fixed value?

    Maybe:

    =2+(8/3)-ROW(B1)


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: check formula integrity: replace dynamic cell reference with fixed value?

    Hi and welcome to the forum

    If you are worried that =2+(8/3)-B1 will not change to =2+(8/3)-B2 when copied down, unless some-one messes with that formula, it will change like that every time.

    If all you want to do is check the 1st part (constant) in your formula, just add +b1 to the end, to cancel out the -B1
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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