need help in getting right formula

rahiljaved

New member
Joined
Oct 12, 2017
Messages
22
Reaction score
0
Points
0
Excel Version(s)
2013
I need help in the formula.


Really appreciate your help. I've attached an example (below) for what I'm looking for. I have unique IDs for each project but there are multiple project managers attached to same IDs.


ID --------Project ---------Project
-----------Manager 1 -----Manager 2
B250 -----Chris------------ Julia
B291 -----Chris ------------Alex
C951 -----Fiona ------------Julia


Project manager 1 is leading the project and project manager 2 is just assisting.
Now here what I want to do. I want to create a formula that when I choose project manager name lets say "Chris" it should say "Lead" and when i choose project manager "Julia" it should say "Assist".

Keep that in mind that I am already pulling data from a source using the index and small function and its working fine in getting all the other relevant information when I select the project manager example.

when I select Chris it gives me below details.

B250 ----------Project details -------------budget -------------hours--------------- Lead

Here the problem is that same ID (B250) is using by another project manager with different hours and working as an assistant so when I select Julia who is an assistant here what I get.


B250 ----------Project details --------------budget -------------hours -------------Lead (This is the issue because she is not the lead and index function is pulling data from the same row in the dataset).

Hope I have explained it well.
 
Last edited:
I havnt post the link of the work book as I have mentioned the example in my post. The reason is that workbook data is too large and confidential nature.
 
Cut the data down to just what is needed to give you a solution and desensitise it. Then post the sample workbook here.
 
Here is the image of the workbook. I have created a sample filtered the data on same sheet and highlight it in red. I want that when i select Irina it should have me the answer in formula bar as "Team" but when its Chris it should give me answer as "Lead".
Sample.jpg
 
This is the dashboard where i am pulling data from that sheet (pasted above). You can see it shows Irina as "Lead" but she is not a "Lead" in this project she is working as "Team". In dashboard when I select Chris name it also show him "Lead" (Which is correct) but Irina as "Lead" is not correct.
sample2.jpg
 
Sorry - I can't work with an image. I asked you to post the workbook.
 
here you go

Jill Goldie15342A15342OpenLeadN
Chris Katotikidis15342B15342OpenLeadN
Chris Katotikidis15342C15342OpenLeadN
Jessica Wiley1534415344OpenLeadN
Virginia Kalapaca1534715347OpenLeadN
Chris KatotikidisIrina Polo1534915349OpenLeadTeamN
Chris Katotikidis1535115351OpenLeadN
Selby Philip1543115431OpenLeadN
Vedad Hasonovic1543315433OpenLeadN
Selby Philip1543415434OpenLeadN
Kevin Carr1543515435OpenLeadN
Lee Ann Reck1544315443OpenLeadN
Darlene Utarid 1549015490OpenLeadN
Harleen Pabla 1549115491OpenLeadN
Yvonne Monestier1549715497OpenLeadN
Yvonne Monestier1549815498OpenLeadN
 

Attachments

  • Book1.xlsx
    14.5 KB · Views: 12
You have not included formulae. What is the formula that you are trying to use that returns the #VALUE! error in G32?
 
I am using index match in dashboard sheet to collect data from the source sheet.

IFERROR(INDEX('2018 MF Database'!$CQ$1:$CR$596,SMALL(IF('2018 MF Database'!$B$1:$C$596&'2018 MF Database'!$BS$1:$BS$596=Dashboard!$D$4&"Open",ROW('2018 MF Database'!$C$1:$C$596)),ROW()-18),1),"")

But the problem is that its getting data from only 1 column which is PMR Role (Lead or Team) but it doesnt change the column. you know what I mean.
 
sorry just realize that I can't send private msg lol after sending another one. anyway hope I have explain it well here.
 
I don't want any private messages about it, as I explained last time you sent me one. You must keep everything here within this thread, please.

I am sorry - I have run out of time for today. Maybe someone else will be able to pick this up.
 
rahiljaved/ROG, you have a very similar thread here:
https://www.computing.net/answers/office/pulling-data-using-using-name-with-multiple-role/21193.html
Please bear in mind the rules here about cross-posting (you are required to supply links).
For why, see here: http://www.excelguru.ca/content.php?184

If you've cross-posted at other sites beyond that one mentioned above, you need to supply links to those threads too.

I note that there too, potential responders are exasperated by your failure to supply a reasonable workbook. The workbook you supply here has data which bears no relation to the formula you supply in msg#10. There are no data in columns CQ,CR, BS. Sheet names don't exist.

Supply a workbook with 2 sheets, name them according to your formulae, have the formulae relate to the right columns/cells.
As DerbyDad03 says at computing.net: "why should I bust my brain trying to figure out your layout?"
We're guaranteed to guess wrongly, and as a result, any answer you do get will probably be wrong.
 
The OP contacted me YET AGAIN via PM yesterday, despite my asking him not to do so, and to keep everything here in this thread. He told me that it would take him quite some time to cut down his work book to supply a small enough sample to post here, and was reluctant to have to make that effort. I told him that if he was expecting us to make an effort to help him here voluntarily then he would need to be prepared to make an effort, too. Given what I have now seen on the other forum, I doubt that is going to happen. As DerbyDad03 said over there, "I give up".
 
Back
Top