IF forumla carrying across tabs for multiple cells and data

needinghelp2765

New Member
Joined
Feb 8, 2022
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi!

I am trying to create a personnel matrix for projects, that will auto-populate the contact details for each tab.

I have created a tab called 'contact list' where the data needs to pull from, a 'live jobs' tab that will act as the matrix to enter the data, and then individual tabs for the projects where I want the data to pull across to.

I am after some help with formulas and function, so if the tab is called 01 it will show in the corresponding cell (already set up), then the project name that corresponds with that number will be pulled across from the the 'live jobs', and then the contact details will be pulled across that correspond to the initials in the matrix.

Spreadsheet is available below.

Any advice would be most welcomed!

Cheers
smile.gif


 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Hi Needinghelp2765,

Are you looking for something like this?

NeedingHelp2765 Personnel Matrix.xlsx
ABC
1
2Project No.01
3Project NameProject 1
4
5Financial & ContractualPerson One
6Positon One
7Phone One
8one@email
9
10Project CoordinationPerson Two
11Postion Two
12Phone Two
13two@email
14
15Site SupervisionPerson Three
16Position Three
17Phone Three
18three@email
01
Cell Formulas
RangeFormula
C2C2=MID(CELL("filename",C2),FIND("]",CELL("filename",C2))+1,255)
C3C3=INDEX('Live Jobs'!$C$2:$C$9999,MATCH($C$2,'Live Jobs'!$B$2:$B$9999,0))
C5,C15,C10C5=INDEX('Contact List'!$B$3:$ZZ$6,1,MATCH(INDEX('Live Jobs'!$D$2:$F$9999,MATCH($C$2,'Live Jobs'!$B$2:$B$9999,0),MATCH(B5,'Live Jobs'!$D$1:$F$1,0)),'Contact List'!$B$2:$ZZ$2,0))
C6,C16,C11C6=INDEX('Contact List'!$B$3:$ZZ$6,2,MATCH(INDEX('Live Jobs'!$D$2:$F$9999,MATCH($C$2,'Live Jobs'!$B$2:$B$9999,0),MATCH(B5,'Live Jobs'!$D$1:$F$1,0)),'Contact List'!$B$2:$ZZ$2,0))
C7,C17,C12C7=INDEX('Contact List'!$B$3:$ZZ$6,3,MATCH(INDEX('Live Jobs'!$D$2:$F$9999,MATCH($C$2,'Live Jobs'!$B$2:$B$9999,0),MATCH(B5,'Live Jobs'!$D$1:$F$1,0)),'Contact List'!$B$2:$ZZ$2,0))
C8,C18,C13C8=INDEX('Contact List'!$B$3:$ZZ$6,4,MATCH(INDEX('Live Jobs'!$D$2:$F$9999,MATCH($C$2,'Live Jobs'!$B$2:$B$9999,0),MATCH(B5,'Live Jobs'!$D$1:$F$1,0)),'Contact List'!$B$2:$ZZ$2,0))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C3Cell Value<0textNO
 
Upvote 0
Solution
This is great, thank you! Exactly what I'm after.

Can you please confirm how to adjust to add more projects and to add more roles?

I'm looking to have 100 projects and 4 roles please.

I can send you my actual spreadsheet if it's easier?

Cheers,
LAura
 
Upvote 0
This is great, thank you! Exactly what I'm after.

Can you please confirm how to adjust to add more projects and to add more roles?

I'm looking to have 100 projects and 4 roles please.

I can send you my actual spreadsheet if it's easier?

Cheers,
LAura
You're welcome.

"add more projects"
In "Live Jobs" add more entries in column B (making sure they match a sheet number) and in column C.
My formulae looks down to row 9,999 so you shouldn't run out of space.

"add more roles"
The mini-sheet below for 01 now looks into "Live Jobs" from F1 to M1, so room for further expansion, and you just enter the new role into "Live Jobs" G1 and into "01" cell B20.

Once you have "01" setup then you can just right-click the tab, Move or Copy, move to end, select create a copy to get all the formulae into the new tab. It will have lots of #N/A errors but once you rename the new tab as 02, 03, etc it will recalculate so you'll only see #N/A errors where you've forgotten an entry into the "Contact List" or "Live Jobs" sheets.

