Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

I need another excel formula

Posted on 2011-03-01
8
Medium Priority
?
195 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 2000 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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…

636 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