Using Excel worksheets as data source for another worksheet

Question

I am creating a resource management excel to track the resources, projects, allocation of resources to the projects.

Resources has following columns: Name, Age, Profile, Country, Billing

Project has following columns: Portfolio Name, Project Name, Product Owner, Scrum Master

To manage the information efficiently I want to add resources in one worksheet, Projects in another and then want to add a third worksheet "Allocation" to use the data from Resource and Project worksheet.

How can I link "Resources" and "Projects" worksheet to the "Allocation" worksheet? so I can select Country/City and then all resources from that country/city will be listed in the drop down?

Edit: I can do this using VBA but looking for inbuilt features of Excel to make this happen.

Answer 1

Assuming a Resource Master like this:

you can create a table in Project Allocation like this:

with these formula:

CELL	FORMULA/VALUE
A2	Your input
B2	=IF(COUNTIF('Resource Master'!D:D,A2)>COUNTA(C:C)-1,"Alert: " & COUNTIF('Resource Master'!D:D,A2)-COUNTA(C:C)+1 & " results not included","")
C2	=IFERROR(INDEX('Resource Master'!$A:$E,AGGREGATE(15,6,1/($A$2='Resource Master'!$D:$D)*ROW('Resource Master'!$D:$D),ROW(C2)-ROW($C$2)+1),COLUMN(C2)-COLUMN($C$2)+1),"")

Drag the C2 cell to cover the rest of the table. The B2 cell checks if the (whole) column C contains less result than expected.

---

FORMULAS EXPLANATIONS

CELL B2

EVOLUTION:

1) Mission statement

CHECK HOW MANY (IF ANY) RESULTS ARE MISSING

2) Breaking up the mission statement

=IF(MISSING RESULT COUNT, ALERT, NO ALERT)

3) Breaking up MISSING RESULT COUNT

=IF(MATCHES FOUND IN RESOURCE MASTER > REPORTED RESULTS, ALERT, NO ALERT)

4) Breaking up MATCHES FOUND IN RESOURCE MASTER

=IF(COUNTIF(RANGE WITH THE COUNTRY IN RESOURCE MASTER, CRITERIA) > REPORTED RESULTS, ALERT, NO ALERT)

5) Specifying RANGE WITH THE COUNTRY IN RESOURCE MASTER

=IF(COUNTIF('Resource Master'!D:D, CRITERIA) > REPORTED RESULTS, ALERT, NO ALERT)

6) Specifying CRITERIA

=IF(COUNTIF('Resource Master'!D:D,A2) > REPORTED RESULTS, ALERT, NO ALERT)

7) Breaking up REPORTED RESULTS

=IF(COUNTIF('Resource Master'!D:D,A2) > COLUMN C VALUES COUNT - HEADER COUNT, ALERT, NO ALERT)

8) Translating COLUMN C VALUES COUNT

=IF(COUNTIF('Resource Master'!D:D,A2)>COUNTA(C:C) - HEADER COUNT, ALERT, NO ALERT)

9) Specifying HEADER COUNT

=IF(COUNTIF('Resource Master'!D:D,A2)>COUNTA(C:C)-1, ALERT, NO ALERT)'

10) Breaking up ALERT

=IF(COUNTIF('Resource Master'!D:D,A2)>COUNTA(C:C)-1, "Alert: " & MATCHES FOUND IN RESOURCE MASTER - REPORTED RESULTS & " results not included", NO ALERT)

11) Breaking up MATCHES FOUND IN RESOURCE MASTER - REPORTED RESULTS (similar but not equal to points from 3 to 9)

=IF(COUNTIF('Resource Master'!D:D,A2)>COUNTA(C:C)-1,"Alert: " & COUNTIF('Resource Master'!D:D,A2)-COUNTA(C:C)+1 & " results not included", NO ALERT)

12) Specifying NO ALERT

=IF(COUNTIF('Resource Master'!D:D,A2)>COUNTA(C:C)-1,"Alert: " & COUNTIF('Resource Master'!D:D,A2)-COUNTA(C:C)+1 & " results not included","")

EXPLODED VERSION:

=IF(
    COUNTIF(
            'Resource Master'!D:D,
            A2
           )
    >COUNTA(
            C:C
           )
    -1,
    "Alert: " & COUNTIF(
                        'Resource Master'!D:D,
                        A2
                       )
                -COUNTA(
                        C:C
                       )
                +1 & " results not included",
    ""
   )

---

CELL C2

EVOLUTION:

1) Mission statement

REPORT THE LIST FILTERED BY CRITERIA

2) Using an IFERROR in case the formula returns error (possible cause: result overflow)

=IFERROR(FORMULA FOR THE FILTERED LIST,"")

3) Breaking up FORMULA FOR THE FILTERED LIST into an INDEX function to return a single data in each cell (1 result in every row)

