+ Reply to Thread
Results 1 to 5 of 5

Create multiple tables based on data in a spreadsheet

  1. #1
    Registered User
    Join Date
    05-24-2013
    Location
    usa
    MS-Off Ver
    Excel 2013
    Posts
    18

    Create multiple tables based on data in a spreadsheet

    Hi friendly excel friends. I was hoping someone may be able to provide some guidance on my issue.

    Basically, I am trying to automate the creation of tables in excel based on data in a spreadsheet. I am connected to a sharepoint list that contains computer inventory, and when I refresh the spreadsheet, the data will reflect whatever has been changed/added etc. In summary, I want tables created based on the "location" column in excel. I am trying to make a process that requires very little manual effort to get the tables created, past refreshing the spreadsheet to reflect new data. I am not sure if this can be done with references/formulas/functions, hence why I am posting and expecting that a macro will be needed.

    Sample data attached. See "Data" and "Output Needed" sheets. I would need the data from "Data" to automatically create tables in another spreadsheet. It would need to be dynamic because some sites may not always have data, and there will always be different amounts of inventory per site, per date range of the report.

    Thank you so much for any help!!!!
    Attached Files Attached Files
    Last edited by ablits1; 03-22-2017 at 10:04 AM.

  2. #2
    Registered User
    Join Date
    05-24-2013
    Location
    usa
    MS-Off Ver
    Excel 2013
    Posts
    18

    Re: Create multiple tables based on data in a spreadsheet

    Am I allowed to "bump" this in hopes of some traction?

  3. #3
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Create multiple tables based on data in a spreadsheet

    Generally, "bumping" a thread is a bit of a risk. It gets your thread more short-term views, but it removes your thread from the list of threads that have received zero replies, and many of the regulars here use that list to find threads that still need help.

    If you're willing to be a bit flexible, I think you can get what you want with formulas. Stacking your tables vertically makes automatic updates difficult, unless you know exactly how much space is needed between tables. If you "stack" them horizontally, the layout is easier to predict. In my attachment, I arranged them horizontally and used the following formula in A3:

    =IFERROR(INDEX(Data!$A$2:$G$100,SMALL(IF(Data!$C$2:$C$100=$A$1,ROW(Data!$C$2:$C$100)),ROW(1:1))-1,MATCH(A$2,Data!$A$1:$G$1,0)),"")

    This formula must be array entered (confirmed with Ctrl + Shift + Enter instead of Enter). It can then be filled right through E3 and filled down well beyond what you think you'll need. Repeat the process for sites B and C, changing only the reference to $A$1 in the formula to match the cell with the new site name. With this layout, the tables should automatically adjust to any additions, subtractions, updates, or other changes to your Data sheet.

    Take a look at the attachment to see if it'll suffice:
    Attached Files Attached Files

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,167

    Re: Create multiple tables based on data in a spreadsheet

    Hi ablits,

    I think your problem is described in the following article,

    https://www.mssqltips.com/sqlservert...h-power-query/

    Power Query is a Microsoft Add-In that you can install in excel 2010 or 2013. It is much easier to use than VBA but takes a bit of learning to get what you want.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Registered User
    Join Date
    05-24-2013
    Location
    usa
    MS-Off Ver
    Excel 2013
    Posts
    18

    Re: Create multiple tables based on data in a spreadsheet

    Thank you both very much... I will try to digest that wicked formula and implement into my real data to see if that will work (I'm sure it could, but we have over a dozen "sites" so the spreadsheet could be messy)

    I will also research Power Query!!!

+ 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. Replies: 3
    Last Post: 11-23-2015, 01:38 PM
  2. Creating multiple pivot tables in one worksheet based on different data
    By hwopt in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-08-2014, 10:28 PM
  3. Replies: 0
    Last Post: 02-01-2013, 06:17 AM
  4. Replies: 0
    Last Post: 03-21-2012, 08:58 AM
  5. Replies: 2
    Last Post: 08-15-2011, 03:31 PM
  6. Replies: 3
    Last Post: 08-24-2010, 09:27 AM

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