+ Reply to Thread
Results 1 to 6 of 6

Lookup and if statement help

  1. 02-05-2016, 10:22 AM #1
    Excel15
    Excel15 is offline
    Forum Contributor
    Join Date
    06-16-2014
    Location
    Columbia, Maryland
    MS-Off Ver
    2013
    Posts
    134

    Lookup and if statement help

    Okay I have a summary page called "High Priority Items" (attaching an example). This page needs to look up across multiple tabs and pull in only high priority tasks. I was going to use a index(match( formula but am having trouble setting it up. Basically I want to just have a dashboard/summary page of all the high priority items on the different tabs in one place. Any suggestions on how to set this up would be much appreciated! The sheet I've attached has 3 tabs. The first tab is the summary tab for the high priority tasks that I need. The other two tabs have different projects that have a Priority. If that priority is high I need to somehow pull it over to the first tab. This sheet will be constantly updated with new projects.

    Cheers
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-05-2016, 10:35 AM #2
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,444

    Re: Lookup and if statement help

    Given that both sheets have the same layout, as does the summary, it would make more sense to have all the data on one sheet and add a column to identify the "source". Then you don't need the Summary sheet either. You can just filter on priority and/or source and/or any other column (as a further constraint).

    No hassle, no complex formulae, no copying and pasting.

    And you can use a Pivot Table to analyse and compare the data from different sources. It is scaleable to, as you can include other sources, or split Social Media into Twitter, Facebook, LinkedIn, whatever.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 02-05-2016, 10:39 AM #3
    Excel15
    Excel15 is offline
    Forum Contributor
    Join Date
    06-16-2014
    Location
    Columbia, Maryland
    MS-Off Ver
    2013
    Posts
    134

    Re: Lookup and if statement help

    So basically have all the data from the website and social media tabs on one tab and create a column that says whether or not its "Social Media" or "Website". Then just create a pivot table off of that data.
    Reply With QuoteRegister To Reply
  4. 02-05-2016, 10:41 AM #4
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Lookup and if statement help

    That would make the most sense

    or you could use this Arrayed formula

    =IFERROR(IF(COUNTIF('Social Media'!$E:$E,"High")>=ROWS($A$4:$A4), INDEX('Social Media'!A$4:A$100, SMALL(IF('Social Media'!$E$4:$E$100="High", ROW($E$4:$E$100)-ROW($E$4)+1), ROWS($A$4:$A4))), INDEX(Website!A$4:A$100, SMALL(IF(Website!$E$4:$E$100="High", ROW($E$4:$E$100)-ROW($E$4)+1), ROWS($A$4:$A4)))),"")
    I'd try TMS's idea first.
    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
    Reply With QuoteRegister To Reply
  5. 02-05-2016, 11:01 AM #5
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,444

    Re: Lookup and if statement help

    Yep. See the attached file. You need to find somewhere to put your budget data but that probably should be on a Summary sheet anyway.

    Regards, TMS
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 02-05-2016, 12:15 PM #6
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,444

    Re: Lookup and if statement help

    Thanks for the rep
    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. If statement and V Lookup
    By sansan88 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-08-2016, 08:52 AM
  2. LOOKUP and IF Statement?
    By CrocHntr in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-27-2013, 12:19 PM
  3. V lookup with an If statement
    By neb2886 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-10-2013, 12:21 AM
  4. IF statement and lookup
    By mwhiteley in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-18-2013, 02:10 PM
  5. How to v lookup & use if statement?
    By floricita in forum Excel General
    Replies: 5
    Last Post: 09-03-2010, 08:05 AM
  6. IF statement with V LOOKUP
    By bigduke6 in forum Excel General
    Replies: 3
    Last Post: 08-26-2010, 10:11 AM
  7. Lookup/if statement?
    By Connie in forum Excel General
    Replies: 5
    Last Post: 08-16-2005, 09:05 PM

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