• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Request Help with a VlookUp with an IF Formula

The person who created this formula no longer works with me. He created this formula within a Macros Code. What I need help with is this: When I run the Macros, the VlookUp portion no longer updates. A pop up box comes up to Update Values for each sheet the formula references. I have no idea how to fix that. Is there a way I can simplify this formula? Or recreate it so that it references the 'SITE' and 'Sue'sGrp' sheets within the workbook?

=IF(C2="REC","Rcvbles",IF(AND(LEFT(H2,2)<>"65",C2="SAL"),"Salvage",IF(C2="MGT","MGT",IF(C2="LIT","LIT",IF(OR(AND(LEFT(H2,2)="65",A2>="2328",A2<="2338"),AND(LEFT(H2,2)="65",A2>="2480",A2<="2489")),"Pre-Legal",IF(ISNUMBER(FIND(140,H2)),"Chase",IF(AND(H2>=8070,H2<=8072),"B of A",IF(H2=8073,"8073",IF(H2=8074,"B of A",IF(H2=8081,"8081",IF(H2=8800,"Barclay",IF(ISNUMBER(FIND(8810,H2)),"Cascade",IF(ISNUMBER(FIND(8825,H2)),"HANNA",IF(ISNUMBER(FIND(8826,H2)),"HANNA",IF(AND(H2>=2725,H2<=2730),"TD Bank",IF(ISNUMBER(FIND(269,H2)),"Santander",IF(ISNA(NOT(OR(LEFT(H2,3)="657",LEFT(H2,3)="656",LEFT(H2,3)="770",VLOOKUP(A2,SuesGrp!A$1:C$143,1,FALSE)=A2))),VLOOKUP(A2,SITE!A$1:K$2397,7,FALSE),IF(AND(OR(LEFT(H2,3)="657",LEFT(H2,3)="656",LEFT(H2,3)="770",LEFT(H2,3)="771",LEFT(H2,3)="773",LEFT(H2,3)="774",LEFT(H2,3)="779"),VLOOKUP(A2,SuesGrp!A$1:C$143,1,FALSE)=A2),"Sue's Grp",VLOOKUP(A2,SITE!A$1:K$2397,7,FALSE)))))))))))))))))))
 
Hi ,

I suggest you let this monstrosity die a natural death.

Please describe your exact requirement , and if possible upload a sample workbook , so that code can be drafted for today and for posterity.

Narayan
 
I have a list of employees who work in different departments. I was using the VlookUp to create the list of employees using 3 different criteria. I cannot seem to upload just a portion of the workbook. The file is too large.
 
I attached the workbook, but without the Site and Sue's Grp sheets. If needed, I can attach those separately, I guess. Sorry this turned into such a hassle.
o_O
 

Attachments

  • Sample Workbook 1.xlsx
    11 KB · Views: 11
Good day KristyLee0228

You will receive the help you need but full information would help, if the workbook is over the 1mb then upload to Dropbox and post the public link here.
Bit difficult for members to help if they are not sure of the data and the criteria you are working towards.

ist of employees using 3 different criteria.
??????????

.
 
Hi ,

I think you need to give some more information ; I am not able to relate the cell references in the formula with either the data in the workbook or with what you have posted about looking up with 3 criteria.

Having uploaded your file , if you can just take one more step of explaining what your requirement is , with specific reference to your uploaded workbook's cells / ranges / tabs , I think a solution should be easy.

Narayan
 
I have inserted the site column to tag employees into different sites. An employee can work in more than one site based on the Coll ID(Column A), Type(Column C) and/or Client ID(Column H).
The formula above tagged them correctly. However, when I run the Macros that formats this page, it does not reference the SITE and Sue's Grp tabs within the VLookUp formula. A pop up box comes up wanting me to Update Values. I select the workbook that the tabs are in and the formula comes back with an error. #NAME!
I was hoping for 2 things here: 1. To fix the error code and 2. Simplify this formula, if possible.
Attached is the workbook again with a bit more data.
Thank you so much for the help!

https://www.dropbox.com/s/xxsn8nkbo0f4842/SampleWorkBook.xlsx?dl=0
 
Hi ,

You should be aware that a .xlsx file cannot have any code in it , so if you think I can troubleshoot the code from your file , it's impossible.

Given enough details of what you want done , I can possibly come up with a formula , but if you need it to be done using code , then please put the code into your file , save it as a .xlsm or .xlsb file and then upload it.

Narayan
 
I don't know if the code will help or not, but here is the portion of the code that refers to that formula. ws.Range("D2") is the line the formula refers to.

