Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Code for If Statement referencing another worksheet

This should be simple. I have 2 worksheets, WS1 and WS2. WS1 has a column of entries that are either marked TRUE or FALSE, e.g., a1=TRUE, a2=TRUE, a3=FALSE.

In WS2 I want to have a formula in the corresponding rows of column b that would appear as "=if(WS1! a1="TRUE", 1,0) where a1 is really a1, a2, a3, etc for each new row in column b. The VBA code to do this for say the value in a1 is

sheets("WS2").select
SelRange.Offset(0,0) = "=if(WS1! a1 =""X"",1,0)". This works!

Here's the rub. a1 has to bumped or iterated so I want to use a variable to define what row number is being used for column a that I want to put into the corresponding row in column B of WS2. I have tried (using i an an incremental row counter)

ptr=Range("a1").offset(i,0)
sheets("WS2").select
SelRange.Offset(0,0) = "=if(WS1! ptr =""X"",1,0)"

which doesn't work. It puts "=if(WS1! ptr ="X",1,0)" into WS2 column b when it should put the string a1, a2, etc. in place of ptr.

Hope this isn't confusing. Any help is welcome.


Post your answer or comment

comments powered by Disqus
Hi guys,
Anyone can help solve this problem?

What I need to do is transpose a list of data into its own respective worksheet based on data in column c.
For example if column C data is "Place1" the entire row of data will be copied and transpose into "Place1" worksheet.

The below is the code that i am trying to write.

Problem after execution of macro:

It only adds row2 data into the respective worksheet but skip the rest of the data and add the last row data is added in the wrong data worksheet.

I have also attached an example of my working file.

For
Each ce In ps.Range("C2:C" & ps.Cells(Rows.Count, "C").End(xlUp).Row)
        For Each ws In ActiveWorkbook.Worksheets
        
        If ce.Value = ws.Name Then
           
           If ws.Range("D1").Value = "" Then
         
                   ce.EntireRow.Copy
                   
                   ws.Range("D1").Select
                   
                   Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                   False, Transpose:=True
                   
                   Application.CutCopyMode = False
               
            Else
    
                   ce.EntireRow.Copy
        
                   ws.Range("IV1").End(xlToLeft).Offset(0, 1).Range("A1").Select
                    
                   Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                   False, Transpose:=True
                    
                   Application.CutCopyMode = False
               
             End If
           
        End If
        
        Next ws
     Next ce


Hello,

I am having issues writing the VBA code for IF statements. I am essentially trying to nest IF statements within one another. I have it done through the regular If functions as seen in the attachment, but I cannot seem to get it to work with VBA. I need it to work in VBA without knowing how many rows of data there are. In the attachment you will see the rules that I need the data follow, but here is an example. Essentially, depending upon how large cell C2 is, I need to add a certain amount of months to whatever is in E2.

=IF(C2="","",IF(C2<500000,DATE(YEAR(E2),MONTH(E2)+1,DAY(E2)),IF(AND(C2>=500000,C2<1000000),DATE(YEAR (E2),MONTH(E2)+1,DAY(E2)),IF(AND(C2>=1000000,C2<=2000000),DATE(YEAR(E2),MONTH(E2)+1,DAY(E2)),IF(AND( C2>2000000,D2="Critical"),DATE(YEAR(E2),MONTH(E2)+2,DAY(E2)),IF(C2>2000000,DATE(YEAR(E2),MONTH(E2)+1 ,DAY(E2)),"Reevaluate"))))))

Thanks for your help.

Hi friends,

I have the formula below, it is in column BR. It goes from column V to BN and it should be copied down from row3 to Row 700. The problem I am having is the formula is too long for the cell, what would be the quivalent VBA code for this?

Thanks for any assistance you can provide.

=IF(V3="X",V$1,”“ )&” “&iF(W3="x",W$1,““)&” “&IF(X3="x",X$1 ,”“)&” “&IF(Y3="x",Y$1 ,”“)&” “&IF(Z3="x",Z$1&” “&” ‘,””)&” “&IF(AA3="x",AA$1 ,”“)&” “&IF(AB3="x",AB$1 ,”“)&” “&IF(AC3="x",AC$1 ,”“)&” “&IF(AD3="x",AD$1 ,”“)&” “&IF(AE3="x",AE$1 ,”“)&” “&IF(AF3="x",AF$1 ,”“)&” “&IF(AG3="x",AG$1 ,”“)&” “&IF(AH3="x",AH$1 ,”“)&” “&IF(AI3="x",AI$1 ,”“)&” “&IF(AJ3="x",AJ$1 ,”“)

