# Design If Statement That Refers To A Range Of Cells On Calculation Sheet

Mar 21, 2014
I'm trying to design an if statement that refers to a range of cells on a calculation sheet to perform one calculation on TRUE; another on FALSE

=IF(A22='Calculation Sheet'!A27:A36,B22*100/16,B22*100/8)

When I view the formula in Function Arguments window, Value_if_true = 1043.75 & Value_if_false = 2087.5 <- these are correct

IF Func Arg.jpg

When I view the cell in the spreadsheet, system displays #VALUE!

When I view Evaluate Formula window, it seems Excel doesn't like the text.

IF Form Eval.jpg

View 5 Replies
ADVERTISEMENT
Jun 15, 2007

is there a way to have the same named range cover a range of cells regardless of which sheet you are on? They seem to want to default to the page you are on. I want to make some VBA subroutines that will go over each sheet, and perform the same functions on each sheet. All sheets have the same format, but different data. I guess worst case scenario I can just break down and use multiple workbooks, but this seems a little less desirable. Should I be dividing this into seperate posts?

View 3 Replies
View Related
Dec 14, 2012

Is it possible to insert a formula or have a calculation performed for the range component when using the countif statement.

I am trying to take the entries in the rows in column B and divide its corresponding row in column A and then count how many meet a certain criteria.

View 1 Replies
View Related
Apr 22, 2009

I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."

