+ Reply to Thread
Results 1 to 5 of 5

Pulling data from a table that changes size?

  1. #1
    Registered User
    Join Date
    02-01-2021
    Location
    Florida, USA
    MS-Off Ver
    2010
    Posts
    2

    Pulling data from a table that changes size?

    So, i need some help. I am moderately experienced with excel but i dont even know where to start with this one. I receive a district schedule from work every week in excel format. (the top spreadsheet) and i need it to "convert" into the lower spreadsheet to show a "at a glance" as to which store an employee is at for that day (don't care about the scheduled start and stop time, just the location/day/employee. This would be relatively simple if the spreadsheet always was exactly the same...but its not. Example would be for store 12345 sometimes has 2 employee scheduled at it or 1, or 3. This causes the merged cell with the store number on the left to always change. Any suggestions on how to start?
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,873

    Re: Pulling data from a table that changes size?

    One way:

    =IFERROR(INDEX(LOOKUP(ROW($A$3:$A$30),ROW($A$3:$A$30)/($A$3:$A$30<>""),$A$3:$A$30),MATCH(1,($B$3:$B$30=$A33)*(C$3:C$30<>""),0)),"Off")

    copied across and down. see sheet.
    Attached Files Attached Files
    Glenn



  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,873

    Re: Pulling data from a table that changes size?

    I just remembered... The formula MAY be an array formula in your version f excel. I tweaked it slightly in the file attached here.

    SO, please refer to the attached file. IF the formulae are enclosed within a pair of { }, these are array formulae.

    These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    02-01-2021
    Location
    Florida, USA
    MS-Off Ver
    2010
    Posts
    2

    Re: Pulling data from a table that changes size?

    Looks like it is set up as an array, no issues there. I don't know why i didn't think of MATCH function. Works great! Thank you!!!

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,873

    Re: Pulling data from a table that changes size?

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

+ 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. Pulling Data from one Table to another
    By mhoffman338 in forum Excel General
    Replies: 9
    Last Post: 10-30-2018, 07:07 PM
  2. Pulling data from table
    By GoodDaySir0 in forum Excel General
    Replies: 2
    Last Post: 08-13-2015, 11:32 AM
  3. [SOLVED] Pulling data from table
    By DonW in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-18-2014, 07:34 AM
  4. [SOLVED] Pulling Aggregate Data from Table 1 to Automate Drawing Conclusions from Table 2
    By QuantumPolagnus in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-11-2013, 05:14 PM
  5. [SOLVED] Pulling info from Table depending on size
    By Cidona in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 06-09-2012, 12:36 PM
  6. Replies: 2
    Last Post: 04-03-2012, 12:41 AM
  7. Pulling data from a table
    By pinkpeace7 in forum Excel General
    Replies: 7
    Last Post: 11-18-2009, 11:44 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