Solved

I need another excel formula

Posted on 2011-03-01
8
181 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

861 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