Solved

I need another excel formula

Posted on 2011-03-01
8
174 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
  • 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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,…
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now