Hi there,
First off, i have a standard format layout for our quote templates containing columns for part number, description, quantity and price. All our suppliers receives the same quote and types the prices in if they can quote and leave it empty if they can't. When we receive these quotes, i want to copy the data of each supplier into multiple "supplier named" sheets in the same format eg. sheet 1 (super spares) - line 3, columns A to C will be the same on all supplier sheets with the only differences in colum D which is price.
I use formules to calculate each individual suppliers cost per sheet eg. supplier 1 (sheet 1) cost incl. TAX - 5% discount, supplier 2 (sheet 2) cost incl. TAX -10%.
I want a formula that can search through multiple sheets, return the MIN price with multiple data on a different sheet.
EXAMPLE:
workbook contains the following:
supplier 1 (sheet 1)
row 3, column A (item number) = TKM095 row 3, column B (description) = Timing Kit row 3, column C (QTY) = 2 row 3, column D (price) = 100
row 4, column A = TKM100, etc.
supplier 2 (sheet 2)
row 3, column A (item number) = TKM095 row 3, column B (description) = Timing Kit row 3, column C (QTY) = 2 row 3, column D (price) = 120
row 4, column A = TKM100, etc
I want to compare data from row 3, column D (price), sheet 1 and sheet 2 row 3, column D and then return the lowest value to eg. row 3 column D on sheet 3 with aditional criteria eg. in this case it must show data from sheet 1, row 3, column A to D on sheet 3, row 3, column A - D because it has the lowest price. It must also (if it can), list the corrosponding sheet name (in this case, the supplier name is the sheet name) in a extra column.
We will then be able to copy and paste the quote into the respected supplier sheets, it will be calculated individually per sheet as per supplier terms, then it will list on the seperate sheet (eg. quote master) the same items as the quote, but with the supplier with the lowest price, hence the extra column listing the sheet name to identify where it must be ordered.
Then i want excel to take data per line and reference on column E (supplier sheet name) and show it in respected order sheets. So sheet 3 (quote master), line 3, column E = Supplier 1 must show all values (Column A - E, sheet 3) on sheet 4 (supplier 1 order) and when line 4, sheet 3, column E = supplier 2, it must show values of all values (column A -E, sheet 3) on sheet 5 (supplier 2 order) and so forth. Thus generating individual orders per supplier based on the values pulled through (min pricing) between multiple sheets to one sheet.
I hope i did explain this idea ok, please feel free to add suggestions or ask questions. i hope there is formulas, even multiple ones without using a data base.
Thank you
First off, i have a standard format layout for our quote templates containing columns for part number, description, quantity and price. All our suppliers receives the same quote and types the prices in if they can quote and leave it empty if they can't. When we receive these quotes, i want to copy the data of each supplier into multiple "supplier named" sheets in the same format eg. sheet 1 (super spares) - line 3, columns A to C will be the same on all supplier sheets with the only differences in colum D which is price.
I use formules to calculate each individual suppliers cost per sheet eg. supplier 1 (sheet 1) cost incl. TAX - 5% discount, supplier 2 (sheet 2) cost incl. TAX -10%.
I want a formula that can search through multiple sheets, return the MIN price with multiple data on a different sheet.
EXAMPLE:
workbook contains the following:
supplier 1 (sheet 1)
row 3, column A (item number) = TKM095 row 3, column B (description) = Timing Kit row 3, column C (QTY) = 2 row 3, column D (price) = 100
row 4, column A = TKM100, etc.
supplier 2 (sheet 2)
row 3, column A (item number) = TKM095 row 3, column B (description) = Timing Kit row 3, column C (QTY) = 2 row 3, column D (price) = 120
row 4, column A = TKM100, etc
I want to compare data from row 3, column D (price), sheet 1 and sheet 2 row 3, column D and then return the lowest value to eg. row 3 column D on sheet 3 with aditional criteria eg. in this case it must show data from sheet 1, row 3, column A to D on sheet 3, row 3, column A - D because it has the lowest price. It must also (if it can), list the corrosponding sheet name (in this case, the supplier name is the sheet name) in a extra column.
We will then be able to copy and paste the quote into the respected supplier sheets, it will be calculated individually per sheet as per supplier terms, then it will list on the seperate sheet (eg. quote master) the same items as the quote, but with the supplier with the lowest price, hence the extra column listing the sheet name to identify where it must be ordered.
Then i want excel to take data per line and reference on column E (supplier sheet name) and show it in respected order sheets. So sheet 3 (quote master), line 3, column E = Supplier 1 must show all values (Column A - E, sheet 3) on sheet 4 (supplier 1 order) and when line 4, sheet 3, column E = supplier 2, it must show values of all values (column A -E, sheet 3) on sheet 5 (supplier 2 order) and so forth. Thus generating individual orders per supplier based on the values pulled through (min pricing) between multiple sheets to one sheet.
I hope i did explain this idea ok, please feel free to add suggestions or ask questions. i hope there is formulas, even multiple ones without using a data base.
Thank you
