I am having a bit of an issue, i have some warehouse supplies tabbed out on different sheets which show a part #, item description, location, UOM, and then a qty ordered column. What i have done is created a formula to where when a value equal to or higher than a 1 is entered in the qty ordered column it copies that row of information and paste it into sheet 1 which is basically an order form at that point. I also have a data tab i am referencing so it knows what column to read and what values to look for (ie: greater than 1, less than 100, column 3 etc) my formula is below , the problem is now that i have got it to work with my aerial equipment sheets how do i ad the three additional sheets (splicing, underground, misc) to work the same so if you enter a qty on any of those sheets it function the same basically transferring rows with qtys from all sheets over to my first sheet (order form)?? In the end i am trying to have 1 order form that populated from all 4 sheets. When i try to ad them i just get formula errors.
Any help is greatly appreciated, again formula works between 1 sheet to the order form but i can not get it to work with all 4 sheets together, maybe wrong formula? too many arguments? how to add? also i am using excel 2013
***i have added the file i have partially working, i need sheets splicing, underground, and misc to work the same as aerial. hope this helps to understand better
=IFERROR(INDEX(Aerial!$A$2:$E$100, SMALL(IF((INDEX(Aerial!$A$2:$E$100, , Data!$B$4)<=Data!$B$3)(INDEX(Aerial!$A$2:$E$100, , Data!$B$4)>=Data!$B$2)(INDEX(Splicing!$A$2:$E$100, SMALL(IF((INDEX(Splicing!$A$2:$E$100, , Data!$B$4)<=Data!$B$3)*(INDEX(Splicing!$A$2:$E$100, , Data!$B$4)>=Data!$B$2), MATCH(ROW(Aerial!$A$2:$E$100), ROW(Aerial!$A$2:$E$100)), ""), MATCH(ROW(Splicing!$A$2:$E$100), ROW(Splicing!$A$2:$E$100)), "") ROWS('Order sheet'!A2:$A$2)), COLUMNS(Aerial!$A$1:A1)),"")
Bookmarks