The sheet looks like this:

v w BR
1Criteria1 Criteria 2
2 x =if(V2="x",V$1,"")&" "&if(W2="x",W$1,"")
3 x x =if(V3="x",V$1,"")&" "&if(W3="x",W$1,"")
4

i am trying to find a Vba code for if Vba active cell value in sheet 3 cell B9 is equal to 101 then it should get data from sheet 1 cell A9 and paste it on to Sheet 3 B10. vise versa..for further info i have attached a sheet

I would like to know how to do an If statement in VBA which is the similar
to using OR in excel functions. eg.

I would like to do something like

If Var1 = 23 or 34 then
Var = "Hello"
end if

any help is appreciated, thanks.

I have a worksheet (bookings) which uses IF statements, referencing another worksheet (timetables), to determine the content of a cell, e.g.

=IF(ISBLANK(timetables!B5),"","booked")

The timetables sheet contains the timetables for rooms. The bookings sheet is set up to show which rooms are available (it also uses conditional formatting) and allows someone to book a room if it is free (blank cells show as green, those containing 'booked' because of the IF show as grey.

The problem I have is that if a booking is made the IF statement is deleted and replaced with the name of the person booking. If the booking is then cancelled the cell will show as available (green because it is blank), but if the timetable has been changed (a regular occurrence) it may now not be available.

The only solution I came up with was to have a master workbook, which contained the timetables and booking sheet, which would not be edited by the end users. But I don't know if the master workbook could essentially 'edit' the user workbook, so that the contents of the master were shown in the user one, unless the user edited it.

I may be talking nonsense and there may be a better way of doing this. Any help is appreciated.

Many thanks.

Need help with code for IF Than statment.

What I have is in column D I have a drop down box for Visual, Hidden. I need the statment for:
If Column D = Visual Than value in column F < 550 turn cell red
If Colunm D= Hidden than value in column F <650 turn cell red

Hi All,

I am trying to make an if statement on a worksheet and want it to check these things.

1) If cell = "y" and referenced value >0 return "Correct"
2) If cell = "y" and referenced value 0 return "Wrong"
5) If cell = "n" and referenced value 0, "Correct", "Wrong"), IF(LOOKUP(CONCATENATE($B3,F$2), $A$2:$A$3397, $C$2:$C$3397)

Here is an If statement I have in a cell in my worksheet:

=IF(H34>1249.999,"Approved","DENIED")

I would like to add another "condition" to that statement that references another cell before determining the "Approved" or "Denied."

In other words, I want the program to reference another cell (let's say H35 for argument's sake) looking for a "yes" or "no". And if the value of H34 is>1249.989 AND the value of H35 is "yes", then the value is "Approved" . If the value of H35 is "no", then the value is "Denied". In other words, if ANY of these conditions is false, then "Denied"

I appreciate any help.

Thanks much.

IF( 'E:file[1.xls]Sheet1'!$A$1 = 1 THEN
{ / Processing following code
Workbooks.Open Filename:="E:file1.xls", UpdateLinks:=3
Workbooks("1.xls").Close savechanges:=True
} else
/ Processing nothing

Does anyone have any suggestions on how to code the if statement for Excel
Macro?
Thank in advance for any suggestions
Eric

I thank Nigel for helping with the code to copy cells to another
worksheet when a date is added. I tried doing another code to delete
the copy if the date is deleted, but I am new and not sure what all the
codes mean yet or where to put the false statement or if this is
possible. Everything I tried does not work. Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
'from Google Group microsoft.public.excel.programming
If Target.Column = 4 And Target.Row > 1 Then
If IsDate(Target.Value) And Cells(Target.Row, 30) <> 1 Then
'transfer parts of this row to Record sheet or delete row if
column date is deleted
With Sheets("Record")
' <<< change the name of the target sheet here

'determine last row on record sheet
Dim xlr As Long
xlr = .Cells(.Rows.Count, 1).End(xlUp).Row

'copy selected cells to record from production (change for
other columns ect,)
.Cells(xlr + 1, 1) = Cells(Target.Row, "D")
.Cells(xlr + 1, 2) = Cells(Target.Row, "F")
.Cells(xlr + 1, 3) = Cells(Target.Row, "H")
.Cells(xlr + 1, 4) = Cells(Target.Row, "N")
.Cells(xlr + 1, 5) = Cells(Target.Row, "T")
.Cells(xlr + 1, 6) = Cells(Target.Row, "U")

'record that row data has been transfered
Cells(Target.Row, 30) = 1

'sort the record sheet by PC
.Range("A2:F" & xlr + 1).Sort Key1:=.Range("D2"),
Order1:=xlAscending

End With
End If
End If
End Sub

Thank you for any help,
Peaches

Hi All,

I have a Workbook (don't we all if we're looking for help here), with a
worksheet at the end that I have several command buttons on. The first
command button will insert a new worksheet that uses a variable as part of
the naming (i.e. Rename to "Formula Sheet #" & <variable>) and inserts the
sheet before the "command button sheet." It is working successfully as far
as inserting and naming goes. I have the varible declared in the Public area
so I can use it for the rest of the buttons.

The rest of the buttons have a macro attached to them with an IF statement.
They are supposed to activate Formula Sheet #<variable> and using the IF at
this point select a range based on the criteria. The code for the whole
thing is as follows

Worksheets("Formula Sheet #" & FormSheet).Activate
If LeftRight / 2 = FormatNumber(LeftRight / 2, 0) Then
FirstRow = Cells(Rows.Count, "A").End(xlUp).Offset(2, 0).Row
FirstCol = Cells(FirstRow, Columns.Count).End(xlToLeft).Column
Range(Cells(FirstRow, FirstCol), Cells(FirstRow + 6, FirstCol + 2)).Select
Else
FirstRow = Cells(FirstRow, FirstCol).End(xlUp).Offset(2, 0).Row
FirstCol = Cells(FirstRow, Columns.Count).End(xlToLeft).Offset(0,
4).Column
Range(Cells(FirstRow, FirstCol), Cells(FirstRow + 6, FirstCol + 2)).Select
End If

The IF portion determines where the range will be selected based on whether
or not LeftRight is even/odd. Basically the "odd" time any of the "range
selection buttons" are used the group of cells to the far left will be
selected and merged where an even LeftRight will select the range a couple of
cells to the right of the previous range and merge it. The problem I am
having is that if I have 'Worksheets("Formula Sheet #" & FormSheet).Activate'
statement in there I get an error. I've tried putting it in various
locations and still get the same error. If I leave it out, the Ranges on the
command button sheet get selected and merged in the same manner as I want the
ranges on the Formula Sheet to be selected and merged.

Also, I have tried moving the actual Range Selection statement to a location
outside of the IF Then but get a 400 error. If anyone has any ideas on how
to activate the formula sheet and select the ranges based on the LeftRight
idea please let me know. All the variable are public so they can be used
with any of the command buttons.

Thanks,

Chaz

I am using a text string to reference a block of cells in another worksheet.
The text string is in a seperate cell, and I am using this string in a
multitude of other cells using INDIRECT(TextStringCell). If the text string
is in the form
[Workbook]Worksheet!A1:B2, for example, i have to have the referenced
workbook open for the link to work. I thought perhaps that if the string was
in the form
C:Directory[Workbook]Worksheet!A1:B2, i needn't have the referenced
workbook open. Needless to say, this doesn't work.

On other spreadsheets, where i do not call a reference indirectly, i can
happily use the function ='C:Directory[Workbook]Worksheet!A1:B2 and I do
not need to have the referenced workbook open. Is this problem just a quirk
of an indirect reference, or is there a way around it? Perhaps I'm missing
some apostrophes some where....

Thanks in advance.

Hey all,

I was hoping to get some help on a VBA question. I am asking my vba to go through a serious of sheets and query the same sheets from another Excel file. So, I basically just need data to transfer from a closed file (in the same folder) into an open file that I am trying to code. Here are the two codes I've been looking at:

With
ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=FullPath" _
        ), Array( _
        ".xls;DefaultDir=Path;DriverId=790;MaxBufferSize=2048;PageTimeout=5" _
        ), Array(";")), Destination:=Range("B6"))
        .CommandText = Array( _
        "SELECT Non_Fixed_C.F1, Non_Fixed_C.F2, Non_Fixed_C.F3, Non_Fixed_C.F4, Non_Fixed_C.F5" & Chr(13) &
"" & Chr(10) & "FROM Non_Fixed_C Non_Fixed_C" _
        )
        .Name = "Query from Excel Files"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
