Conditional Function/Formula Across Worksheets

May 1, 2006

I have 7 sheets named: joe, kelly, meg, nate, dave, tom, sam. To keep it simple, in cell B2 on each sheet there is either an "8" or it's blank. On an 8th sheet I'm trying to determine a formula to use that will display the person or persons if Cell B2=8. So if just meg's B2 has 8 and tom's B2 has 8; the formula would produce meg & tom.

View 8 Replies


ADVERTISEMENT

Conditional IF Function/Formula

Sep 8, 2006

Is it possible to write a formula that will compare the text in two different cells? And if I can do this can I also have that formuls place a numeric value in another cell? An example of what I am trying to do is: If the text in C2 is equal to the text in C3 place a 1 in C4 or if the text in C2 is not equal to the text in C3 place a 0 in C4.

View 2 Replies View Related

Lookup Function/Formula Across Multiple Worksheets

May 14, 2008

This formula works on 1 sheet but we are unable to make it work looking across multiple worksheets? Looking for a match to A3 in column G and wanting to pull the info from column E in the same row.

=IF('2'!G3:G271=A3, LOOKUP(A3,'2'!G3:G271,'2'!E3:E271),0)

View 8 Replies View Related

Conditional PRODUCT Function Formula (return The Product Of A List Of Numbers In A Column)

Sep 30, 2009

I am trying to use a PRODUCT formula to return the product of a list of numbers in a column, between 2 specified dates. The spreadsheet is arranged as follows:

Column BA list of dates
Column C & DNumerical data not used in any calculations
Column EThe numbers to multiply together
Cell A1Start date to be used in the PRODUCTformula
Cell A2End date to be used in the PRODUCT formula

The formula I have to multiply the data in column E, subject to the start and end dates in Column A is:

{=PRODUCT(IF(B15:B1000>A1,IF(B15:B1000<=A2,INDEX(B15:E1000,0,4))))}

This returns zero though works appropriately if I replace “PRODUCT” with a “SUM”.

View 2 Replies View Related

Conditional Formatting: OFFSET Function To Define A Range Inside A SUM Function

Apr 13, 2007

In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?

View 9 Replies View Related

Nesting Ceiling Function In IF Function - Conditional Formatting?

May 13, 2013

I am trying to nest an IF function with a CEILING function. If C10 is < 3.5, make it 3.5, however, if C10 > 3.5, CEILING (C10, 5)

right now it looks like:

