Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
moving filtered data between worksheets automatically
I am not sure if this can be done.
I have a database in excel that tracks certain equipment inventory and maintenance . worksheet 1 looks like this: Truck # Station Location code # Status DATE 450 Millersville dw56 repair 10/3/06 451 Tioga dw63 inventory 10/1/06 452 Baltimore dw78 repair 9/30/06 I would like for any thing that has Status = repair to automatically go into worksheet#2. Also in worsheet 1 there are drop down lists for the truck, Station location, and Status (not sure if that effects what I want to do). Any help is greatly appreciated. thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
moving filtered data between worksheets automatically
Hi,
A way for automatically transferring the data to sheet2 is to implant formulas in enough rows to hold all the data (and copy the formulas further down when they fill). If you put in Sheet2!A2 the following formula, you can copy it through 5 columns and as many rows as necessary. =IF(ISNUMBER(SMALL(IF(Sheet1!$D$1:$D$100="repair", ROW(Sheet1!$A$1:$A$100)),ROW()-ROW($A$2)+1)),INDEX(Sheet1!A$1:A$100,SMALL(IF(Shee t1!$D$1:$D$100="a",ROW(Sheet1!$A$1:$A$100)),ROW()-ROW($A$2)+1)),"") This is an array formula so it must be committed with Shift+Ctrl+Enter. HTH Kostis Vezerides dweezy wrote: I am not sure if this can be done. I have a database in excel that tracks certain equipment inventory and maintenance . worksheet 1 looks like this: Truck # Station Location code # Status DATE 450 Millersville dw56 repair 10/3/06 451 Tioga dw63 inventory 10/1/06 452 Baltimore dw78 repair 9/30/06 I would like for any thing that has Status = repair to automatically go into worksheet#2. Also in worsheet 1 there are drop down lists for the truck, Station location, and Status (not sure if that effects what I want to do). Any help is greatly appreciated. thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
moving filtered data between worksheets automatically
Thanks so much, I have not gotten it to work yet. I don't have much time now
to play with it. I will try later and update my progress. -d "vezerid" wrote: Hi, A way for automatically transferring the data to sheet2 is to implant formulas in enough rows to hold all the data (and copy the formulas further down when they fill). If you put in Sheet2!A2 the following formula, you can copy it through 5 columns and as many rows as necessary. =IF(ISNUMBER(SMALL(IF(Sheet1!$D$1:$D$100="repair", ROW(Sheet1!$A$1:$A$100)),ROW()-ROW($A$2)+1)),INDEX(Sheet1!A$1:A$100,SMALL(IF(Shee t1!$D$1:$D$100="a",ROW(Sheet1!$A$1:$A$100)),ROW()-ROW($A$2)+1)),"") This is an array formula so it must be committed with Shift+Ctrl+Enter. HTH Kostis Vezerides dweezy wrote: I am not sure if this can be done. I have a database in excel that tracks certain equipment inventory and maintenance . worksheet 1 looks like this: Truck # Station Location code # Status DATE 450 Millersville dw56 repair 10/3/06 451 Tioga dw63 inventory 10/1/06 452 Baltimore dw78 repair 9/30/06 I would like for any thing that has Status = repair to automatically go into worksheet#2. Also in worsheet 1 there are drop down lists for the truck, Station location, and Status (not sure if that effects what I want to do). Any help is greatly appreciated. thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
moving filtered data between worksheets automatically
Vezrid,
I did not get the formula to work, in addition the database I am filtering and the info I am trying to move has way more than 5 columns. Thanks for the suggestion. I am thinking that I will need to write some type of macro. Problem is, I have no experience doing that. Anybody please help! "dweezy" wrote: Thanks so much, I have not gotten it to work yet. I don't have much time now to play with it. I will try later and update my progress. -d "vezerid" wrote: Hi, A way for automatically transferring the data to sheet2 is to implant formulas in enough rows to hold all the data (and copy the formulas further down when they fill). If you put in Sheet2!A2 the following formula, you can copy it through 5 columns and as many rows as necessary. =IF(ISNUMBER(SMALL(IF(Sheet1!$D$1:$D$100="repair", ROW(Sheet1!$A$1:$A$100)),ROW()-ROW($A$2)+1)),INDEX(Sheet1!A$1:A$100,SMALL(IF(Shee t1!$D$1:$D$100="a",ROW(Sheet1!$A$1:$A$100)),ROW()-ROW($A$2)+1)),"") This is an array formula so it must be committed with Shift+Ctrl+Enter. HTH Kostis Vezerides dweezy wrote: I am not sure if this can be done. I have a database in excel that tracks certain equipment inventory and maintenance . worksheet 1 looks like this: Truck # Station Location code # Status DATE 450 Millersville dw56 repair 10/3/06 451 Tioga dw63 inventory 10/1/06 452 Baltimore dw78 repair 9/30/06 I would like for any thing that has Status = repair to automatically go into worksheet#2. Also in worsheet 1 there are drop down lists for the truck, Station location, and Status (not sure if that effects what I want to do). Any help is greatly appreciated. thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro question | Excel Worksheet Functions | |||
Moving data between worksheets ... | Excel Discussion (Misc queries) | |||
Automatically pasting worksheet data to new worksheet with formulas | Excel Worksheet Functions | |||
Unique and duplicate data between 2 Excel worksheets | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) |