+ Reply to Thread
Results 1 to 5 of 5

Insert new excel sheets in a file - Issue with ranges - Need a CLEVER process!

  1. #1
    Registered User
    Join Date
    11-07-2012
    Location
    Europe
    MS-Off Ver
    Excel 2010
    Posts
    53

    Arrow Insert new excel sheets in a file - Issue with ranges - Need a CLEVER process!

    Hello!
    So, this is my situation - I have developed an excel tool, which makes a lot of useful calculations for me. The calculations come from 10 other sheets, which are in the same file.
    So, in general, I have an excel file with about 20 sheets - 10 make the calculations and 10 serve as input data.

    My issue is, that every month I get new input data (10 sheets) and I need a way to integrate them to the tool and to make it work.

    What I do now:
    1. I take every sheet of the 10 sheets for "input data" and I rename it manually, putting "old_" in front.
    2. Excel automatically changes the formulas in a way to reference this "old_" name. (This is actually my problem!)
    3. I insert the 10 sheets with the new data.
    4. I press about 10 times "Replace" and I replace the formulas with "old_" to the new sheet.
    5. It takes me a lot of time... And I do not like it

    Any ideas for process improvement? I understand also macros, so you can give an idea there. I can write a macro, which does exactly the same process for me, (the work is actually 80% macro recorder and 5% deleting useless code actually ) but there should be a more clever way in Excel to do it... Really, something like turning off automatical references or something...

    Any ideas are welcomed!

    Thank you!

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Insert new excel sheets in a file - Issue with ranges - Need a CLEVER process!

    Change your formulas to:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This will then preserve the original sheet names in the formula.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    11-07-2012
    Location
    Europe
    MS-Off Ver
    Excel 2010
    Posts
    53

    Re: Insert new excel sheets in a file - Issue with ranges - Need a CLEVER process!

    Thank you, OllyXLS!
    That is a good option, indeed!
    Any other options would be appreciated.
    Btw, any idea why I get the mistake #REF!, when I use the INDIRECT formula?

    edit: I think it is because of the QUOTES missing

  4. #4
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Insert new excel sheets in a file - Issue with ranges - Need a CLEVER process!

    If you're getting a #REF! error:

    Make sure your formula is enclosed by quotation marks. e.g.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Make sure your sheet and cell references are valid.

  5. #5
    Registered User
    Join Date
    11-07-2012
    Location
    Europe
    MS-Off Ver
    Excel 2010
    Posts
    53

    Re: Insert new excel sheets in a file - Issue with ranges - Need a CLEVER process!

    Thanks once again, it works now.

    Anyway, other options would be appreciated... Anything to make it even faster ....
    (Although I have written a macro to put every formula to =INDIRECT("") automatically... and I really do not know what can be faster )
    Anyhow ... ?

+ 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. Process excel file for rows having value Yes in field 17
    By szpt9m in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-07-2013, 04:45 AM
  2. Replies: 1
    Last Post: 08-29-2013, 07:29 AM
  3. A clever way to pass lot of ranges from the sheets to VBA and vice versa
    By TopXorGuy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-07-2013, 02:49 PM
  4. EXCEL - add formula on specific cells during the insert process
    By eastydie in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-20-2011, 11:26 PM
  5. Invoke exclusive excel process on opening this file
    By JB2005 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-06-2005, 03:05 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