Dynamic List / VLOOKUP across multiple sheets

[pmcgrath92]

Hi Everyone,

I want to create a dynamic "to-do" list in a main/front sheet, which is based upon cell value from data validation in multiple other sheets. Let's say I have sheets A and B - which each have a list of objectives and a "Status" (e.g. Unactioned/In Progress) based on Data Validation. Then there is a main sheet/to-do list, which looks at the status of objectives across ALL sheets (in this case, A and B); subsequently listing them all objectives in a dynamic fashion. Some pictures as examples below:

Sheet A - with the list of Task/Objectives in Column C; and Status in Column E.

Sheet B - same layout as Sheet A

TO-DO Sheet - Which lists all of the Unactioned and In Progress tasks from Sheets A and B underneath.

I.e. in Sheet TO DO I would like B3:B6 (or longer) to list all of Sheet A C6:C11 and Sheet B C6:C9 based on Sheet A E6:E11 and Sheet B E6:E9 = "Unactioned"
(& then the same from TO DO B12:14 but based on the Status = "In Progress").

I hope that's a good enough explanation, but happy to provide any more details.

Thanks in advance!

Paul

 

[C Moore]

Have you considered PowerQuery? It's ability to append and filter could handle this.

 

[pmcgrath92]

Have you considered PowerQuery? It's ability to append and filter could handle this.

Haven't ever heard of this before, but after a quick search that could be a better solution than formulae or VBA. I'll look into it, thank you.

Do you know if it's an add-on which you have to download and install; or is available by standard? (The former may be an issue for me).

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

PQ is built in from 2016 (I think) & is on the Data tab under Get & Transform.

 

[pmcgrath92]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

PQ is built in from 2016 (I think) & is on the Data tab under Get & Transform.

I'm running Excel 2016, apologies, would have been wise to mention (as you pointed out). I'll have a look into PQ then, thanks.