Results 1 to 9 of 9

Thread: Multiple sheets

  1. #1

    Multiple sheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a 7 sheet spreadsheet.
    Sheet 1 is where the formula will be going and it contains a dropdown menu with a list of product names and a dropdown menu with a list of product types. These names and types correspond to different name and types on sheets 2-6
    On sheets 2-6 there is information (eg. name, percentage, code) that needs to go on sheet 1.
    Sheet 7 is the information for the dropdown lists.
    I need to work out how to achieve the following:
    If, Product Name = ABC and Product Type = XYZ, the relevant information with regards to the Product Name and Type from sheets 2-6 automatically populates to Sheet 1.
    Hope this makes sense.
    Cheers

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,087
    Articles
    79
    Blog Entries
    14
    Hi Tessa, and welcome to the forum.

    Do you happen to have a sample of your data that you can attach and upload? It would be easier for us to help if we were working with data that resembled what you use, rather than try to mock something up that may not be correct.

    You can attach a workbook to the thread by clicking the "Go Advanced" button, then look under the text window for the option to upload a file.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    324
    Articles
    0
    This would probably be a nightmare worksheet if it was to house hundreds of volatile formula to maintain, a cleaner neater solution may be to use VBA, however as Ken says we do need a sample, providing a workbook will not only get you your answer quicker but will better illustrate your problem, usually when we can see your data (it can be dummy data but must be of the same type) and your structure it is far easier for us to give you a tailored, workable answer to your query
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  4. #4
    Here is the spreadsheet that I am trying to figure out. If there is any other way that this could be done please let me know.

    Cheers
    Tess
    Attached Files Attached Files

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,282
    Articles
    0
    You would need to name each table in each of those sheets (only the data portions). The names would need to be a combination of exactly what is in B2 and C3 (but without any spaces)...e.g:OnePathOneAnswerInvestmentPortfolioConservative

    Also, it would be recommended to not use Merged cells. Instead just make single columns and widen as necessary to give the correct appearance.

    Then you could simply use formula:

    Code:
    =IF(ISERR(INDEX(INDIRECT(SUBSTITUTE($B$2&$C$3," ","")),ROWS($A$1:$A1),COLUMNS($A$1:A$1))),"",INDEX(INDIRECT(SUBSTITUTE($B$2&$C$3," ","")),ROWS($A$1:$A1),COLUMNS($A$1:A$1)))
    copied down and across the 3 columns.

    If you have XL2007 or later:

    Code:
    =IFERROR(INDEX(INDIRECT(SUBSTITUTE($B$2&$C$3," ","")),ROWS($A$1:$A1),COLUMNS($A$1:A$1)),"")

  6. #6
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    324
    Articles
    0
    NBVC, welcome and nice solution, as you point out it would benefit from a layout change.
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,282
    Articles
    0
    Thanks Simon. Looks like a great place....

    I see you are already a Super Mod... Well, well


  8. #8
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    324
    Articles
    0
    You know me, i get where bleach can't i now admin 5 sites and mod at 1!
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,087
    Articles
    79
    Blog Entries
    14
    Hi NBVC, and welcome!
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •