Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# help with excel

Posted on 2011-03-03
Medium Priority
244 Views
I have an excel workbook that has a tab called data which has about 26 columns.

In my second tab called formatted I want to only include lines where the value in column W is a negative number.
0
Question by:Matt Pinkston
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3

LVL 9

Expert Comment

ID: 35027369
Here is a formula-only approach:

1. Add a column next to your source data, and place the following formula in in:

=IF(W2<0,ROW(),"") where column W contains your data. This can be hidden later

2. In the "Summary" sheet, enter an array formula like this where you want the filtered list to appear (assuming it will be starting at the top of the spreadsheet with headers):

{=IFERROR(INDEX(data!A1:B11,SMALL(data!C1:C11,ROW()),COLUMN()),"")}
where:
data!A1:B11 is the range of your whole list on the data sheet

See the attached example file, which should make it a bit clearer! Book1.xlsx
0

LVL 4

Expert Comment

ID: 35027977
I would do this as follows:

1.  Build your entire second sheet as a reference to the first sheet.  Type the following formula into Cell A1 of the second tab:  =data!A1  Then, copy this formula to all other cells in the sheet.

2.  Apply a Numeric Filter to Column W.  In this case, your filter will be "is less than 0."

Good luck!
0

LVL 9

Accepted Solution

McOz earned 2000 total points
ID: 35032546
Just a note - I'm assuming you want to avoid having to refresh the filtered list every time data changes in the data sheet. If not, jusgre44's solution is the simplest to set up. But it requires you to re-filter the second sheet, or use a macro to re-filter it, or it will display wrong data.

Cheers,
Oz
0

Author Comment

ID: 35203025
thanks maybe it will get more activity
0

LVL 9

Expert Comment

ID: 35286291
Hi pinkstonmp, did these comments provide the solution to your question? If not, can you provide some feedback so we can work on it?

Thanks!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
###### Suggested Courses
Course of the Month6 days, 20 hours left to enroll