none
VBA to conditionally update formulas to point at a different worksheet RRS feed

  • Question

  • Hi,

    Can anyone help?

    I have an excel spreadsheet with a number of worksheets. I have a dashboard sheet with a load of formulas that either point to another worksheet (the latest software version under test) such as the below (where 1.0 is the name of the worksheet)...

    =COUNTIFS('1.0'!E2:E140,"*3.5,*",'1.0'!F2:F140,"Pass")

    Or occasionally formulas which point to other worksheets or which point to within the worksheet...

    =IF(AD16>0,"Fail",IF(AND(AD16=0,AC16>0),"Pass",IF(AND(AC16+AD16=0),"No Run")))

    I would like to be able to update all of the formulas which point to the last version... (1.0) to point to a new version/worksheet when it comes out...

    Ideally this would involve me-

    Pressing a button from within the spreadsheet saying "add new version" or something similar....

    Which will open a dialog box asking me to enter the version to be replaced... (I.e. 1.0)

    Once I have entered this, it will ask me the new version... (I.e. 1.1)

    Once I enter this and confirm it, it will do a find and replace of all the formulas to change all references to '1.0'! to '1.1'! without changing any of the other formulas (which point to other worksheets or to the worksheet being changed...)

    If anyone could help me I'd really appreciate it.

    Monday, July 7, 2014 8:32 PM

Answers