NeedingHelp2765 Personnel Matrix-V2.xlsx
ABC
1
2Project No.01
3Project NameProject 1
4
5Financial & ContractualPerson One
6Positon One
7Phone One
8one@email
9
10Project CoordinationPerson Two
11Postion Two
12Phone Two
13two@email
14
15Site SupervisionPerson Three
16Position Three
17Phone Three
18three@email
19
20 
21 
22 
23 
24
01
Cell Formulas
RangeFormula
C2C2=MID(CELL("filename",C2),FIND("]",CELL("filename",C2))+1,255)
C3C3=INDEX('Live Jobs'!$C$2:$C$9999,MATCH($C$2,'Live Jobs'!$B$2:$B$9999,0))
C5,C20,C15,C10C5=IF(B5="","",INDEX('Contact List'!$B$3:$ZZ$6,1,MATCH(INDEX('Live Jobs'!$D$2:$F$9999,MATCH($C$2,'Live Jobs'!$B$2:$B$9999,0),MATCH(B5,'Live Jobs'!$D$1:$M$1,0)),'Contact List'!$B$2:$ZZ$2,0)))
C6,C21,C16,C11C6=IF(B5="","",INDEX('Contact List'!$B$3:$ZZ$6,2,MATCH(INDEX('Live Jobs'!$D$2:$F$9999,MATCH($C$2,'Live Jobs'!$B$2:$B$9999,0),MATCH(B5,'Live Jobs'!$D$1:$M$1,0)),'Contact List'!$B$2:$ZZ$2,0)))
C7,C22,C17,C12C7=IF(B5="","",INDEX('Contact List'!$B$3:$ZZ$6,3,MATCH(INDEX('Live Jobs'!$D$2:$F$9999,MATCH($C$2,'Live Jobs'!$B$2:$B$9999,0),MATCH(B5,'Live Jobs'!$D$1:$M$1,0)),'Contact List'!$B$2:$ZZ$2,0)))
C8,C23,C18,C13C8=IF(B5="","",INDEX('Contact List'!$B$3:$ZZ$6,4,MATCH(INDEX('Live Jobs'!$D$2:$F$9999,MATCH($C$2,'Live Jobs'!$B$2:$B$9999,0),MATCH(B5,'Live Jobs'!$D$1:$M$1,0)),'Contact List'!$B$2:$ZZ$2,0)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C3Cell Value<0textNO
 
Upvote 0
Thanks for the speedy response!

I have added the data as per your instruction, but unfortunately am still getting N/A for the fourth role, and any projects above the 3 already set up.

Spreadsheet is available via the link below - could you please let me know what I'm doing wrong?

 
Upvote 0
Not all formulae had been changed so I've done that.

You had the Project Number mismatched between text and numeric so lookups were failing. I've changed to numeric throughout but formatted as 00000

I had missed one formula for the new Role columns so I did a global Find and Replace of $D$2:$F$9999 to $D$2:$M$9999 so it uses up to the new G1 in "Live Jobs" for "Administrative Support" but has room for another 6 Roles if needed.

I appreciate you supplying the sheet but you may want to break that link now as it contains personal data, including your email address so I've emailed the complete sheet to you.

Here's the amended sheet for your first project 0003 but with data anonymized.

Cell Formulas
RangeFormula
D2D2=MID(CELL("filename",D2),FIND("]",CELL("filename",D2))+1,255)+0
D3D3=INDEX('Live Jobs'!$C$2:$C$9999,MATCH($D$2,'Live Jobs'!$B$2:$B$9999,0))
D5,D20,D15,D10D5=INDEX('Contact List'!$B$3:$ZZ$6,1,MATCH(INDEX('Live Jobs'!$D$2:$M$9999,MATCH($D$2,'Live Jobs'!$B$2:$B$9999,0),MATCH(B5,'Live Jobs'!$D$1:$M$1,0)),'Contact List'!$B$2:$ZZ$2,0))
D6,D21,D16,D11D6=INDEX('Contact List'!$B$3:$ZZ$6,2,MATCH(INDEX('Live Jobs'!$D$2:$M$9999,MATCH($D$2,'Live Jobs'!$B$2:$B$9999,0),MATCH(B5,'Live Jobs'!$D$1:$M$1,0)),'Contact List'!$B$2:$ZZ$2,0))
D7,D22,D17,D12D7=INDEX('Contact List'!$B$3:$ZZ$6,3,MATCH(INDEX('Live Jobs'!$D$2:$M$9999,MATCH($D$2,'Live Jobs'!$B$2:$B$9999,0),MATCH(B5,'Live Jobs'!$D$1:$M$1,0)),'Contact List'!$B$2:$ZZ$2,0))
D8,D23,D18,D13D8=INDEX('Contact List'!$B$3:$ZZ$6,4,MATCH(INDEX('Live Jobs'!$D$2:$M$9999,MATCH($D$2,'Live Jobs'!$B$2:$B$9999,0),MATCH(B5,'Live Jobs'!$D$1:$M$1,0)),'Contact List'!$B$2:$ZZ$2,0))
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top