Solved

I need another excel formula

Posted on 2011-03-01
8
180 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

821 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