+ Reply to Thread
Results 1 to 6 of 6

Indirect help..!

  1. #1
    Registered User
    Join Date
    01-08-2015
    Location
    ca
    MS-Off Ver
    2012
    Posts
    3

    Indirect help..!

    Hi,

    I could use some help here..

    I have a worksheet (worksheet A) with a dropdown list of choices (ie; food, drinks, sides).
    On another worksheet (worksheet B), I have a few tables (ie; food--> hot dog, burger; drinks-->pepsi, coke; sides-->fries, rings, etc. etc.)

    So if someone chooses 'food' from worksheet A, I want to be able that the next column will show all the 'food' choices in worksheet B that relate to 'food'. I know that I can do this with the indirect function, but I do not know how to make that function work for all cells in the column..

    Is there another method?
    Thanks!!

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Indirect help..!

    Let's pretend on page 2 Food is in C1, with the list of foods below it. Highlight the list, and in the upper left corner in the field name type FOOD.

    Now that list is named FOOD in Excel's brain thing.

    Back to page 1 you have a dropdown in A1.
    The dropdown in B1 which is contingent will use a formula in the data validation menu =INDIRECT(A1)

    When you choose Food in A1, the list of "Food" will appear in B1.

    Now go back and define the names of each other list by a word they can choose from the first menu.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    01-08-2015
    Location
    ca
    MS-Off Ver
    2012
    Posts
    3

    Re: Indirect help..!

    Awesome! Thanks!

    That was much easier than I thought! )

    One last question please...
    OK, same setup..
    I have a worksheet (worksheet A) with a dropdown list of choices (ie; food, drinks, sides).

    On another worksheet (worksheet B), I have a few tables (ie; food--> hot dog, burger; drinks-->pepsi, coke; sides-->fries, rings, etc. etc.)

    The third column has the total value/amount of items ordered.
    ____________________
    |food | hot dog| 1 |
    |drink| pepsi | 2 |
    |food | burger | 3 |
    |side | fries | 2 |
    |food | hot dog| 4 |
    |drink| pepsi | 3 |
    --------------------

    My question is..
    On worksheet 3, I want to show the total number of items ordered.
    For example, I want to show the total number of 'hot dogs' that were ordered. Can you help me with that formula?

    Thanks!!

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Indirect help..!

    Let's pretend you put the word "Totals" in A1.

    A2:
    =IFERROR(INDEX(Sheet1!$B$1:$B$50,MATCH(0,COUNTIF($A$1:A1,List),0)),"")

    This is an array formula confirmed with Ctrl+Shift+Enter. Then drag down 15-20 cells. This will create a unique list of the things chosen in Column B from sheet1.

    Next, in B2 put =IFERROR(SUMIF(Sheet1!$B$1:$B$50,A2,Sheet1!$C$1:$C$50),"") Drag this formula down as well.

    Now you have unique list of items chosen, and their respective totals.

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Indirect help..!

    Something like

    =SUMIF(WorksheetA!$B$1:$B$25, "Hot dog", WorksheetA!$C$1:$C$25)
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  6. #6
    Registered User
    Join Date
    01-08-2015
    Location
    ca
    MS-Off Ver
    2012
    Posts
    3

    Re: Indirect help..!

    Awesome! Tx sooo much!

+ 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. [SOLVED] INDIRECT Function with multiple sheets - SUMIF, INDIRECT & MATCH
    By DJDRU in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-07-2014, 08:42 AM
  2. Sum of indirect sheet names with multiple cells (SUM, INDIRECT,SHEETNAME in cell)
    By a1b2c3d4e5f6g7h8 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-12-2013, 08:42 AM
  3. Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Mike Barlow in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  4. Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Andy Wiggins in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 06:05 AM
  5. [SOLVED] Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Mike Barlow in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 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