+ Reply to Thread
Results 1 to 7 of 7

Lookup to populate table headings

  1. #1
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,665

    Lookup to populate table headings

    Hi,

    I have a dashboard in which the user can select All, Branch 1, Branch 2, Branch 3 etc to Branch 6.

    Depending on the selection I need it to pull in data headings to be used on my dashboard – would a lookup do this?

    I have uploaded an example which should explain it more clearly but this is what I want to achieve...

    If branch one or two is selected it would display the following in the blue box on the dashboard (Cell B80):
    INVENTORY ANALYSIS
    Campaign
    Commodity including Downstream finished goods
    Automotive
    Buffer Stock
    Raw Materials, work-in-progress and stores

    If branch 3, 4, 5 or 6 is selected it would display the following in the blue box on the dashboard (Cell B80)
    Raw glass
    Interlayer
    Other raw materials
    Semi-finished - goods in transit
    Semi-finished product awaiting further processing
    Work-in-progress - within production process
    Finished goods
    Trading Goods
    Trading goods in transit
    Maintenance spares
    Other stores
    TOTAL NET STOCK

    If all is selected then the blue box on the “dashboard” sheet would display all of the above (As in the example of the spreadsheet - not displayed here)

    The branch selection is linked to cell D4 on the “Calcs” sheet

    All the lists can be found on the “Calcs” sheet range C97:C114

    Any suggestions?
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,665

    Re: Lookup to populate table headings

    Hi,

    I tried using this IF formula but it didn’t work:
    Please Login or Register  to view this content.
    As I need to set the max number of rows e.g. If 1 then restrict it to 19 rows from cell C123, if 2 or 3 then restrict to 6 rows from cell C158, if other max rows should be 13 from C145

    Can anyone help – I am unsure if this is the best way to achieve what I need.

  3. #3
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,665

    Re: Lookup to populate table headings

    Anyone? ive searched the internet for something similar so if somore could just point me in the right direction I would apprechiate it

  4. #4
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,665

    Re: Lookup to populate table headings

    I have come up with the formula below - does anyone know of a cleaner way?

    Please Login or Register  to view this content.

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Lookup to populate table headings

    Your ranges don't seem to match the Calcs sheet you posted, but one way to shorten it/make if more efficient is to use IFERROR()

    Please Login or Register  to view this content.


    ---------- Post added at 10:36 AM ---------- Previous post was at 10:29 AM ----------

    Actually, here perhaps is a better way still, it allows you to copy formula down without manual intervention to change ROW value...

    Please Login or Register  to view this content.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  6. #6
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,665

    Re: Lookup to populate table headings

    Excellent - thanks NBVC!! The formula where I do have to manually change every line will save me time as previously I manaully changed each one.

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Lookup to populate table headings

    You're welcome. Glad it works for you

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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