wrt1mea
asked on
I need another excel formula
OK guys...I am back.
I am needing a formula to essentially look at an accounting code and return the name of the project. I have posted a couple of other questions with similiar requests, but everytime I try to manipulate other formulas, I am getting circular reference warnings. See the attached. I was able to essentionally use a Index Match, but am returning two values that I dont need (see the yellow. Is there a way to reference the accounting code without running into a circular reference error?
EX-Exch-Test-3111.xlsx
I am needing a formula to essentially look at an accounting code and return the name of the project. I have posted a couple of other questions with similiar requests, but everytime I try to manipulate other formulas, I am getting circular reference warnings. See the attached. I was able to essentionally use a Index Match, but am returning two values that I dont need (see the yellow. Is there a way to reference the accounting code without running into a circular reference error?
EX-Exch-Test-3111.xlsx
Are you just trying to recreate the projects from column A. Given your example you could just return all the values beginning with "Project" - would that work on your data, or are the project names different in reality?
If you want to do that then you could use this formula in A4
=INDEX(Projects!$A$4:$A$44 ,SMALL(IF( LEFT(Proje cts!$A$4:$ A$44,7)="P roject",RO W(Projects !$A$4:$A$4 4)-ROW(Pro jects!A$4) +1),ROWS(A $4:A4)))
confirmed with CTRL+SHIFT+ENTER and copied down, see attached
regards, barry
26856593.xlsx
If you want to do that then you could use this formula in A4
=INDEX(Projects!$A$4:$A$44
confirmed with CTRL+SHIFT+ENTER and copied down, see attached
regards, barry
26856593.xlsx
ASKER
Barryhoudini,
Returning all the values with "Project" wouldnt work because they arent the real project name. I have replaced production data with example data.
Returning all the values with "Project" wouldnt work because they arent the real project name. I have replaced production data with example data.
ASKER
jppinto,
I cannot add a preceding column. In a nutshell, what I am trying to do is get the project name based off of the Code number. Thats pretty much it. When I try, I get circular error messages.
I cannot add a preceding column. In a nutshell, what I am trying to do is get the project name based off of the Code number. Thats pretty much it. When I try, I get circular error messages.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>what I am trying to do is get the project name based off of the Code number
but the codes don't match do they? In Summary B4 the code is 90010, where does that code appear in Project sheet? If it did appear then you could use a simpe INDEX/MATCH like
=INDEX(Projects!B$4:B$44,M ATCH(B4,Pr ojects!B$4 :B$44,0))
but in your data as it stands that'll give #N/A
barry
but the codes don't match do they? In Summary B4 the code is 90010, where does that code appear in Project sheet? If it did appear then you could use a simpe INDEX/MATCH like
=INDEX(Projects!B$4:B$44,M
but in your data as it stands that'll give #N/A
barry
ASKER
Works great! you really should teach this stuff to people like me...
Thanks again for all the help and support.
Thanks again for all the help and support.
sorry, typo, the INDEX/MATCH would be
=INDEX(Projects!B$4:B$44,MATCH(B4,Projects!B$4:B$4 4,0))
barry
=INDEX(Projects!B$4:B$44,MATCH(B4,Projects!B$4:B$4
barry
jppinto
EX-Exch-Test-3111-1-.xlsx