This one is a problem for two reasons: 1. I cannot manage to make the File Path dynamic, no matter if I'm using ranges or not. It just will not edit the file path...which I need to be able to do. Secondly, and is this is minor and I could work around, but I'd rather not have to dictate which columns (I just want all, not the F1, F2, F3, etc.).

   
FileName = Range("D" & (13 + I)).Value
    FullPath = Path & "" & FileName

    For I = 1 To 2
    Worksheets("Settings").Select
    Sheet_Name = Range("G" & (20 + I))
    Worksheets(Sheet_Name).Select
    Input_Table = Range("P3").Value

'------------------------------
' Update Data Table
'------------------------------
    Range("B6").Select
        With Selection.QueryTable
            .Connection = Array( _
            "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" & FullPath & ""
_
            , _
            ";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System
database="""";Jet OLEDB:" _
            , _
            "Registry Path="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet
OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global" _
            , _
            " Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System
Database=False;Jet OLEDB:Encrypt Database=False;" _
            , _
            "Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False" _
            )
            .CommandType = xlCmdTable
            .CommandText = Array(Input_Table)
            .Refresh BackgroundQuery:=False
        End With
        Range("B4").Select
        Selection.QueryTable.Refresh BackgroundQuery:=False
This is great except that after the second time, I get a 1004 run-time error, and it does not seem just does not perform the query. All the labels are updating fine below and it's navigating the file fine...it's just breaking down when it goes to do the query for the second sheet.

Any help please? Thank you so much!

hi there,

need some help guys on getting a macro or vba code for copying data from one worksheet to another. on the attached file, i have mentioned on dark grey filled with bold text the constant parameters i need to copy or transfer every day on the dBase sheet next to it. can you please help me setting up a code that will work for this situation? i find it really hard to construct a vba code that will pull up the date, description, vessel type, etc. etc and paste it on the next sheet.i would like to have it on a single button that after clicking it auto populates the data i need on the other sheet on the corresponding headings. also, i want to protect the data into it to make sure that nobody can mess around with it . would appreciate any assistance. thank you very much for helping out.

Hi,

First of all I am using excel 2003 other wise this would be easier, but since you can't refrence another worksheet in 2003 when using conditional formating I was wondering If there was another way to do it.

My problem is I have two sheets, one is a plannned break down for an activity while the other is the actuall breakdown of what happend. The activity is broken down into a month by month basis, with an x percentage of hours for each month. Now what I am trying to do is say you have a total of 500 work hours, and you work 30 hours one month and the plan said it should be 20, is there a way to highlght that cell and also the remaining cells you have left to work in.

Below I attached a example work book witch will make it easier to understand

Thanks in Advance

Hello,

I am using Excel 2003 Professional and am having some trouble with linking one sheet to another "master" sheet in a different workbook.

The master sheet contains contract names like:
A1 Contract 1 (Vol-1)
2 Contract 1 (Vol-2)
3 Contract 1 (Vol-3)
4 Contract 1 (Vol-4)

There are numerous variations of "Contract 1" and for the purposes of my sheet, I only need to list the words, "Contract" rather than "Contract 1 (Vol-x)."

I would normally use an imbedded if statement (see below) when linking this sheet to the master sheet in order to convert each variation of Contract 1 to its simpler form, but there are more than 8 variations of Contract 1, so I run out of available arguments in the if statement.

=if(A1="Contract 1 (Vol-1)", "Contract 1", if(A1="Contract 1 (Vol-2)", "Contract 1", if(.......

I have tried altering the master sheet's actual text reference in my sheet using:

=REPLACE(A1,11,FIND("(",A1),"")

but run into problems when trying to imbed this into an if statement so it can be used across multiple contracts.

If anyone has any ideas regarding how to link my sheet to the master sheet while shortening the name, it would be greatly appreciated!

Thanks in advance!

Is there a better way than this to code IF statements in a worksheet?

Code:
--------------------
=IF(H8=1, A1, IF(H8=2, A2, IF(H8=3, A3, A4)))
--------------------

As the code shows, the common reference cell is H8 and the IF statement
checks it's value (from a range of 1-3) and then makes the target cell
equal one out of a range of cells depending on the outcome.

This is a short example but I have some very long and complex IF
statements that use this method and was wondering if there was a way to
simplify e.g. by using array formulas or something similar?

Something along the lines of:

Code:
--------------------
=[IF(H8 = 1;2;3, A1;A2;A3, A4)]
--------------------

That obviously doesn't work but that's the kind of thing I was
thinking.

Cheers
-Rob

--
TheRobsterUK

------------------------------------------------------------------------
TheRobsterUK's Profile: http://www.excelforum.com/member.php...fo&userid=9924
View this thread: http://www.excelforum.com/showthread...hreadid=387970

Hello, I'm getting a #REF! error with your formula (my original message and
your response is below. I have assumed that Patterns is a name which refers
to the entire column of data containing the patterns. Also, that Dimension1
is the array containing the data of interest - that is the entire data from
the top left cell to bottom right. Is this correct? Does there have to be a
1 to 1 correspondence between each row on the new worksheet and the one
containing the data. If so, that would
mean there would be blank rows in the new worksheet as the corresponding
data sheet would not have the patten specified in the formula. Many thanks
for your help, David

-----------------------------------------
Your response to my question:

Create a PIVOT table is the best solution

If you prefered the formula then try this:

Sheet2

In A1:
=IF(ISERR(SMALL(IF(Patterns="Lines",ROW(INDIRECT(" 1:"&ROWS(Patterns)))),ROWS($1:1))),"",INDEX(Dimens ion1,SMALL(IF(Patterns="Lines",ROW(INDIRECT("1:"&R OWS(Patterns)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
Drag the Fill Handle from A1 all the way down as far as needed

Copy from A1 to B1 and change Dimension1 to Dimension2
Drag the Fill Handle from B2 all the way down as far as needed

"DavidS" wrote:

> Hello, I have 8 columns of data that extends to over 1000 rows. One column
> contains different patterns. I would like to copy the data in the cells of
> two columns on the same row for all the instances of the same pattern to
> another worksheet. I would then have a worksheet for each pattern. I may
> not
> have described this clearly so there an example below. Many thanks, David
>
> Before
>
> Column C Column F Column G
> Cross 5.32 17.6
> Lines 8.67 12.4
> Circles 3.44 67.5
> Lines 5.67 45.3
> Squares 8.42 4.56
> Lines 8.99 12.7
> etc...
>
> After - for the Lines pattern (another worksheet)
>
> Column A Column B
> 8.67 12.4
> 5.67 45.3
> 8.99 12.7
>

Is there a better way than this to code IF statements in a worksheet?

As the code shows, the common reference cell is H8 and the IF statement checks it's value (from a range of 1-3) and then
makes the target cell equal one out of a range of cells depending on the outcome.

This is a short example but I have some very long and complex IF statements that use this method and was wondering if there was a way to simplify e.g. by using array formulas or something similar?

Something along the lines of:

That obviously doesn't work but that's the kind of thing I was thinking.

Cheers
-Rob

Hi,

I have some VBA code that I'm working on, and I'm not sure how to proceed. First of all, can I enbed one if statement inside another? If I can it would solve my problem.

This is basically what I'm going for:

if one>two and three>four then
   if five=six then
   do this
   elseif seven=eight then
   do that
   end if
end if
I'm not sure if that will work. I was thinking I may need a subroutine, or a function. The results of "do this" and "do that" will be required further in my code. If I do need a subroutine or a function, which one, and where does it go, in the main code, or in a module?

Help
Soma

I am trying to write an IF statement that would pull info from 20 different worksheets in the same workbook onto a consoldiated worksheet. For example "MH1" is the start of the first worksheet and "WAR2a" is the end of the last worksheet and there is 18 different worksheets between them. They are all laid out the same. The formula below is what I am trying to do.

I need to take what is in cell C10 in all 20 worksheets and if it is less than or equal to 27 then I want to take whats in cel E10 in all 20 worksheets if not then I want zero for the ones that are not less than or equal to 27.

=IF(MH1:WAR2a!C10<=27,MH1:WAR2a!E10,0)

Thanks,

Hello,

Another question for the brain boxes of the forum.

I have an XLS spreadsheet which updates cells through an ODBC connection to a database. The information is pulled across ok and 90% of the Vlookup and IF statements work.

The problem arises on a summary page.

When i start off the summary page has cell references in numeric order to another spreadsheet and pulls back the information to 4 cells Across and 1000 Down.

='Campaign Responses - Cash Break'!A2

This is a spreadsheet that changes once the queries have run and a number between 1-1000 records are returned.

However once the data has changed and the page refreshed. Not all the results have been pulled across to the summary pages. A few are there but the numeric sequence jumps from

='Campaign Responses - Cash Break'!A20

to

='Campaign Responses - Cash Break'!A802

Therefore missing out pulling back the records between row 80 and 802.
Sometimes there is also a problem with the cell displaying #ref. However when i copy the formula from the top line of cells and paste it the cell values update.

Anyone have any idea why this might be?

Thanks

Need syntax for If statement to see if Worksheet_Activate code exists.

For example

If Sheet1.Worksheet_Activate = True Then

Help please. Thank you. mikeburg


No luck finding an answer? You could always try Google.