+ Reply to Thread
Results 1 to 9 of 9

INDEX/MATCH across multiple sheets using multiple criteria

  1. #1
    Registered User
    Join Date
    05-19-2010
    Location
    London
    MS-Off Ver
    Excel Mac 2008
    Posts
    12

    INDEX/MATCH across multiple sheets using multiple criteria

    I am trying to put together a basic project planner to organise the planning and analysis of various retail campaigns over the period of two years across three departments within the business.

    I have a tab for each campaign, which includes a column for year, week number (calendar week number, used in our business for timings), task, and department responsible.
    Further to the campaign tabs, each of the three departments will have their own tab, to show their own tasks only and the campaign it is linked for.

    Obviously different tasks for different campaigns will take place in the same week, and it is therefore easier for each department to have their own tab, instead of flicking through each campaign’s tab to find out what’s on for this week.

    I am very familiar with V and HLOOKUP functions, but I cannot see it being applied here. I would much prefer formulas to macros. Basically, the “common” data to all tabs which could potentially be used for a VLOOKUP would be the department reference sign. (for business privacy reasons I have labelled the departments in the attached example as “Dept1” “Dept2” and “Dept3”). I assume I need to use INDEX and MATCH, which I am not familiar with.

    Perhaps I could get the Departmental tabs to scan all campaign tabs for their dept reference sign and copy the task across? However, I need the week number and year to follow over too.

    Further to this, there may be difficulties where two departments need to collaborate on a task, which I have indicated as: Dept1 & Dept2 in the “responsible” column.

    Also, in the example “Campaign1” you will see week 48 appears twice, as two important tasks are relevant in that week, sometimes for the same dept.

    I think already that the format of the departmental tabs may need to be changed, (e.g. perhaps week numbers should go across in rows) I am happy for this to happen.
    Can anyone advise how best to go about this?

    Sorry for the long essay, hopefully the attached workbook helps.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: INDEX/MATCH across multiple sheets using multiple criteria

    Hi adamf1987,

    I'd suggest you put all your data in a single table and see if a Pivot Table can be used to display what you want.
    I'd make my fields:
    1. Launch Date
    2. Campaign #
    3. Dept #
    4. Responsible Person
    5. End Date (if needed)

    Then using Pivot Table Filters you could look at and filter by Campaing, Department, Responsible Person
    You could also sort and group by dates.

    I think a single table would be much better than multiple sheets. If Pivot Tables are needed then Auto Filters can also be effective in displaying information.
    This might help for some ideas http://hubpages.com/hub/Filter-an-Ex...Upcoming-Dates
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    05-19-2010
    Location
    London
    MS-Off Ver
    Excel Mac 2008
    Posts
    12

    Re: INDEX/MATCH across multiple sheets using multiple criteria

    thank you for your response.

    I'm not too sure whether this will help me or not.

    I essentially need the departmental tabs to be "untouched", in that no-one needs to edit them.

    If there are changes to campaign deadlines or more tasks involved, then we would go to that campaign's tab and update it. I envisage the departmental tabs to be printed out every monday so each department can see what needs to be done in that week overall, instead of having to look at a million different sheets per campaign.

    PivotTables would not be automatic enough, as I would need to go in after every change and update and re-print something for each department, whereas formulas would drag the data across automatically and each department could just click over to their tab.

  4. #4
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: INDEX/MATCH across multiple sheets using multiple criteria

    Hello,

    See the attached. Hope this helps.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  5. #5
    Registered User
    Join Date
    05-19-2010
    Location
    London
    MS-Off Ver
    Excel Mac 2008
    Posts
    12

    Re: INDEX/MATCH across multiple sheets using multiple criteria

    Wow, that is excellent, thank you very much. I am still trying to work out how you have done that!

    Just to be slightly more picky, you have separated the "Campaigns" into their own "minitable" within the "Dept1" tabs, would it be possible to add another "Campain" range, to say column E on the Dept1 tab, that would list which campaign it was, and then everything for Dept1 would just go down in a list? At the moment the campaigns are still separated.

    Thank you very much for you help so far, I really don;t think I would have been able to get this going myself.

  6. #6
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: INDEX/MATCH across multiple sheets using multiple criteria

    See the attached.

    Hope this work out.

  7. #7
    Registered User
    Join Date
    05-19-2010
    Location
    London
    MS-Off Ver
    Excel Mac 2008
    Posts
    12

    Re: INDEX/MATCH across multiple sheets using multiple criteria

    thank you that's excellent!

    Could you elaborate how I could expand the formulae in the "Campaign" column for when I add more Campaigns? I think I can work out the rest of the formulae by amending the Defined Names you have created for each Campaign, but I can't see how to add further campaigns to the Campaign column.

  8. #8
    Registered User
    Join Date
    05-19-2010
    Location
    London
    MS-Off Ver
    Excel Mac 2008
    Posts
    12

    Re: INDEX/MATCH across multiple sheets using multiple criteria

    Please could somebody help, these tables and forumlae work perfectly, however, I am very unsure how to expand it. I will have about 10 campaigns, not just two, and I cannot see how I can add more campaigns to the formulae.

  9. #9
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: INDEX/MATCH across multiple sheets using multiple criteria

    Adam,

    Consolidating 10 sheets based on condition VBA is the better. hope some one will help you. Attached is an alternate, enter your all sheet name in a range then make a validation list to choose the campaign to show the data. Hope this helps.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1