Solved

Need Help Converting an Excel formula to work on multi sheet

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

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

Suggested Solutions

Title # Comments Views Activity
Problem with a moving column in Excel 6 43
MS Excel Formula Help 3 35
Power BI date filters 3 13
Formula to run in whole column: 15 13
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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 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.

830 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