+ Reply to Thread
Results 1 to 4 of 4

Create a master list from dynamics lists in multiple sheets.

Hybrid View

  1. 01-28-2016, 10:56 AM #1
    excobra
    excobra is offline
    Registered User
    Join Date
    06-03-2013
    Location
    Riyadh
    MS-Off Ver
    Office 365 Subscription
    Posts
    47

    Create a master list from dynamics lists in multiple sheets.

    Here is the scenario.

    I have 20 different branches like e.g. Branch 01 Branch 02 ..... Branch 20. These branches are divided into 4 categories (Flagship,Standard, Mall & kiosk). A branch can be in one category at a time. Like for example Branch 01 can only be Flagship. If it changes category then it will be moved to respective sheet automatically.

    Now i want to create a master table with all the branches and their respective values. Right now i am doing this manually. I have attached sample sheet. Bear in mind a brach can be classified from one category to other what i mean to say is input sheets are dynamic. So out put master sheet has to be adjusted accordingly.

    Example file is attached
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-28-2016, 11:44 AM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Create a master list from dynamics lists in multiple sheets.

    1) Create a key to initially spot each branch on its current sheet. In A7, then copied down:

    =IF(ISNUMBER(MATCH(B7, Flasgship!C:C, 0)), "Flasgship", IF(ISNUMBER(MATCH(B7, Standard!C:C, 0)), "Standard", IF(ISNUMBER(MATCH(B7, Mall!C:C, 0)), "Mall", IF(ISNUMBER(MATCH(B7, Kiosk!C:C, 0)), "Kiosk"))))


    2) Create the unique formulas needed for INDIRECT matches across row 1, then copy those formulas down to get the other row results, too.

    D7: =INDEX(INDIRECT("'" & $A7 & "'!E:E"), MATCH($B7, INDIRECT("'" & $A7 & "'!C:C"), 0))
    E7: =INDEX(INDIRECT("'" & $A7 & "'!F:F"), MATCH($B7, INDIRECT("'" & $A7 & "'!C:C"), 0))
    F7: =INDEX(INDIRECT("'" & $A7 & "'!G:G"), MATCH($B7, INDIRECT("'" & $A7 & "'!C:C"), 0))
    G7: =INDEX(INDIRECT("'" & $A7 & "'!H:H"), MATCH($B7, INDIRECT("'" & $A7 & "'!C:C"), 0))
    H7: =INDEX(INDIRECT("'" & $A7 & "'!I:I"), MATCH($B7, INDIRECT("'" & $A7 & "'!C:C"), 0))
    I7: =INDEX(INDIRECT("'" & $A7 & "'!J:J"), MATCH($B7, INDIRECT("'" & $A7 & "'!C:C"), 0))
    J7: =INDEX(INDIRECT("'" & $A7 & "'!K:K"), MATCH($B7, INDIRECT("'" & $A7 & "'!C:C"), 0))
    etc...
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 01-28-2016, 01:42 PM #3
    excobra
    excobra is offline
    Registered User
    Join Date
    06-03-2013
    Location
    Riyadh
    MS-Off Ver
    Office 365 Subscription
    Posts
    47

    Re: Create a master list from dynamics lists in multiple sheets.

    Hi JBeaucaire

    Thanks for the help. i applied the same formula on the actual sheet. First formula is ok and working but for second formula it is giving me a reference error. I have modified the first formula in the following way.
    =IF(ISNUMBER(MATCH(B10,'Capex Flagship'!$B$421:$B$429,0)),Flagship,IF(ISNUMBER(MATCH(B10,'Capex Standard'!$B$421:$B$429,0)),BT_Stand,IF(ISNUMBER(MATCH(B10,'Capex Mall'!$B$421:$B$429,0)),BT_Mall,IF(ISNUMBER(MATCH(B10,'Capex Metro'!$B$421:$B$429,0)),BT_Metro,IF(ISNUMBER(MATCH(B10,'Capex Airport'!$B$421:$B$429,0)),BT_Airport,IF(ISNUMBER(MATCH(B10,'Capex Kiosk'!$B$421:$B$429,0)),BT_Kiosk,IF(ISNUMBER(MATCH(B10,'Capex Other'!$B$421:$B$429,0)),BT_Other)))))))
    My indirect formual looks like this.

    =INDEX(INDIRECT(""&$A10&"'!I:I"),MATCH(B10,INDIRECT(""&A10&"'!G:G"),0))
    Can you spot error?

    Screen Shot 2016-01-28 at 8.38.32 PM.png
    Reply With QuoteRegister To Reply
  4. 01-28-2016, 02:31 PM #4
    excobra
    excobra is offline
    Registered User
    Join Date
    06-03-2013
    Location
    Riyadh
    MS-Off Ver
    Office 365 Subscription
    Posts
    47

    Re: Create a master list from dynamics lists in multiple sheets.

    i figured i was using the wrong sheet names. Thanks for the help.It is working now.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro create sheets from master and copy data from list to new worksheet
    By su2009 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-20-2015, 10:36 AM
  2. [SOLVED] How to create a master list worksheet from lists of names on several worksheets
    By ESUCP Jamie in forum Excel - New Users/Basics
    Replies: 8
    Last Post: 07-20-2015, 03:03 PM
  3. [SOLVED] Consolidating multiple sheets in master tab and create summary sheet
    By Farida in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-14-2014, 02:41 PM
  4. Filter data from a master to multiple sheets + appending lists
    By nomwich in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 11-15-2013, 12:42 PM
  5. Multiple Data Validation Lists based on a master list, but same info cannot be selected
    By nharwell in forum Excel Formulas & Functions
    Replies: 28
    Last Post: 08-15-2013, 09:38 AM
  6. [SOLVED] Create master list from multiple sheets in the same format
    By malmandras in forum Excel General
    Replies: 1
    Last Post: 05-09-2013, 01:59 PM
  7. Create Multiple Sheets from master sheet
    By neilshaw in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 05-08-2011, 06:10 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1