=IFERROR(INDEX(DATA RANGE IN RESOURCE MASTER, RESULT'S ROW IN RESOURCE MASTER, DYNAMIC COLUMN COUNTER),"")

4) Specifiying DATA RANGE IN RESOURCE MASTER

=IFERROR(INDEX('Resource Master'!$A:$E, RESULT'S ROW IN RESOURCE MASTER, DYNAMIC COLUMN COUNTER),"")

5) Breaking up RESULT'S ROW IN RESOURCE MASTER into an AGGREGATE function to filter the data list in Resource Master

=IFERROR(INDEX('Resource Master'!$A:$E,AGGREGATE( SMALL , NO ERRORS, FILTER, DYNAMIC ROW COUNTER), DYNAMIC COLUMN COUNTER),"")

6) Specifying SMALL and NO ERRORS (aggreagate function argument)

=IFERROR(INDEX('Resource Master'!$A:$E,AGGREGATE(15,6, FILTER, DYNAMIC ROW COUNTER), DYNAMIC COLUMN COUNTER),"")

7) Breaking up FILTER

=IFERROR(INDEX('Resource Master'!$A:$E,AGGREGATE(15,6, LIST WITH ERRORS AND RESULTS * ROW COUNTER LIST, DYNAMIC ROW COUNTER), DYNAMIC COLUMN COUNTER),"")

8) Breaking up LIST WITH ERRORS AND RESULT

=IFERROR(INDEX('Resource Master'!$A:$E,AGGREGATE(15,6,1/(CRITERIA = RANGE WITH THE COUNTRY IN RESOURCE MASTER) * ROW COUNTER LIST, DYNAMIC ROW COUNTER), DYNAMIC COLUMN COUNTER),"")

9) Specifying CRITERIA

=IFERROR(INDEX('Resource Master'!$A:$E,AGGREGATE(15,6,1/($A$2 = RANGE WITH THE COUNTRY IN RESOURCE MASTER) * ROW COUNTER LIST, DYNAMIC ROW COUNTER), DYNAMIC COLUMN COUNTER),"")

10) Specifying RANGE WITH THE COUNTRY IN RESOURCE MASTER

=IFERROR(INDEX('Resource Master'!$A:$E,AGGREGATE(15,6,1/($A$2='Resource Master'!$D:$D) * ROW COUNTER LIST, DYNAMIC ROW COUNTER), DYNAMIC COLUMN COUNTER),"")

11) Breaking up ROW COUNTER LIST and specifying RANGE WITH THE COUNTRY IN RESOURCE MASTER

=IFERROR(INDEX('Resource Master'!$A:$E,AGGREGATE(15,6,1/($A$2='Resource Master'!$D:$D)*ROW('Resource Master'!$D:$D), DYNAMIC ROW COUNTER), DYNAMIC COLUMN COUNTER),"")

12) Breaking up DYNAMIC ROW COUNTER and DYNAMIC COLUMN COUNTER into self-referencing row and column calculation; this allows the formula to be dragged to cover multiple results and all their data

=IFERROR(INDEX('Resource Master'!$A:$E,AGGREGATE(15,6,1/($A$2='Resource Master'!$D:$D)*ROW('Resource Master'!$D:$D),ROW(C2)-ROW($C$2)+1),COLUMN(C2)-COLUMN($C$2)+1),"")

EXPLODED VERSION:

=IFERROR(
         INDEX(
               'Resource Master'!$A:$E,
                AGGREGATE(
                          15,
                          6,
                          1/(
                             $A$2='Resource Master'!$D:$D
                            )
                          *ROW(
                               'Resource Master'!$D:$D
                              ),
                          ROW(
                              C2
                             )
                          -ROW(
                               $C$2
                              )
                          +1
                         ),
                COLUMN(
                       C2
                      )
                -COLUMN(
                        $C$2
                       )
                +1
              ),
         ""
        )

Answer 2

Have you tried using Data Validation to create dropdown lists?

1. Select the column or cell that you want to have dropdown lists for selection.
2. Click the Data menu.
3. Click Data Validation.
4. Select List in Allow:.
5. Click the up arrow and in Source, then select the values that you want to appear in the dropdown list then click the down arrow button, or enter the range.
6. Click OK.
7. To remove the dropdown list from a header, select the cell, click Data Validation, click Clear All, then click OK.

Answer 3

I think you are using the wrong tool for the task. Have you heard about MS Project? Has built-in features to handle all that and more and also talks the management lingo.

Anyway if you still want to stick with Excel, you could use some named ranges and lookups mixed with indirect and offsets inside array R1C1 formulas.

More modern approach would be to use data model/power query after defining these tables.

But most important than any of these suggestions is you need to state your model more precisely first, you must define which are the primary and foreign keys for your 'tables' so you can work after. In a SQL statement it would be the JOIN xxx ON yyy=zzz stuff...