Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Vloop command not working (2003)

    For some odd reason result of up macro should be IDND and not N/A. See cell A2 in source spreadsheet.
    If the SR number is the same in both spreadsheets then put the name in the Team column in the source spreadsheet into the column Team in the original spreadsheet.
    The autofilter needs to be applied to cells in the source file. I have tried without filter and still no luck.

    Any help would be appreciated, see attached for spreadsheets l am using. I am using Vloop to do this.
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Vloop command not working (2003)

    You have this problem spread across different threads , can you try not to cross post please?

    The reason you are getting N/A# in your source data is because you don't have a valid name in the cell referenced in column F. To get around the problem of this use an if statement like so:

    =IF(F2="","",VLOOKUP(F2,engineers!$C$2:$D$11,2,TRU E))
    Jerry

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vloop command not working (2003)

    since cell F2 is blank in your source file, then N/A will be the result.

    pls double check your file whether it should be blank
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vloop command not working (2003)

    Hello,

    Having a value there makes no difference.

  5. #5
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vloop command not working (2003)

    Thanks for that, where would this coding go?
    I assume this would resolve the issue and IDND would then be shown in Team column in original spreadsheet?
    Could you possibly put in files l have put in attachment so l can see exactly what you mean.
    That would be appreciated.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Vloop command not working (2003)

    Hi there

    I am at work now but I don't think it needs me to send you the workbooks again.

    Open up Source.xls, get all the autofilters to show All and then copy and paste the formula above into cell I2 and copy down all the available cells.
    Jerry

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Vloop command not working (2003)

    You are trying to match an SR# against a list of engineer names - how do you expect to find a match?
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vloop command not working (2003)

    I tried this but still no luck, any other ideas?

  9. #9
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vloop command not working (2003)

    How do you suggest l resolve issue?

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Vloop command not working (2003)

    Are you looking for a formula like this in ORIG.xls in L2?
    =VLOOKUP(A2,'G:AccountsMonthly reports[source.xls]SRs'!$A$1:$I$555,9,FALSE)

    This looks up the SR# in the original vs the SR# in Source and gets the "TEAM". The only problem still remains that some of the TEAMS are #NA (no engr) for some of the the SR# (like in your example). EXCEL finds the first matching SR# in your list. This is row 2 in your data and row 2 has no engr.... For this scheme to work you must sort the source so that the #NAs are at the bottom of the list so it finds the matches first.

    You could perhaps do it with an ARRAY formula (confirm with ctrl-shift-enter):
    =INDEX('G:AccountsMonthly reports[source.xls]SRs'!$I$2:$I$555,MIN(IF(('G:AccountsMonthly reports[source.xls]SRs'!$A$2:$A$555=A2)*(ISTEXT('G:AccountsMonthly reports[source.xls]SRs'!$I$2:$I$555)),ROW('G:AccountsMonthly reports[source.xls]SRs'!$I$2:$I$555))))

    Steve

  11. #11
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vloop command not working (2003)

    I am confused, could you possible put in this attachment to explain please.
    Attached Files Attached Files

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Vloop command not working (2003)

    I could but they will be dependent on where you and I both store them since they are external references.

    Open up both files in XL.

    In orig.xls in copy this into paste into L2 (don't press <enter>)
    =INDEX([source.xls]SRs!$I$2:$I$555,MIN(IF(([source.xls]SRs!$A$2:$A$555=A2)*(ISTEXT([source.xls]SRs!$I$2:$I$555)),ROW([source.xls]SRs!$I$2:$I$555))))

    After it is in L2 and you are in edit mode, then while holding <ctrl> and <shift> then hit <enter> The formula in the formula bar will look like:
    {=INDEX([source.xls]SRs!$I$2:$I$555,MIN(IF(([source.xls]SRs!$A$2:$A$555=A2)*(ISTEXT([source.xls]SRs!$I$2:$I$555)),ROW([source.xls]SRs!$I$2:$I$555))))}
    [The "squiggly brackets indicate an ARRAY formula]

    If you then save and close source.xls the formula in orig.xls will automatically add the drive/path to it since it is an external link. At this point you can even SAVEAS the source.xls file with a new name and the formula in orig.xls will be updated to reflect the change (as long as both are open when the file is renamed!)

    Steve

  13. #13
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vloop command not working (2003)

    See attachment for no N/A result in source, problem still exits.
    If you assume all files are local , therefore remove all path names. Could possibly place any coding in files so l can see what you mean?
    Attached Files Attached Files

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Vloop command not working (2003)

    <P ID="edit" class=small>(Edited by sdckapr on 20-Dec-07 11:52. Added PS fpr modified formula since #NAs were removed from Column I)</P>The formula in [orig.xls] "MACDs IBM" L2 still is looking up an SR# in your list of engineers. It gives a #NA error since the value in A2 (1302880) is not in the list of engineers in the source file. The list has names and teams but no SR#

    Follow the instructions in <post#=683,851>post 683,851</post#>....

    Is orig.xls supposed to be linked to "closed_SRs.xls" or to "source.xls"?
    I presume that the external file "closed_SRs.xls" for some reason has been renamed source.xls by you....

    Steve
    PS. I just notices. since you place null strings in column I of source the ISTEXT will not filter out the errors. Instead we can key on a name in column F:
    =INDEX([source.xls]SRs!$I$2:$I$555,MIN(IF(([source.xls]SRs!$A$2:$A$555=A2)*(ISTEXT([source.xls]SRs!$F$2:$F$555)),ROW([source.xls]SRs!$I$2:$I$555))))

  15. #15
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vloop command not working (2003)

    I have tried this but for some reason the last 2 values in the Team column in the original file (simple complex November.xls) are showing incorrect values and l cannot work out why. See attachment for details.
    Attached Files Attached Files

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
  •