If i have an index in the last tab of spreadsheet which contains the followingRef description cost minutes So for an example an lenght of pipe would beRef description cost minute’s CU015 15mm Copper Pipe ?22.90 10 That’s the index So on the first tab I have the following:qty-Ref-description-Unit-cost-Mins-Labour/Hr-Hours-Labour-Material-TotalI want to be able to type in the ref code (cu015) and the quantity on the first sheet and it would all automatically fill in? Anyone any ideas?
Use VLOOKUP. Design your products list sheet (Sheet2) as is, then on the ordering sheet (Sheet1), in the description column, use this formula: VLOOKUP(B2,Sheet2!$A$1:$D$10,2,0) This formula looks for the value in B2 (the reference code) on Sheet2's table Starting from A1 to D10 (so maybe you have 10 products on there) in the 2nd row. Similarly, for the cost and minute columns on Sheet1, use these formulas: Cost VLOOKUP(B2,Sheet2!$A$1:$D$10,3,0) Mins VLOOKUP(B2,Sheet2!$A$1:$D$10,4,0) P.S. I use the dollar symbols within my formulas just to maintain consistency. In case if I had to drag the column down a few rows, it wouldn't change the reference table (A1:D10) to something else (like A4:D14) Hope that helps.