If (C10

View 1 Replies View Related

Conditional Sum Across Worksheets

Dec 16, 2006

I have two salespeople (referenced by A1) who I want to total their individual sales. Each worksheet has one salesperson and a total sales price. How do I search multiple worksheets (each having one of two salespeople) in a workbook to total only their sales? ie: cell A1 defines the salesperson and cell M1 has the sale price.

View 9 Replies View Related

Conditional Median Formula Into Conditional Quartile / Percentile?

Apr 15, 2014

Having trouble changing this formula from its current Median to a Quartile or Percentile. The median formula looks like this:

(MEDIAN(IF(DATA!$B$15:$DZ$15=1000,DATA!$B26:$DZ26))) and works perfect.

However I can't figure out how to manipulate it into a percentile keeping the condition of

IF(DATA!$B$15:$DZ$15=1000)

View 2 Replies View Related

Conditional Formatting For Multiple Worksheets?

Oct 23, 2013

I am having an excel workbook consisting of 11 sheets. Can I apply conditional formatting to multiple sheets at the same time?

View 2 Replies View Related

Conditional Formatting - Comparing Worksheets

Dec 5, 2008

I have 2 worksheets - one is a monthly update, the other is last month's data.

I want to highlight the changes on the monthly update sheet using conditional formatting.

I named the columns as ranges on the "last month" worksheet, and then used =Match(A1,Jan,0) (wheras Jan is the range on the "last month" worksheet where A1 resides)

What formula do you use to say "if A1 does NOT match a value in range "jan", then format it"?

View 9 Replies View Related

Search Worksheets, Conditional Counting?

Mar 9, 2007

My workbook is made up of a number of worksheets, A master sheet, which creates new sheets for each work order I enter. Therefore, eventually I will end up with a large number of worksheets that I will have to sort through to find what I need. I put in a command button to allow me to search and hide all of the worksheets that do not match the the work order number I entered. However, if no sheets contain a matching number, then all sheets will be hidden, except for the master sheet. I want to add an exception that pops open a message box if no matches were found and stops the code. I was thinking of doing a conditional count to count all worksheets that match the criteria. If the count = 0, then the message box will open.

Private Sub SearchButton_Click()
Application. ScreenUpdating = False
Dim ws As Worksheet
Dim Search As String
Search = InputBox("Enter work order", "Search Archives", "")
For Each ws In ActiveWorkbook.Worksheets
If ws. Range("D4") <> Search And ws.Name <> "Master" Then
ws.Visible = xlSheetHidden
End If
Next ws
Application.ScreenUpdating = True
End Sub

View 2 Replies View Related

Conditional Summary From Multiple Worksheets

Feb 12, 2008

In the attached WB I'm looking for some formula (for cells A6:C6 in the brown table) to summaries cells A2, in the different sheets, as per a criteria, located in cells A5:C5 in the brown table.

The criterias (A, B, and C) should check cells A1 of each sheet.

I would also like to know how to handle sheets named A, B, C, D, E instead of Numbers in such formulas.

So far for my question.

In addition - I tried some " Array Formulas" in order to understand the concept of solving such a problem - and came up with some "weird" results as you will see in the attached WB. Any additional word will be superfluous...

View 9 Replies View Related

Conditional Formatting On Numerous Worksheets At One Time

Nov 2, 2011

Is it possible to perform conditional formatting on numerous worksheets at one time. I have about 50 worksheets that I would like to perform the same conditional format on. Is there a way to do this.

View 5 Replies View Related

Conditional Copying Of Columns Between Worksheets/Workbooks

Jun 20, 2006

i want to copy data from one excel workbook to other (or ever between diff sheet wud do ) on basis of search on a complete column by picking data one by one from other column & on finding the exact match copy 4 columns form one workbook(or sheet) to other. this has to be done 4 a bulk of data (1000+) so manually is next to impossible.

View 2 Replies View Related

Conditional Formatting Highlighting Data That Matches On 2 Different Worksheets?

Jul 17, 2012

I have 2 worksheets. One has locations with numbers, example:

275 Location 1
276 Location 2

I have all my locations on the second sheet, but in 2 different columns, listed with numbers only, example:
271 275
272 300

I have 2 scenarios I need help with.

1st:
If any of the numbers on sheet 1 match the numbers in column 1 on sheet 2, highlight the number on sheet 1 in green If any of the numbers on sheet 1 match the numbers in column 2 on sheet 2, highlight the number on sheet 1 in red

2nd:
If the number on sheet 2 matches any number on sheet 1, highlight green

I want these to apply to all the cells that have numbers (it could apply to all cells I guess as it should ignore it if it doesn't match, I would assume)

View 7 Replies View Related

Conditional Part Cell Lookup Across Multiple Worksheets

Feb 22, 2008

We have the following formula in our timesheets. Basically when we enter a Job number in one cell in say Tab1, it will return the matching project name in a different cell that we have next to the project number. The job number match is done in the tab named "ProjectSchedule", where all the details of the project are listed. We need to extend the match range to include another tab named "CustomSchedule", but I can't figure out how to make the first formula search jobs from both tabs.

Current working formula:

=IF(C7="","",INDEX(ProjectSchedule!$C$3:$C$202,MATCH(C7 & "*",ProjectSchedule!$B$3:$B$202,0)))

What I'm trying to accomplish, but it not working is something like this:

=IF(C7="","",INDEX(ProjectSchedule!$C$3:$C$202&CustomSchedule!$C$3:$C$202,MATCH(C7 & "*",ProjectSchedule!$B$3:$B$202&CustomSchedule!$B$3:$B$202,0)))

View 9 Replies View Related

Conditional Formatting With INDEX/MATCH Functions On Seperate Worksheets.

Apr 16, 2009

I know there are many posts concerning this, but after scouring, I couldn't find one that fit my situation. I have a total of six worksheets, I am only concerned with two worksheets.

Worksheet (functions!)

This one has a list of numbers formatted as general. (Column G)

Is actually a formula/macro that outputs a number... (didn't know if this mattered?)

View 6 Replies View Related

Search Function Across Multiple Worksheets?

Apr 24, 2014

I have 6 worksheets with data. Column C of every worksheet is filled with unique Tracking Numbers.

I want to create a 7th worksheet with a search function. Users will be able to type in a Tracking Number (on cell A2), and information associated with that Tracking Number (from Columns A, D, E, F and G of worksheets 1-6) will show up beneath cell A2 on cells A4, B4, C4, D4, and E4.

View 3 Replies View Related

Vlookup In A Function To Automate Worksheets

Jun 23, 2009

i want to do a vlookup in a function to automate my worksheets. im trying to grasp how to do it.

View 4 Replies View Related

Using Countif Function For Multiple Worksheets

Jan 28, 2010

I am trying to use countif to count the number of times a unique items occurs in multiple worksheets.

For example, I want to count number of times "ITEM1" occurs in row 1 of sheets1, sheets2, sheets3, sheet4, etc. It may look like this:

Sheet1 = 4 entries
Sheet2 = 22 entries
Sheet3 = 5 entries
Sheet4 = 10 entries

So the entire count would be 41 total.

View 5 Replies View Related

Index-match Function Across Different Worksheets?

Mar 31, 2012

I have different worksheets to contains information from different years. To make it comphrensve, I have created a mastersheet which contains all the info across the worksheets. And once I make an entry in the worksheet, I hope the mastersheet can be updated automatically. the function I set is
INDEX('2011-12'!K$4:K$7,MATCH(Master!$B5,'2011-12'!$B$4:$B$7,0),1).

Yet, my problem is..... once I perform sorting in the mastersheet, the cells are wrongly matched. So, I just wonder how I can revise the function?

View 2 Replies View Related

INDEX And MATCH Function Across Two Worksheets

Apr 5, 2013

I have two worksheets, named as follows:

Worksheet1: SUMMARY
Worksheet2: ART 2013

'ART 2013' contains a list of staff numbers in column C16:C752 and a list of dates in column M16:M752 (formatted as "mm-yyyy")

Within 'SUMMARY' cell G12 contains the date 04-2013.

What I would like to do is achieve a function that will look at 'ART 2013' columns C and M in and where the date in Column M is equal to cell G12 (04-2013) of the 'SUMMARY' worksheet excel will return the Staff Number in 'ART 2013' column C.

View 7 Replies View Related

How Can A Command Button Function For Two Worksheets

Jul 9, 2008

I've a command button on one worksheet (named "Front") that when clicked it "resets" a large number of cells back to "."

I've ALSO a command button on another worksheet (named "Back") that when clicked it also "resets" a large number of cells back to "."

Both are embedded on their own VBA tabs.

Both work exactly as needed.

No problem.

Is there a way I can move the coding from both to the "This Workbook" VBA tab, leave the one command button on the "Front" worksheet, and eliminate the one on the back?

I'm kinda guessing that this will require my rewording the
"(Range("C5").Formula = ".")"
(example line of code for the first cell it resets on the "front" tab) to something specifically referencing the "Front" worksheet, and the same for the ones on the "Back", but I could be wrong.

View 9 Replies View Related

Large Function For Multiple Worksheets

May 6, 2007

I am trying to use the 'large' function to input data from multiple spreadsheets, but am uncertain how to do so other than to use the same rows/columns in each and to use only continuous worksheets (that is, I want to enter data from worksheet 1,3,5 and different columns in each, for example).

View 4 Replies View Related

Union Function For Multiple Worksheets

Jun 1, 2007

I have two worksheets sheet1 & sheet2 in Workbook3 both get data from two different workbook1 & workbook2 using Microsoft Queries. Column Headers are same but contentes and no. of rows are different in these sheets. I would like to combine data from these sheets into sheet3 of Workbook3 so that i can create pivot table on that. I tried using Union Method, But it gave me "Run time error '1004'. Method 'Union' of object '_Application' failed in the following line

Set myMultipleRange = Application.Union(r1, r2)

Union method works fine when combining ranges from the same worksheet.

Multiple consolidation in Pivot table is not desirable to me as i have to group data using two columns and i could not get what i wanted using two page fields.

I have created named ranges for each sheet data. Is there an alternative to Union method for combining these named ranges from two worksheets into third sheet through VB code?

View 5 Replies View Related

Conditional IF Function By Name

Dec 4, 2008

I have this spreadsheet I'm making for Christmas to keep track of my wifes and my purchases. I'm having problem with a formula tho and I'm not sure how to go about this. Basically I have a "name" column. As you will see. At the bottom I have "total spent on cristina" "total spent on jon"

I need like an "IF" condition that will look at that name column and say, "if name is Jon" add up the amounts in the "total cost" column according to what Jon spent. If name is cristina, add up her amounts.

View 3 Replies View Related

Function Works In VBA But Does Not Work Inside Worksheets

Jun 21, 2008

i have written a code in VBA to interpolate the value of Y0 corresponding to X0 using a set of (X,Y) points. (I have written this in module1) . This function works when i call it through a Sub or another Function in VBA. but it does not work when I try to use it as a function in my excel worksheets (when I type : =interpolate(A1:A10,B1:B10,30) .... 30 is an arbitrary value). in this case I get #value! error

The function is known in within the worksheets because when I start typing its name, the Auto Name Complete feature of excel, finds this function.

I am new to VAB for excel. Please give me a hint to see my mistakes of if something is missing inside my code.

PHP
Public Function Interpolate(ByRef X() As Double, ByRef Y() As Double, ByRef X0 As Double) As Double    Dim I As Integer, Slope As Double, NData As Integer        NData = UBound(X)    For I = 1 To UBound(X) - 1        If (X(I) = X0) Then            Interpolate = Y(I)            Exit Function        ElseIf (X0 < ListMax(X(I), X(I + 1)) And X0 > ListMin(X(I), X(I + 1))) Then            Slope = (Y(I) - Y(I + 1)) / (X(I) - X(I + 1))            Interpolate = Y(I + 1) + Slope * (X0 - X(I + 1))            Exit Function        End If    Next I        End FunctionPublic Function ListMax(ParamArray ListItems() As Variant)        Dim I As Integer        ListMax = ListItems(0)        For I = 0 To UBound(ListItems())            If ListItems(I) > ListMax Then ListMax = ListItems(I)        Next IEnd FunctionPublic Function ListMin(ParamArray ListItems() As Variant)        Dim I As Integer        ListMin = ListItems(0)        For I = 0 To UBound(ListItems())            If ListItems(I) < ListMin Then ListMin = ListItems(I)        Next IEnd Function 

View 10 Replies View Related

Matching Names On Different Worksheets With Function To Add Abbreviations

Jan 12, 2010

writing a macro script. I want to find matching names on two diffferent worksheets. More specifically I need the ability to match if I apply a abbreviation(s) to worksheet one in order to get matches in worksheet two.

e.g Company = Co. or
Insurance = Ins

Please see my attached excel document for an example.

I have over 27000 lines so doing this manually will take forever!

Ideally I would like this to be a macro script that I can edit to add as many different abbreviations as required. The attached document will make my task a lot clear.

Unfortunately I don't have any starting code as I am not an excel/visual basic programmer, But i hope somebody will be able to help me out here.

The reason I need to do it this way is because I have been given a file from a partner company that uses different abbreviations to the ones we use in my company. I want to see if the companies in the list I have been given already exist in out company database, therefore we need a suitable matching procedure.

View 10 Replies View Related

Function To Copy Data From Worksheets To Another File

Mar 20, 2007

How do I create code that will copy and paste 30 rows of data

Copy from filename "Record1", worksheet name "CA3M", in column A and B, and Paste data in filename "Record2", worksheet name "CA3M"

- Column A 30 rows of data paste into A20:A49 of the new file and worksheet

- Column B 30 rows of data paste into E20:E49 of the new file and worksheet, prior to pasting these results remove all dashes "-" eg.0-0-1 = 001

The location of the 30 rows of data to be copied changes, so the code should always look for the following information in the row before and after the 30 rows to copy in column A and B:

- Find the 30 rows of data in Column A and B where the first row immediately follows the row with the cell result "Account Date" and the last row is immediately before the row with the cell result "Previous Page Page".

Also, I will probably have to update the code to repeat this function for several other worksheets within these 2 files after my database is completely built.

View 12 Replies View Related

Conditional Formatting (max If Function)

Feb 8, 2014

I would like to use conditional formatting to highlight the max value if the constraints are not violated.

For example,
Row 91 - Profit
Row 94 - Constraint A (750<=A<=855)
Row 95 - Constraint B (12<=B<=17)
So, I tried to type this in conditional formatting but it didn't work ><
=MAX(IF(AND(B94>=750,B94<=855,B95>=12,B95<=17),$B$91:$Q$91,0))

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved