Solved

Need Help Converting an Excel formula to work on multi sheet

Posted on 2014-11-12
4
63 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 69

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 69

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

ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

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…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

777 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