Consulting

Results 1 to 5 of 5

Thread: Solved: Using two commands in Excel 2007

  1. #1

    Solved: Using two commands in Excel 2007

    Good afternoon guys. Well I am back and I have a good one for you.
    This concerns two spread sheets. The first ss has all the data I need to
    put in ss two. This is what I would lke to accomplish. In ss #1 in Column
    A there are 600 symbols. In Column B and C there is data for the symbols
    in column A. Now for the second ss. I would like to start with a new ss.
    I want the symbols in the first ss and the numbers in columns B and C to be brought over into ss two. I can do that using the VLookup command.
    Here is the problem. I only want the symbols and the positive numbers from Columns B and C brought over to the second ss.
    Can you help me with this. Thanks for your time with this.
    Max

  2. #2
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    I'm not sure what you mean by "to bring over" from workbook1 to workbook2.

    If you want workbook to to have a copy of workbook1's symbol to data chart, copy/paste will get everything, then AutoFilter will let you find and delete the negative values that you don't want.

    If you want to restrict VLOOKUP to only seek non-negative values, I don't understand.

    When you say spreadsheet1 and 2 do you mean different workbooks or different sheets within the same book?

  3. #3

    Reply to message on using vlookup and another command

    As to your first question. There are two workbooks. One and two.
    I want workbook two to use some command to look in workbook one.
    and bring over to workbook two any of the 660 symbols that meet the follow. First in workbook two in column A I want the symbol. In column
    B3:B660 I want any of the numbers that are more than 0.01 and the numbers in column C3:C660 that are more than 0.01.
    There are numbers in columns B and C that are less than negative that I
    do not want.
    I do not want to do a copy and paste.
    Max

  4. #4
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    Are there duplicate codes in [Book1]Sheet1!A:A ?

    If [Book2] A1 has a codestring, entered by the user, the formula (in [Book2]Sheet1!B1)

    =IF(VLOOKUP(A1,[Book1]Sheet1!$A$1:$B$10,2,FALSE)<.01,"",VLOOKUP(A1,[Book1]Sheet1!$A$1:$B$10,2,FALSE))

    will show either a number bigger than .01 or a blank cell.

    You might consider using conditional formatting to hide the look-ups that are < .01.

  5. #5

    Smile Moving data

    Hi Mikeriskson Thanks for the formula. The formula works just
    fine. I have added the rest of the columns to pick up the data I
    need. Again thanks. You did a great job with this one.
    Max

Posting Permissions

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