+ Reply to Thread
Results 1 to 11 of 11

Formula to display a selection of cells from one workbook into a another workbook

  1. #1
    Forum Contributor
    Join Date
    06-19-2012
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    143

    Formula to display a selection of cells from one workbook into a another workbook

    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, and it displays everything in the selected data range across the equivelent number of cells in the new spreadsheet.
    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 files would be saved in the same server folder.
    Last edited by Tamarissa; 07-10-2018 at 04:09 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula to display a selection of cells from one workbook into a another workbook

    I dont use google doc, so I would ask that you upload small (clean) sample workbooks (not a pic) of what you are working with, and what your expected outcome would look like.
    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

  3. #3
    Forum Contributor
    Join Date
    06-19-2012
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    143

    Re: Formula to display a selection of cells from one workbook into a another workbook

    As I stated in my original post, I do not know how to produce the expected results in Excel, as those formulas do not even exist in or have a counterpart in Excel that I can find, which is why I provided the exact scenario in a different spreadsheet format that does produce the exact results I am looking for.
    This way, everyone can see exactly what I am talking about.

    Regardless of the spreadsheet program, it is still a spreadsheet. If you are unable to look at a formula in a cell in Google Spreadsheets and make a comparison between it and Excel, than you aren't able to help me.
    I have no problems with most things switching between the 2 different formats, and others are just as capable. Someone else will maybe be able to provide an answer.
    If not, then I finally found something that Google Sheets does better than Excel. A certain hot location may have frozen over if that's true.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula to display a selection of cells from one workbook into a another workbook

    Quote Originally Posted by Tamarissa View Post
    ...If you are unable to look at a formula in a cell in Google Spreadsheets and make a comparison between it and Excel, than you aren't able to help me. ...
    wow, some would consider that a bit of an arrogant reply?
    Docs uses completely different terminology for some things, than excel does, and I currently see no need to downgrade myself to that app (this is an EXCEL forum, after all)
    For example, there is no native function called ARRAYFORMULA, but excel handles array functions just fine, without bothering to need a function to tell it that it is an array
    Nor is there a native IMPORTRANGE function, it doesnt need 1, it has more than enough functions that work between sheets and files.

    My reason for asking is - if I can understand what you are trying to do, I can better point you in the right direction. In many cases here, the approach is for you to help us help you.

  5. #5
    Forum Contributor
    Join Date
    06-19-2012
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    143

    Re: Formula to display a selection of cells from one workbook into a another workbook

    I also prefer Excel as it doesn't even compare in functionally to Google Sheets, but I am forced by my company to use Google Sheets, so I have learned to adapt (mostly it goes into Excel first for analysis and the results pasted into a Google Sheet).

    Excel really does need equivalents of those functions. They are extremely useful. As I am trying to re-create this scenario in Excel, that is the reason for posting in an Excel forum.

    What happens with formula I indicated is that I enter 1 formula into 1 cell only, and all the data entered on that sheet and within the range referenced is displayed in the new workbook. Ridiculously easy. It live-updates, so I don't have to go back and keep copy~pasting or re-consolidating it, and I don't have to enter a vlookup/index/etc forumla into every single corresponding cell (and still have to refresh them to get any updates that occured) in the consolidating workbook.

    1 formula in 1 cell takes care of all issues; no maintenance required.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formula to display a selection of cells from one workbook into a another workbook

    As requested already, please attach the files here. Some members will not be able access your linked documents. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  7. #7
    Forum Contributor
    Join Date
    06-19-2012
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    143

    Re: Formula to display a selection of cells from one workbook into a another workbook

    Why would members not be able to access the link? All that is required is to click on it, and it will open up.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formula to display a selection of cells from one workbook into a another workbook

    Do you want help here or not?

    Some members will not be able to access file-sharing sites, and some will refuse to do so. You have been asked to attach the file here twice now. It’s your choice, but may be one of the reasons you are not being offered further assistance here.

  9. #9
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Formula to display a selection of cells from one workbook into a another workbook

    Anyway you can ask moderator to move this thread to appropriate part of forum: For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Simply use Report Post from your first post

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formula to display a selection of cells from one workbook into a another workbook

    The OP is looking for Excel equivalent formulae to those they have in Google Docs, Sandy, so it is an Excel query.

  11. #11
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Formula to display a selection of cells from one workbook into a another workbook

    Yep, I know but maybe there OP will find someone who knows GD and Excel formulas

+ 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. [SOLVED] Help Importing from another workbook and pasting multiple selection on original workbook
    By bdouglas1011 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-21-2017, 12:40 AM
  2. Replies: 0
    Last Post: 02-06-2015, 04:50 PM
  3. Formula to Display Cells from Another Page Upon Selection
    By dantmaldo in forum Excel General
    Replies: 3
    Last Post: 05-20-2014, 04:25 PM
  4. Replies: 0
    Last Post: 09-18-2012, 08:10 AM
  5. Replies: 3
    Last Post: 10-18-2011, 11:30 AM
  6. Replies: 7
    Last Post: 08-09-2011, 04:31 AM
  7. Macro to copy selection workbook to workbook
    By mrparrot in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-16-2010, 05:39 PM

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