auto replace text in a formula

  • Hi guys,
    this forum rocks. I'm getting back into Excel, but i'm have difficulty in auto updating text in a formula.


    I have attached example file.


    Every week, new sheet is added, and then formulas in this main sheet need to be updated to add latest week data. E.g for weekending 09-Oct-16, will need to go to formula in cell J9 and replace 0210 with 0910.


    I currently do this manually, by going into Edit, Find and Replace and replace text in formula accordingly.


    However, I want to be able to enter 09/10/16 in cell B9 and it automatically update formulas in cell J9:O9.


    I researched and thinking of the REPLACE or SUBSTITUTE function but can't seem get it to work.


    Any help appreicated! thx

  • Re: How to auto replace text in a formula


    For simplicity, I edited the formula in B10 to prepend 'NPS' to the start of the formatted date. You could include it directly in the main formula but then you'll be using multiple quotes marks to delimit the literal string and they're confusing enough on their own...
    [bfn]="NPS" &TEXT(B9,"ddmm")[/bfn]


    Your main formula then becomes
    [bfn]=VLOOKUP(K6,INDIRECT($B$10 &"!A:B"),2,FALSE)[/bfn]


    Obviously you need to make those references Absolute/Relative depending...

  • Re: How to auto replace text in a formula


    Quote from cytop;778153

    For simplicity, I edited the formula in B10 to prepend 'NPS' to the start of the formatted date. You could include it directly in the main formula but then you'll be using multiple quotes marks to delimit the literal string and they're confusing enough on their own...
    [bfn]="NPS" &TEXT(B9,"ddmm")[/bfn]


    Your main formula then becomes
    [bfn]=VLOOKUP(K6,INDIRECT($B$10 &"!A:B"),2,FALSE)[/bfn]


    Obviously you need to make those references Absolute/Relative depending...


    Genius, that works a treat! thank so much cytop :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!