Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Editing Formula daily problem

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi, It would be big help if anyone can help me with this problem

    Suppose in Cell A1 I have date 25-Mar-02
    And in Cell B1 I have fomula:
    ='c:My Document[Valuation 25-Mar-02.xls]Ingenium'!$E$4

    NOTE: The file name has the date within to correspond with Cell A1.
    The problem here is suppose I have to copy the B1 formula to all rows in Column B and ensuring the file name within each formula has the date correspond to the date in the same row in Column A

    Such as Cell A2: 26-Mar-02
    So Cell B2:='c:My Document[Valuation 26-Mar-02.xls]Ingenium'!$E$4

    So On...

    Please help!!

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    I think for this purpose, you might want to use a macro, codes as follow:

    For i = 1 To Range("A1").End(xlToRight).Column
    Range("B1").Offset(0, i - 1).Formula = "='c:My Document[Valuation " & Format(Range("A1").Offset(0, i - 1), "dd-mmm-yy") & "]Ingenium'!$E$4"
    Next i

    Note, for the file directory, its is 1 slash each time, instead of 2.

    And "Range("A1").End(xlToRight).Column" is the last column of continueous data on row "A", ie. when you selected cell A1, then press ctrl-"right arrow". (this is to capture all the columns in row A. Alternatively, can replace that with a number(depends how many column you want to enter the formula in row B for).

    HTH

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-25 20:52, chaju wrote:
    Hi, It would be big help if anyone can help me with this problem

    Suppose in Cell A1 I have date 25-Mar-02
    And in Cell B1 I have fomula:
    ='c:My Document[Valuation 25-Mar-02.xls]Ingenium'!$E$4

    NOTE: The file name has the date within to correspond with Cell A1.
    The problem here is suppose I have to copy the B1 formula to all rows in Column B and ensuring the file name within each formula has the date correspond to the date in the same row in Column A

    Such as Cell A2: 26-Mar-02
    So Cell B2:='c:My Document[Valuation 26-Mar-02.xls]Ingenium'!$E$4

    So On...

    Please help!!
    Hi chaju:
    BabyTiger has given you a macro solution. I give you below a formula that you can you can use as well. Here goes

    ="'c:\My Document\[Valuation"&TEXT(A1,"dd-mmm-yy")&".xls]Ingenium'!$E$4"

    so if you have 25-Mar-02 in cell A1, and 26-Mar-02 in cell A2, applying the formulas will give in cells B1 and B2 ...

    'c:\My Document\[Valuation25-Mar-02.xls]Ingenium'!$E$4
    'c:\My Document\[Valuation26-Mar-02.xls]Ingenium'!$E$4

    HTH
    Please post back if it works for you ... otherwise explain a little further and let us take it from there!

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi chaju:
    Please not that this board doubles up on the use of the (slash caharacter) ... so you can adjust this aspect or any thing else to suit!

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks,, but I am not looking for solution to display the formula...
    I am looking for the data linked with those file name (with corresponding date)

    Any further help.. many many thanks

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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