(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:

=COUNTIF(IF(Analyses!$A$1:$A$10000=Summary!B8,Analyses!$DX$1:$DX$10000,""),">0")

(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.

View 5 Replies
View Related
Aug 10, 2008

I have a named range that expands and contracts based upon the amount of data that is in some column. Call it AllData_UsedRange.

I have another named range that actually refers to a range. Call it AllData.

Column A

Row2 56

Row3 44

Row4 65

AllData is a named range that refers to the range A2:A65536

AllData_UsedRange refers to A2:A4 by way of this formula.

=OFFSET(AllData,0,0,COUNTA(AllData))

How to I obtain an address of AllData_UsedRange in VBA code?

These do not work...

ThisWorkbook.Names("AllData_UsedRange").RefersToRange.Address

Evaluate(ThisWorkbook.Names("AllData_UsedRange"))

View 9 Replies
View Related
Jan 13, 2010

My file has a code that refers to a dynamic named range. When I run the macro that range gets corrupted and I cannot figure it out why. before the macro the named range refers to:

=OFFSET(Manager!$A$1,0,0,MATCH("*",Manager!$A:$A,-1),3)

and right after I run the macro it turns to:

=OFFSET(Manager!#REF!,0,0,MATCH("*",Manager!$A:$A,-1),3)

View 2 Replies
View Related
Mar 22, 2012

How I change the code below so that the ."AddItem" refers to a range within Sheet1?

For example .AddItem Worksheets("Sheet1").Range("O2:O20")

Code:

With ListBox1

.AddItem "January"

.AddItem "February"

.AddItem "March"

View 2 Replies
View Related
May 3, 2006

is it possible to concatenate in such a way that formatting is retained? For example, my =CONCATENATE formula refers to a cell containing 0.065, but which is formatted as a % so that the cell shows 6.5%. Concatenating that with text, however, produces "0.065 [text]"

View 4 Replies
View Related
Mar 11, 2007

I have some code which refers to a group of cells in another workbook as follows:

Workbooks("SUMMARY06.XLS").Worksheets("JS").Range("H23:H30").Value = ActiveCell.Range("B1:B8").Value

I wish to change the reference to the sheet reference rather than the tab name, but the following doesn't work.

Workbooks("SUMMARY06.XLS").sheet103.Range("H23:H30").Value = ActiveCell.Range("B1:B8").Value

And if I make two or more references to the same Workbook, can I use a with statement, like:

With Workbooks("SUMMARY06.XLS")

.Worksheets("JS").Range("H23:H30").Value = ActiveCell.Range("B1:B8").Value

End With

View 9 Replies
View Related
Mar 5, 2012

I don't want my sheet to auto calculate every time I change a cell that has nothing to do with the range I want to calculate. What would be the code to only have auto calculate run in range("A13:L17") when a worksheet_change happens in range("A13:L17").

View 9 Replies
View Related
Apr 11, 2007

Not sure what I'm doing wrong here but I think my syntax is wrong, here is the

Sub CalculatSG()

Dim FinalRow As Integer

FinalRow = Range("C" & Rows.Count).End(xlUp).Row

Range("BU5") = "=(BT5/100)*AE52"

Range("BU5").Copy

Range("BU5:BU[" & FinalRow & "]").Activate

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

End Sub

My objective is to apply the same calculation to the range of cells; the range may change which is why I have defined FinalRow and passed this as a refernence.

View 6 Replies
View Related
Dec 17, 2013

I have some data A2:A1000 where there is a start date in column D and end date in column E (formatted DDMMMYYYY). In column F I have a formula that calculates the NETWORKDAYS between the dates.

What I want is a set of formulae that will look in column D for a date between 01/01/2013 and 31/01/2013 (or any date I specify) and find the 'MIN', 'MAX' and 'AVG' of the values in column F where the date in column D matches my criteria.

What I can't figure out is how to offset from the cells matching the date range!?

View 1 Replies
View Related
Jun 23, 2009

How do I count the number of cells that have a value greater than 0 in a range of cells?

View 2 Replies
View Related
Nov 6, 2008

I would like to perform what seems like a simple calculation: =IF C1>0, THEN A1-C1, IF C1=0, THEN DO NOTHING. How would this look as a formula?

View 4 Replies
View Related
Nov 4, 2013

I am putting together a spreadsheet that needs to perform different calculations based on the information that is plugged in to the spreadsheet. Right now, I have a simple formula for my final product, which is:

=G2-E3-F3+C3+D3

But, if the variables change, then I have another cell with this formula:

=IF(OR(C4=0,D4=0),"",SUM(G2,H3))

Now, my first problem is that I need to get the information regarding D4 out of the above formula. Someone wrote the formula below to combine the information in cell C3 & D3, so I no longer need the reference to cell D4 in the above formula. How to remove the D4 references without getting an error.

You will notice that the 2nd formula has cell C4. This cell has an IF statement in it. The statement is as follows: =SUM(IF(C3=0.875,D3-0.625,D3))

Basically, what I need to do is to combine the first & second formulas on this page. How to do that.

View 2 Replies
View Related
Jul 26, 2007

I have several columns as so:

B - Patient Surname

C - First Surname

D - Postcode

E - Date

F - Results of If Statement (Days Difference)

I am looking to comapre b, c, d with teh previous cells to make sure I am looking at the same person hcen the AND statement. If they all match then if subtracts the latest date (column E) from an ealier date for the same person. If the person is different it will return a 0. If the date is the same ti will return a 0.

I have done this so far - =IF(AND(B2=B1,C2=C1, D2=D1),(E2-E1),0)

What I am struggling to do is if the number of days (E2-E1) is 0-3 then for the result to show the text "Less then 3". If the value is 4 - 7 then to have it read "Less then 7", anything over 7 to read "OVer 7".

View 8 Replies
View Related
Jun 11, 2014

I have a sheet with thousands of rows and 45 columns and I need to delete a range of cells (G:Y) if the cell in column V = the cell in column X, and I need to do this by row. I think an if/then statement would work.

View 4 Replies
View Related
Jan 8, 2014

I'm after a formula that will look at a number of cells(that are not in a range) with the word "C" and if they all = "C" then I want the cell to come back with the result "yes" otherwise "no".

View 12 Replies
View Related
Oct 2, 2013

I need a If statement that can be used on 2 columns.

If there is a 1 in the first column I want to indicate if the 2nd column is 50% or greater with a number 1.

View 3 Replies
View Related
Feb 4, 2014

This formula is being used in a vacation time spreadsheet. So after each paycheck the if statement is applied and need to calculate the accumulation for that week based on if the person has worked 5 yrs or less. I need to stop the today's date calculation in the if statement after it is used. So that each time the spreadsheet is opened the values are a fixed number not changed based on the current date. However if the if statement is pasted into the next pay week it will calculate based on the actual date.

It is calculating is the following:

C4 = 5yrs Anniversary Date of Hire

3.08= less than 5 yrs

4.62= more than 5 yrs

=IF(($C$4)<=TODAY(),4.62,3.08)

[URL] ......

View 1 Replies
View Related
Aug 22, 2008

I think the problem is the order in which I have the "next" loops. I would like to evaluate the if statement below over a range of cells

Sub Analysis()

Dim i As Integer

Dim x As Integer

Dim y As Integer

Dim k As Integer

Dim TheLast As Integer

TheLast = Sheets("Name").Range("c" & Rows.Count).End(xlUp).Row

For i = -7 To -26 Step -1

For x = 24 To 43

For y = 14 To 43

For k = 16 To TheLast.................

View 9 Replies
View Related
Jun 8, 2006

I'm trying to create a nested IFs statement that looks at a range of cells between 2 tabs within the same worksheet, determines if there is a value of 1 in ANY of those cells, and if there is, to multiply the value of yet another cell by 1 in a cell on the second tab. So, if 1 is a value in any cell of Tab 1 cells C82 to C86, then in Tab 2 cell B21, multiply 1 by the value in 'Tab 1 Cell H5'.

I've tried this =IF(('LM fundamentals'!C82>=1), ('LM fundamentals'!C83>=1), ('LM fundamentals'!C84>=1), ('LM fundamentals'!C85>=1), ('LM fundamentals'!C86>=1), 'Order Information'!$B$21!=(1* 'LMfundamentals'!$H$5!))

I even tried a vlookup, having given the cell range in Tab 1 a name, etc. But I have little skill with logic statements, apparently.

View 5 Replies
View Related
Aug 7, 2008

Hi. Does anyone know a formula to copy a selected range of cells on sheet one to a range on sheet three when a check box in checked. Ex. copy range a4:j4 on sheet one into a4:j4 on sheet three once the check box for on sheet one is checked?

View 9 Replies
View Related
Oct 4, 2007

I have a command button on sheet MASTER. When the workbook is Activated I want it to check and see if in sheet COSTM, cell B3 there are the words "Project Number", if so then show command button (ClearPrevious), if not, don't show. Also, when the If statement is finished, then the workbook needs to end up showing the sheet MASTER. I have tried various codes and none work, or they are on perpetual loops. I know this has got to be simple, but cannot find an example to take from to solve the issue. Would appreciate any help offered. Below is code I have right now.

Private Sub Worksheet_Activate()

If Sheets("COSTM").Select Range("B3").Select = "Project Name:" Then

Me.ClearPrevious.Visible = True

Else

Me.ClearPrevious.Visible = False

End If

Sheets("MASTER").Select

End Sub

View 9 Replies
View Related
Mar 16, 2007

1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it?

2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?

View 2 Replies
View Related
Apr 11, 2008

Tried to write a Macro to Range Cells in a sheet, setting the range values from a another cell. I have encounted a Run-Time error as select method of Range class failed. Below is the Macro.

Sub Macro1()

Dim r1 As Range, r2 As Range, myMultiAreaRange As Range

Worksheets("Pre").Activate

Set r1 = Range("J4")

Set r2 = Range("K4")

Set myMultiAreaRange = Union(r1, r2)

myMultiAreaRange.Select

Worksheets("Data").Select

Range(r1, r2).Select

Selection.Copy

Sheets("1").Select

Range("B5").Select

ActiveSheet.Paste

End Sub

Have the Ranges been set incorrectly

View 5 Replies
View Related
Jun 20, 2014

I'm having a problem referring to the value in my text boxes. I'm trying to refer the value in those to certain columns and rows that will be deleted after.

[Code]......

View 7 Replies
View Related
Dec 2, 2009

I need to put a formula which will show me required quantities under a relevant date which is already calculated in an aside table.

In the cell E8 I want it to look up for A8 in the table A35:AR45. Then in that row to find a date which is exactly the same or smaller than E7 and bring up all summed quantities from the table A50:A55 (quantities summed must be in the same column where the relevant date was found and in the row where the requirend part number is).

In the other hand I need to doin the cell H8 to llok up for A8 in the ableA35:AR45. Then in that row to find a date which is exactly the same as in H7 and bring up all summed quantities from the table A50:A55 (quantities summed must be in the same column as the relevant date was found and in the row where the required part number is).

View 3 Replies
View Related
Sep 22, 2006

This thread is related to this thread. Offset, Match, Max Formula In the Insert - Name - Define window:

If one wants to copy a formula from the "Refers to" box and the formula extends past the right side of the box, how does one use "select all" or mark the whole formula from beginning to end so it can be copied without messing up the formula. At the moment, when I try this, it changes the formula to include the active cell of the worksheet that's open when I use the Ctrl + c etc. I have tried Ctrl + Ins and Ctrl + a and Ctrl + c. None of these are working for me.

View 8 Replies
View Related
Dec 29, 2013

I'm working with a multi-page workbook I use for work scheduling purposes.

This is how Sheet1 is set-up:

A

B

1

Name

[Code]....

In the cells in Column D I have used the following formula =IF('Sheet1'!B2="Yes","Holiday",""). What I have realized is that this formula is quite limited. For example not all 12 colleagues work everyday and they work different shifts on different days- each day's data is sorted so that the colleagues are organized primarily by Shift start time and then alphabetically by name- consequently if I used this formula across the entire workbook the Sheet1B2 data may not be consistently referencing the same colleague.

I was wondering if there was a way to amend the formula so that if a colleague is marked as 'Yes' in Sheet1, 'Holiday' could be inserted in Column D in the rows corresponding to where the colleague's name appears?

View 3 Replies
View Related