Solved

I need another excel formula

Posted on 2011-03-01
8
183 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:wrt1mea
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 35011996
Not sure what you're trying to do here. If you want for example to put a code on a cell and get the corresponding project name of that code, please check the attached example (yellow cell) to see if this is what you want.

jppinto
EX-Exch-Test-3111-1-.xlsx
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35012029
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(Projects!$A$4:$A$44,7)="Project",ROW(Projects!$A$4:$A$44)-ROW(Projects!A$4)+1),ROWS(A$4:A4)))

confirmed with CTRL+SHIFT+ENTER and copied down, see attached

regards, barry
26856593.xlsx
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 35012106
Barryhoudini,

Returning all the values with "Project" wouldnt work because they arent the real project name. I have replaced production data with example data.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:wrt1mea
ID: 35012117
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.
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 35012148
OK, then perhaps you can return every column A value (from A4 on) with an entry in column B, so that would make the formula like this

=IFERROR(INDEX(Projects!$A$4:$A$44,SMALL(IF(Projects!$B$4:$B$44<>"",ROW(Projects!$A$4:$A$44)-ROW(Projects!A$4)+1),ROWS(A$4:A4))),"")

still confirmed with CTRL+SHIFT+ENTER

barry
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35012187
>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,MATCH(B4,Projects!B$4:B$44,0))

but in your data as it stands that'll give #N/A

barry
0
 
LVL 1

Author Closing Comment

by:wrt1mea
ID: 35012194
Works great! you really should teach this stuff to people like me...

Thanks again for all the help and support.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35012195
sorry, typo, the INDEX/MATCH would be

=INDEX(Projects!B$4:B$44,MATCH(B4,Projects!B$4:B$44,0))

barry
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question