Code:
Application.Workbooks.Open ("w:\Cashmacroref.xlsx")
  wb.Activate
  ws.Range("I2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("H2").Formula
  Range("I2").AutoFill Destination:=Range("I2:I" & MaxRows), Type:=xlFillDefault
  Range("I:I") = Range("I:I").Value
  Columns("H:H").Delete Shift:=xlToLeft
  ws.Range("I2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("I2")
  ws.Range("I2").Formula = "=" & Range("I2").Value
  Range("I2").AutoFill Destination:=Range("I2:I" & MaxRows), Type:=xlFillDefault
  Range("I:I") = Range("I:I").Value
  ws.Range("J2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("J2").Formula
  ws.Range("J2").Formula = "=" & Range("J2").Value
  Range("J2").AutoFill Destination:=Range("J2:J" & MaxRows), Type:=xlFillDefault
  Range("J:J") = Range("J:J").Value
  ws.Range("K2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("K2").Formula
  ws.Range("K2").Formula = "=" & Range("K2").Value
  Range("K2").AutoFill Destination:=Range("K2:K" & MaxRows), Type:=xlFillDefault
  Range("K:K") = Range("K:K").Value
  ws.Range("D2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("D2").Formula
  ws.Range("D2").Formula = "=" & Range("D2").Value
  Range("D2").AutoFill Destination:=Range("D2:D" & MaxRows), Type:=xlFillDefault
  Range("D:D") = Range("D:D").Value
  ActiveSheet.Sort.SortFields.Clear
  ActiveSheet.Sort.SortFields.Add Key:=Range("S2:S" & MaxRows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  xlSortTextAsNumbers
 
Hi ,

I think you are taking over the troubleshooting by deciding what will be helpful and what will not.

If you want your problem to be solved please let us have everything ; the code you have posted does not define what wb is , what ws is ; it's a waste of time trying to debug this problem like this.

I hope someone else will help out.

Narayan
 
I apologize. I honestly don't know what is wrong at all. I didn't mean to come off like I was taking over. I didn't realize you needed the entire page of code. I was trying to be helpful by eliminating what may not have been needed, that's all. Here is entire page of code.

Code:
Sub CashmacroNoselect()

' CashmacroNoselect Macro

  Application.ScreenUpdating = False
  Dim wb As Workbook
  Dim ws As Worksheet
  Set wb = ActiveWorkbook
  Set ws = ActiveSheet
  Columns("F:G").Cut
  Range("X:Y").Insert
  Columns("R:R").Cut
  Range("X:X").Insert
  Columns("I:I").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Columns("J:J").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Columns("K:K").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Columns("T:T").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Columns("T:T").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Columns("T:T").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Columns("U:U").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Columns("Q:Q").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Columns("R:R").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Rows("1:4").Delete Shift:=xlUp
  Range("M:N,F:F,W:W,X:X,AB:AB").NumberFormat = "0"
  Range("P:P,S:S,U:U,AE:AE").NumberFormat = "m/d/yy;@"
  Range("Q:Q,R:R").NumberFormat = "[$-409]mmm-yy;@"
  Columns("V:V").NumberFormat = "[$-409]d-mmm;@"
  Range("Y:Y,T:T,Z:Z,AA:AA,AC:AC").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
  Range("AF:AF").NumberFormat = "0.0"
  MaxRows = Cells(Rows.Count, "A").End(xlUp).Row
  ActiveSheet.Sort.SortFields.Clear
  ActiveSheet.Sort.SortFields.Add Key:=Range("G2:G" & MaxRows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  xlSortTextAsNumbers
  With ActiveSheet.Sort
  .SetRange Range("A1:CY" & MaxRows)
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
  Columns("G:G").Find(What:="-", After:=Range("G1"), LookIn:=xlFormulas, LookAt _
  :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
  False, SearchFormat:=False).Activate
  ActiveCell.Offset(0, -6).Select
  Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp)).EntireRow.Delete Shift:=xlUp
  MaxRows = Cells(Rows.Count, "A").End(xlUp).Row
  Columns("I:I").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Range("I1").FormulaR1C1 = "Clt ID"
  Application.Workbooks.Open ("w:\Cashmacroref.xlsx")
  wb.Activate
  ws.Range("I2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("H2").Formula
  Range("I2").AutoFill Destination:=Range("I2:I" & MaxRows), Type:=xlFillDefault
  Range("I:I") = Range("I:I").Value
  Columns("H:H").Delete Shift:=xlToLeft
  ws.Range("I2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("I2")
  ws.Range("I2").Formula = "=" & Range("I2").Value
  Range("I2").AutoFill Destination:=Range("I2:I" & MaxRows), Type:=xlFillDefault
  Range("I:I") = Range("I:I").Value
  ws.Range("J2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("J2").Formula
  ws.Range("J2").Formula = "=" & Range("J2").Value
  Range("J2").AutoFill Destination:=Range("J2:J" & MaxRows), Type:=xlFillDefault
  Range("J:J") = Range("J:J").Value
  ws.Range("K2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("K2").Formula
  ws.Range("K2").Formula = "=" & Range("K2").Value
  Range("K2").AutoFill Destination:=Range("K2:K" & MaxRows), Type:=xlFillDefault
  Range("K:K") = Range("K:K").Value
  ws.Range("D2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("D2").Formula
  ws.Range("D2").Formula = "=" & Range("D2").Value
  Range("D2").AutoFill Destination:=Range("D2:D" & MaxRows), Type:=xlFillDefault
  Range("D:D") = Range("D:D").Value
  ActiveSheet.Sort.SortFields.Clear
  ActiveSheet.Sort.SortFields.Add Key:=Range("S2:S" & MaxRows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  xlSortTextAsNumbers
  With ActiveSheet.Sort
  .SetRange Range("A1:CY" & MaxRows)
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
  Range("S2").Select
  Range(Selection, Selection.End(xlDown)).Select
  Selection.End(xlDown).Select
  ActiveCell.Offset(1, -3).Range("A1").Select
  Range(Selection, Selection.End(xlDown)).Copy
  ActiveCell.Offset(0, 3).Range("A1").Select
  ActiveSheet.Paste
  ws.Range("R2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("R2").Formula
  Range("R2").AutoFill Destination:=Range("R2:R" & MaxRows), Type:=xlFillDefault
  Range("R:R") = Range("R:R").Value
  ActiveSheet.Sort.SortFields.Clear
  ActiveSheet.Sort.SortFields.Add Key:=Range("Z2:Z" & MaxRows), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  xlSortTextAsNumbers
  With ActiveSheet.Sort
  .SetRange Range("A1:CY" & MaxRows)
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
  Columns("Z:Z").AutoFilter
  ActiveSheet.Range("Z1:Z" & MaxRows).AutoFilter Field:=1, Criteria1:="<0", _
  Operator:=xlAnd
  Range("Z2").Select
  Range(Selection, Selection.End(xlDown)).Cut
  ActiveCell.Offset(0, -1).Range("A1").Select
  ActiveSheet.Paste
  ActiveSheet.Range("$Z$1:$Z$1047038").AutoFilter Field:=1
  Range("Y1").FormulaR1C1 = "NSFAmt"
  Selection.AutoFilter
  ws.Range("X2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("Y2").Formula
  Range("X2").AutoFill Destination:=Range("X2:X" & MaxRows), Type:=xlFillDefault
  Range("X:X") = Range("X:X").Value
  ws.Range("Q2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("Q2").Formula
  Range("Q2").AutoFill Destination:=Range("Q2:Q" & MaxRows), Type:=xlFillDefault
  Range("Q:Q") = Range("Q:Q").Value
  Range("Q:Q").NumberFormat = "[$-409]mmm-yy;@"
  Columns("O:O").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  ws.Range("O2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("O2").Formula
  Range("O2").AutoFill Destination:=Range("O2:O" & MaxRows), Type:=xlFillDefault
  Range("O:O") = Range("O:O").Value
  Columns("P:P").Delete Shift:=xlToLeft
  Columns("U:U").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  ws.Range("U2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("U2").Formula
  Range("U2").AutoFill Destination:=Range("U2:U" & MaxRows), Type:=xlFillDefault
  Range("U:U") = Range("U:U").Value
  ws.Range("W2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("W2").Formula
  Range("W2").AutoFill Destination:=Range("W2:W" & MaxRows), Type:=xlFillDefault
  Range("W:W") = Range("W:W").Value
  ws.Range("X2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("X2").Formula
  Range("X2").AutoFill Destination:=Range("X2:X" & MaxRows), Type:=xlFillDefault
  Range("X:X") = Range("X:X").Value
  ws.Range("AH2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("AH2").Formula
  ws.Range("AH2").Formula = "=" & Range("AH2").Value
  Range("AH2").AutoFill Destination:=Range("AH2:AH" & MaxRows), Type:=xlFillDefault
  Range("AH:AH") = Range("AH:AH").Value
  ws.Range("AI2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("AI2").Formula
  ws.Range("AI2").Formula = "=" & Range("AI2").Value
  Range("AI2").AutoFill Destination:=Range("AI2:AI" & MaxRows), Type:=xlFillDefault
  Range("AI:AI") = Range("AI:AI").Value
  ws.Range("AJ2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("AJ2").Formula
  ws.Range("AJ2").Formula = "=" & Range("AJ2").Value
  Range("AJ2").AutoFill Destination:=Range("AJ2:AJ" & MaxRows), Type:=xlFillDefault
  Range("AJ:AJ") = Range("AJ:AJ").Value
  ws.Range("AK2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("AK2").Formula
  ws.Range("AK2").Formula = "=" & Range("AK2").Value
  Range("AK2").AutoFill Destination:=Range("AK2:AK" & MaxRows), Type:=xlFillDefault
  Range("AK:AK") = Range("AK:AK").Value
  ws.Range("AL2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("AL2").Formula
  ws.Range("AL2").Formula = "=" & Range("AL2").Value
  Range("AL2").AutoFill Destination:=Range("AL2:AL" & MaxRows), Type:=xlFillDefault
  Range("AL:AL") = Range("AL:AL").Value
  ws.Range("AM2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("AM2").Formula
  ws.Range("AM2").Formula = "=" & Range("AM2").Value
  Range("AM2").AutoFill Destination:=Range("AM2:AM" & MaxRows), Type:=xlFillDefault
  Range("AM:AM") = Range("AM:AM").Value
  ws.Range("AN2").Formula = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Range("AN2").Formula
  ws.Range("AN2").Formula = "=" & Range("AN2").Value
  Range("AN2").AutoFill Destination:=Range("AN2:AN" & MaxRows), Type:=xlFillDefault
  Range("AN:AN") = Range("AN:AN").Value
  ws.Rows("1:1").Value = Workbooks("Cashmacroref.xlsx").Sheets("Sheet1").Rows("1:1").Value
  Workbooks("Cashmacroref.xlsx").Close SaveChanges:=False
  With ws.UsedRange
  .Columns.AutoFit
  .HorizontalAlignment = xlCenter
  .Font.Name = "Calibri"
  .Font.Size = 10
  End With
  ws.Rows("1:1").Font.Bold = True
  Application.ScreenUpdating = True
End Sub
 
It was suggested to you to provided as much information as possible, you have removed code as you know it is not included in the fault but those who are trying to help have no idea of what your missing code is or what it does.


You cannot help someone with driving lessons when they turn up in a shiny car.....bur they have removed the engine because they wanted to save fuel.


If you workbook contains sensitive names then do a find and replace with nonsense names, if it contains numerical just enter anything, that still keeps any formulas/functions/code you are using and then upload directly or Dropbox,....The more information you give the higher standard of help you will receive.


Go have some coffee and wait to see what help you receive.


.
 
Thank you. The entire workbook was uploaded to Dropbox and my prior message contains all the code.
I appreciate your help. Thank you.
 
Hi ,

Sorry I let my irritation come through ; the point is if this step had been taken when you first posted your question ( on Monday ) you would have got your solution on Monday ; as it stands , you will get it today. Please check back later.

Narayan
 
Hi ,

The macro you posted is making extensive reference to a workbook :

Cashmacroref.xlsx

What is this ?

In fact the macro you have posted does nothing but copy a lot of formulae from the above workbook to the workbook which you uploaded ; thus the formula which you posted initially does not figure anywhere in the posted macro.

There is no way anything can be done unless you upload the above workbook.

Or you explain what you want the formula to do , and we develop a new formula , which hopefully will be easier to understand.

Narayan
 
That is a file the macro references to format and place the formulas into a data file. Similar to the sample workbook I sent you.
I can upload the Cashmacroref.xlsx. But, I think developing a new formula maybe the key to better understanding.

I need the formula to look up the list of employees that come over in the data file and find them in the SITE tab and Sue's Grp tab. It is possible that an employee can have more than one site .. if they have more than one Coll ID.
The formula should be able to tag the employee as such based on TYPE, CLT ID and/or COLL ID.
Thank you.

https://www.dropbox.com/s/irq42p9ts0y41z5/Cashmacroref.xlsx?dl=0
 
Hi ,

I am still somewhat clueless about the whole thing.

I copied the two tabs SITE and SuesGrp from the workbook Cashmacroref.xlsx to the other workbook which you had uploaded earlier :

SampleWorkBook 11.25.14.xlsx

I then inserted the formula in column I in the tab CashData , after making some minor changes. See if the results given by the formula are correct.

The link to the file is :

https://www.dropbox.com/s/xxz4dygxhviq2cc/SampleWorkBook 11.25.14.xlsx?dl=0

Narayan
 
Back
Top