+ Reply to Thread
Results 1 to 10 of 10

Pull Same Cell from Multiple Sheets into one Row

  1. #1
    Registered User
    Join Date
    02-15-2016
    Location
    USA
    MS-Off Ver
    10
    Posts
    4

    Pull Same Cell from Multiple Sheets into one Row

    Hi everyone,
    New here, but I did search the threads to see if my answer was on here somewhere, and I couldn't find something that worked for me. I've seen a lot of threads with the Indirect Function which I think I understand, but I keep getting the #REF! every time I've tried.

    I need to pull values from the same cell on different sheets, and create a list on a Summary Sheet.

    For example, I would like to pull cell F20 on all of my previous sheets (let's say Sheet 1 through Sheet 10) into Column D of "Summary Sheet". I'm looking for a formula I just drag from D3 to D13 to populate it.

    Extra Question - Is there also a way for me to add sheets, and the summary sheet will also pick up on the added sheet, and insert the value into the Summary Sheet?

    Many thanks in advance.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,076

    Re: Pull Same Cell from Multiple Sheets into one Row

    Google excel 3dreference
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    02-15-2016
    Location
    USA
    MS-Off Ver
    10
    Posts
    4

    Re: Pull Same Cell from Multiple Sheets into one Row

    Thanks Special-K. But I'm not trying to enter use a formula with that Data. It seems like the 3d reference would only be used to do something with the data in the same cells (sum, avg, etc).

    I just want it to populate into different in a column. I could just do a =Sheet1!F20 into D3 of my Summary Sheet , Sheet2!F20 in D4, =Sheet3!F20 in D5 and so on. So that's why I'm wondering if there's a formula that can be dragged down since I'll be doing this for hundreds of data points and multiple lists.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,840

    Re: Pull Same Cell from Multiple Sheets into one Row

    Try pasting the following in D3 and dragging down:
    Please Login or Register  to view this content.
    Let me know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    02-15-2016
    Location
    USA
    MS-Off Ver
    10
    Posts
    4

    Re: Pull Same Cell from Multiple Sheets into one Row

    Awesome! Thanks JeteMc. This worked great. My follow up questions:

    I'm trying to understand formulas better, and I understand that the "Sheet" means that any sheet that has the name sheet in it, it will pull from (I think), but what's the purpose of putting the &ROW(A1), which drags down?

    My second question is it possible to have a formula similar to this while changing the Sheet Names? For example, I will be using this spreadsheet for apartment units, so how would the formula change if I named the Sheets, 101A, 101B, 103C, 103D, etc?

    Thanks again - this is great for what I need.

  6. #6
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Pull Same Cell from Multiple Sheets into one Row

    in case of sheets as 101A, 101B and so on use
    =INDIRECT("101"&CHAR(64+ROW(A1))&"!F20")

    will work up to 101Z
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,840

    Re: Pull Same Cell from Multiple Sheets into one Row

    Quote Originally Posted by sorduj View Post
    what's the purpose of putting the &ROW(A1), which drags down?
    Row(A1) tells the formula to append the row for cell A1, which is one, to "Sheet". Since A1 is written as a relative it will change as you pull down, to A2 and then A3. Therefore "Sheet" will be appended by 2 and then 3 etc.

    Quote Originally Posted by sorduj View Post
    My second question is it possible to have a formula similar to this while changing the Sheet Names? For example, I will be using this spreadsheet for apartment units, so how would the formula change if I named the Sheets, 101A, 101B, 103C, 103D, etc?
    I would suggest making a table of apartments and writing an INDIRECT formula to look at the table and pull information from different sheets. I have set up a sample file to show you how this might work:
    Apartment Info.xlsx
    Let me know if you have any questions.
    Last edited by JeteMc; 02-15-2016 at 02:25 PM.

  8. #8
    Registered User
    Join Date
    02-15-2016
    Location
    USA
    MS-Off Ver
    10
    Posts
    4

    Re: Pull Same Cell from Multiple Sheets into one Row

    This is great! Thanks so much!

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,840

    Re: Pull Same Cell from Multiple Sheets into one Row

    You're Welcome and thank you for the feedback. If you haven't already please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a good day.

  10. #10
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Pull Same Cell from Multiple Sheets into one Row

    wrongly posted
    Last edited by hemesh; 02-16-2016 at 04:07 AM.

+ 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. Pull same cell from multiple sheets into master column
    By mattminiat in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-13-2022, 12:40 PM
  2. Pull data from multiple sheets
    By dubtheninja in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-04-2014, 11:20 PM
  3. Pull data from multiple sheets based on cell in Main sheet
    By cpyter in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-29-2014, 01:19 PM
  4. One Workbook to pull multiple sheets..
    By VIPShannon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-19-2013, 02:55 PM
  5. Data pull from multiple sheets
    By Velmortis in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-02-2013, 05:21 PM
  6. [SOLVED] Want to pull values based on same cell # but from multiple sheets
    By pamc in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-29-2013, 09:52 AM
  7. [SOLVED] Pull data from same cell from multiple sheets into master column
    By incubi in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-09-2013, 04:42 PM

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