PDA

View Full Version : Solved: Using two commands in Excel 2007



Tenspeed39355
04-12-2008, 01:07 PM
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

mikerickson
04-12-2008, 03:32 PM
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?

Tenspeed39355
04-12-2008, 04:49 PM
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

mikerickson
04-12-2008, 05:50 PM
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.

Tenspeed39355
04-15-2008, 04:33 AM
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