+ Reply to Thread
Results 1 to 2 of 2

Google Sheets 'Arrayformula' with nested 'Importrange' formula Excel Equivalent?

  1. #1
    Registered User
    Join Date
    06-19-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    83

    Google Sheets 'Arrayformula' with nested 'Importrange' formula Excel Equivalent?

    I'm trying to consolidate individual spreadsheet tabs across multiple spreadsheets into one master sheet that collects all the data for analysis.

    Google Sheets has this formula that will display the selected criteria: =ARRAYFORMULA(IMPORTRANGE("Spreadsheet URL","Tab Name!A1:J29")).

    That formula is entered into 1 cell (the green cells (one formula per tab) in my consolidation example), and it displays everything on the tab in the selected data range (noted in the formula) across the equivelent number of cells in the new spreadsheet.

    1 formula entered into 1 cell, and a whole range of data is entered automatically. It live-updates and requires no maintenance (ie - no copy/paste, no re-consolidating it, no need to enter a vlookup or index formula into every single cell to get the current data).

    Original Data Link: https://drive.google.com/open?id=1yy...J4b2LxZio7XqI4
    Consolidated Data Link:https://drive.google.com/open?id=1wX...21WHq7ZMpwZH_0

    Does Excel have an equivalent option? All original files would be saved in the same server folder.


    I tried asking this question in the Excel Formulas & Functions forum, but all I got for replies was "please put this into Excel so we know what you're talking about". If I could do that, I wouldn't be asking for help with this issue.
    Last edited by Tamarissa; 07-10-2018 at 04:08 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    45,577

    Re: Google Sheets 'Arrayformula' with nested 'Importrange' formula Excel Equivalent?

    No, we asked for you to show us some data and what you expected, so we could see what we could come up with in excel for you
    (and if you took a gander at the forum rules, you would see that posting duplicate threads is not permitted, but I will let that slide - this time )

    https://www.excelforum.com/excel-for...-workbook.html
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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