Solved

Need Help Converting an Excel formula to work on multi sheet

Posted on 2014-11-12
4
60 Views
Last Modified: 2014-11-27
I currently have the following formula (below) which works great but I now need to tweak it to work multi sheet.

=IF(P5="_IM1_/img/samples/flag_green.gif_IM2_Deal Status_IM3_",5,IF(P5="_IM1_/img/samples/flag_yellow.gif_IM2_Deal Status_IM3_",3,IF(P5="_IM1_/img/samples/flag_red.gif_IM2_Deal Status_IM3_",1,1)))

My workbook now has the following tabs

DHC Detal
Scores

before in column AB I had the formula above but now rather than look at the existing sheet line it needs to look at TAB Scores and when the value in DHC Detail column A has a match somewhere in Scores Column A then the formula would look at the value in Scores column E
0
Comment
Question by:Matt Pinkston
  • 2
  • 2
4 Comments
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
ID: 40438370
Change your reference accordingly. E.g. A of Scores is referred by  Scores!A. Because of the space in the other sheet name you will need to enclose the name in single quotes like in 'DHC Detail'!A.
0
 

Author Comment

by:Matt Pinkston
ID: 40438409
Yea I get that but only when DHC Detail Column A has a match on Scores column A
0
 

Author Comment

by:Matt Pinkston
ID: 40438456
So basically

If Scores A = DHC Detail A then

using the e* from Scores
=IF(E5="_IM1_/img/samples/flag_green.gif_IM2_Deal Status_IM3_",5,IF(E5="_IM1_/img/samples/flag_yellow.gif_IM2_Deal Status_IM3_",3,IF(E5="_IM1_/img/samples/flag_red.gif_IM2_Deal Status_IM3_",1,1)))  
 
and if no match column should be left blank
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40438952
Not getting you exactly. Are we on one of this two sheets with the formula?
I reckon "Scores A" means "Scores A on the same row as the formula"?
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

758 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

20 Experts available now in Live!

Get 1:1 Help Now