Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching for data
I have a workbook with 2 spreadsheets I'd like to tie together. The first
sheet has several columns, one consisting of vendor names, and others with amount of invoice, invoice #, etc. I'd like to be able to consolidate only the rows that have an invoice amount inputted (along with other data, such as vendor name and invoice number, etc.) onto the other sheet. I'd also like the data to show up sorted in by vendor name on the second sheet. Is this something that can possibly be done? Please help! Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching for data
Here's one simple play which delivers the core functionality of
auto-extracting only the lines with invoice amounts over to another sheet .. Illustrated in this sample construct: http://www.savefile.com/files/1017890 Extract only rows with invoice amt.xls Assume source data is in a sheet: x, cols A to C, data from row2 down, with key col = col C (Invoice Amt) In another sheet: y (say) In A2: =IF(x!C2="","",ROW()) Leave A1 blank In B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!A:A,SMALL($ A:$A,ROWS($1:1)))) Copy B2 to D2. Select A2:D2, fill down to cover the max expected extent of data in x. Hide away col A. Cols B to D will return only the lines from x where InvAmt (Invoice Amount col) contains an input (ie not empty), all neatly bunched at the top. To sort by vendor name, just do a simple copy n paste special as values of the extract elsewhere, then do a datasort by vendor name -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Karen" wrote: I have a workbook with 2 spreadsheets I'd like to tie together. The first sheet has several columns, one consisting of vendor names, and others with amount of invoice, invoice #, etc. I'd like to be able to consolidate only the rows that have an invoice amount inputted (along with other data, such as vendor name and invoice number, etc.) onto the other sheet. I'd also like the data to show up sorted in by vendor name on the second sheet. Is this something that can possibly be done? Please help! Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching Data Table | Excel Discussion (Misc queries) | |||
Searching, matching then searching another list based on the match | Excel Discussion (Misc queries) | |||
Data searching between sheets | Excel Discussion (Misc queries) | |||
Data searching between sheets | New Users to Excel | |||
Searching for Data | Setting up and Configuration of Excel |