Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Match or Vlookup or Index (2003)

    Hello,

    I need to fill out some cells based on information from another sheet. I am attaching a spreadsheet with explanation of what I need to do. Basically, what I want to do is once a user selects a name, contract number, and Program, all previous information will be display for them to see and make sure they don't enter invoices twice. Thanks in advance.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Match or Vlookup or Index (2003)

    See the attached workbook. The worksheet with the formulas has been set not to display zero values, in order to hide 0 results (which indicate that there was no match).
    Note: the value June was followed by a space, I removed this.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> Were the replies <post:=647,885>post 647,885</post:> and <post:=648,107>post 648,107</post:> to your previous questions useful?
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match or Vlookup or Index (2003)

    Thanks Hans. The other reply to my previous questions worked great. I got one more question if I could. The user will be inputting new information in the Invoice number through invoice month fields. I want the information to go on rows 15 through 17 for the month they are entering the information and also to be able to carry the new information to the data sheet for historical purpose. Is there a way to do this. For example, they select all fields in yellow and start entering another invoice for company2 for august. I would like the user to see the information as they are inputting in rows 15 through 17 at the same time I want to update the data sheet with the new information. I thought about putting a macro to do this, but it would be too complex for me. Any help would be great. Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Match or Vlookup or Index (2003)

    Rows 15 through 17 contain formulas. If the user were to enter data there, they would replace (=destroy) the formulas. So you cannot use rows 15-17 to enter new data. That should be done in the data sheet.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match or Vlookup or Index (2003)

    I was thinking of entering the information in rows 8 through 11 and having them show up in rows 15 through 17 as they are been entered and then setting up a submit buttom with a macro to add the new row of information to the data sheet.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Match or Vlookup or Index (2003)

    You could use a submit button to add the data entered in rows 3 to 5 and 8 to 11 below the existing table in the data sheet. They will then be displayed in rows 15-17 to. You cannot have the data displayed in rows 15-17 before clicking the submit button since the formulas in rows 15-17 display data from the data sheet, not from the input sheet.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Match or Vlookup or Index (2003)

    The attached version of your workbook contains a Submit button and a macro that will attempt to add the data from B3:B11 to the data sheet. It checks whether the month exists and whether data for that month have already been entered.
    Attached Files Attached Files

  8. #8
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match or Vlookup or Index (2003)

    Thanks again Hans. This will work just fine.

  9. #9
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match or Vlookup or Index (2003)

    Hans,
    I use the worksheet above, but when those fields for the name, contract number and Program are based on a list-validattion, the formula does not work. Can you tell me why?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Match or Vlookup or Index (2003)

    Works OK for me - see attached version.
    Attached Files Attached Files

  11. #11
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match or Vlookup or Index (2003)

    Sorry. You are correct.

  12. #12
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match or Vlookup or Index (2003)

    Hans,
    I need to add one more variable to select from in oder to display the information. I try editing the formula, but I am lost as to how it gets to display the information. For example, I want the user to add select one more option under the program in the spreadsheet. This new field is going to be title "location". It looks like the formula is doing a sum of all the products and make values to upper case and I know is using some logic of multiplication 1 and 0 to display the value, but I am still not getting. Any explanation would be great if you have time. Your help is greatly appreciated.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Match or Vlookup or Index (2003)

    Let's look at the formula in B15:

    =SUMPRODUCT((UPPER(data!$A$3:$A$46)=UPPER($B$3))*( data!$B$3:$B$46=$B$4)*(UPPER(data!$C$3:$C$46)=UPPE R($B$5))*(UPPER(data!$G$3:$G$46)=UPPER(B$14))*data !$D$3:$D$46)

    The use of UPPER is merely to make comparisons of text values case-insensitive, so that "company2" is considered to be equal to "Company2". (Since column B in data contains numbers, we don't use UPPER for that). If we take that out for simplicity and add some spaces for clarity, we get

    =SUMPRODUCT( (data!$A$3:$A$46=$B$3) * (data!$B$3:$B$46=$B$4) * (data!$C$3:$C$46=$B$5) * (data!$G$3:$G$46=B$14) * data!$D$3:$D$46 )

    The parts such as (data!$A$3:$A$46=$B$3) result in an array of TRUE/FALSE values: TRUE for each cell in data!A3:A46 that is equal to B3, FALSE for the others. In calculations, TRUE = 1 and FALSE = 0.
    Four of these arrays are multiplied to combine the conditions: the result is TRUE = 1 only if all contributing elements are TRUE. If at least one of them is FALSE = 0, the product is FALSE too. This means that we can combine *any* number of TRUE/FALSE conditions by simply multiplying them. If you need another condition, add *(data!... = ...) substituting the correct range and comparison cell.

    Finally, this array of 1s and 0s is multiplied with the amounts in data!D346, and the results are added. Only the amounts from the rows where *all* conditions are TRUE are included in the sum.

    Note: the use of absolute ($) and relative references in B$14 is important to make it possible to fill the formula from B15 to the right.

  14. #14
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match or Vlookup or Index (2003)

    Thanks Hans. I understand now. I know I am pushing it, but how about an explanation of how the macro works?

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Match or Vlookup or Index (2003)

    The instruction

    Set oCell = Range("B14:I14").Find(What:=Range("B11"), MatchCase:=False, _
    LookIn:=xlValues, LookAt:=xlWhole)

    looks for the value of B11 (the month entered by the user) in the range B14:I14. If found, oCell is the cell containing the month, otherwise, oCell is Nothing. The lines

    If oCell Is Nothing Then
    MsgBox "Incorrect Month!", vbExclamation
    Exit Sub
    End If

    check whether oCell is Nothing, and issue a warning and quit the macro if necessary. The lines

    If Not oCell.Offset(1, 0) = 0 Then
    MsgBox "Invoice already exists!", vbExclamation
    Exit Sub
    End If

    check whether the cell below the month is empty. If not, there are data already, so issue a warning and quit the macro. If we get past this, we can add the new invoice. In the lines

    With Worksheets("data")
    lngRow = .Range("A65536").End(xlUp).Row + 1
    .Range("A" & lngRow) = Range("B3")
    .Range("B" & lngRow) = Range("B4")
    .Range("C" & lngRow) = Range("B5")
    .Range("D" & lngRow) = Range("B8")
    .Range("E" & lngRow) = Range("B9")
    .Range("F" & lngRow) = Range("B10")
    .Range("G" & lngRow) = Range("B11")
    End With

    we first find the last used row in column A on the data sheet, and get the row number of the next row (the first empty one). The values from B3:B5 and B8:B11 are copied to this row in the data sheet. Finally, the line

    Range("B8:B11").ClearContents

    clears B8:B11 so that the user can enter a new invoice.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •