Solved

Need Help Converting an Excel formula to work on multi sheet

Posted on 2014-11-12
4
66 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
[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
  • 2
  • 2
4 Comments
 
LVL 70

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 70

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

737 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