+ Reply to Thread
Results 1 to 3 of 3

Edit filename in Formula with reference to a cell in current file

  1. #1
    Registered User
    Join Date
    08-05-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    25

    Unhappy Edit filename in Formula with reference to a cell in current file

    I have stock report files for every week named as
    1) Stock Repo-01, 2) Stock Repo-02, 3) Stock Repo-03 and so on where 01, 02 indicate week numbers

    Every report file has separate sheet for days and Summary Sheet showing total transactions of all products every day .

    Summary Sheet has Current Report no. in cell E1 and date in cell F1
    col-A>Part No., col-B>Product, col-C>Opening Balance, col-D> Recieced Qty. col-E> Issued Qty. col-F> Closing Balance

    quantities in column F from file <Stock Repo-01> are to be copied in column C of successive file <Stock Repo-02>
    the formula in cell C5 is =’[Repo-01.xlsm]summary’!$F$5
    now I want this formula to take no. 02 from cell E1 (where E1 contains current Report no. that is 02) so that formula will be =’[&”Repo-“(E1-1).xlsm]summary’!$F$5
    but its not working, what changes are required in formula to work.
    Else can I use Lookup formula having similar changes in filename as above.

  2. #2
    Forum Contributor
    Join Date
    08-14-2013
    Location
    Here and there
    MS-Off Ver
    Excel 2010
    Posts
    376

    Re: Edit filename in Formula with reference to a cell in current file

    Any chance you could attach a sample (removing all personal details)

  3. #3
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: Edit filename in Formula with reference to a cell in current file

    Use the INDIRECT function, so something like:

    =INDIRECT("'[Repo-0"&E1-1&".xlsm]summary’!$F$5")

    Notice that I manually typed the "0", because if you have "02" in a cell that's formatted as number, the 0 will disappear.
    When I say semicolon, u say comma!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA script- save file with current filename
    By -James_ in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-06-2012, 04:10 PM
  2. Help with formula of filename and saving filename as current time
    By hqradio in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-04-2012, 06:10 AM
  3. Reference Current Cell in Formula
    By tpowell55 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-27-2010, 09:08 PM
  4. Replies: 2
    Last Post: 03-08-2010, 09:40 PM
  5. Create buttom that saves current file in a given filename (Excel)
    By mamealemka in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-17-2006, 08:10 AM

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