capture & save cell value before it change to use for averaging

[Legacy 194999]

I have a Costing worksheet on which I enter a job number, and all pertinent info for that job autofills from other sheets in workbook (such as part number, Customer PO number, manufacturing hrs, freight cost, material cost, etc.)
The Costing sheet then gives me a net profit margin percent for that particular job. All this autofill info changes every time a different job number is entered on the Costing sheet.

What I want is for each net margin percent to be saved on another sheet so I can average the net profit margin on each job for a particular part number. (Example: Job # 1170 is for customer named Acme, part number is 12345, net margin for job 1170 is 15.65%. Job # 1142 is also for customer Acme, same part number, 12345, but net margin on that job was 19.86%. I want to capture those margins from the Costing sheet and save each job margin on another sheet so I can average the profit margin for all jobs for that part number.)

I can get it to show whatever the current margin is being shown on the costing sheet, but how do I save each net margin before entering another job number? (PS - I do not know VBA...all my sheets use formulas, so I would need explicit directions on how to write code if that is what would be required.)

Thank you so much!!

 

[Excelestial]

If you don't want to write code, why not design your spreadsheet to allow you to retrieve as many records as you want and do the averaging of profit margins for you.

Would be a cinch.

 

[Legacy 194999]

I'm sorry, but I guess I don't understand what you mean. I could manually enter each job's profit margin from the Costing sheet into another sheet and then get the average, but I thought maybe there was a way to automatically save each margin from the Costing sheet before a new job number was entered...but maybe it's beyond my capability.

 

[Excelestial]

What I'm saying is have a spreadsheet (or the current spreadsheet) that allows you to populate as many job numbers as you want...say...populate by using a data validation list and selecting job numbers.

After you've selected all the job numbers you want to see (or all the records of the 1 job number you want to see)...Excel will average the profit margins for you.

This way you don't have to call up one record at a time...you can call them all up at once. And when they appear, so will your profit margin average.

 

[Legacy 194999]

Okay, on a new sheet, currently named Sheet1, I made a drop down list in column A (titled Jobs), beginning in cell A2. The list contains all the job numbers that are on the sheet named Jobs, where the jobs are entered as they come in.....this "Jobs" sheet gives the customer name, the part number, the material costs, freight costs, labor hrs (which it takes from another time sheet in the workbook), part price....all the information for the assigned job#.

Column B I titled Net Profit Margin and entered an If formula saying if the Costing sheet job number is the same as that chosen from the drop down list, then enter the net margin given on the Costing sheet. But, that goes blank when a new job number is put into the costing sheet. How do I make it "stick"?

 

[Excelestial]

Run your new drop-down list down to as many rows as necessary. You may need 100 rows...you may need 10 rows. Point is to make the drop-down available for any number of records you want to pull.... then....

 

[Excelestial]

Across the top row of your table on this new sheet, have the same header rows that are on the JOBS sheet. You will need these for your VLOOKUPs...then...

 

[Excelestial]

Once your headers are in, run the formula...VLOOKUP(item #, JOBS TABLE, match(B1,JOBS TABLE ROW 1, false)...as an example...

 

[Legacy 194999]

Run your new drop-down list down to as many rows as necessary. You may need 100 rows...you may need 10 rows. Point is to make the drop-down available for any number of records you want to pull.... then....

I'm sorry....please be patient with me.
My drop down list does contain all the job numbers entered in the Jobs sheet, and I can choose any job that exists. It pulls the net profit from the Costing sheet for the correct job number, but when I enter a new job number in the Costing sheet the profit margin for all other jobs disappears and only the net profit margin for the job number currently in the Costing sheet is given.

 

[Excelestial]

If you have multiple records for the same job number, insert a help column on your JOBS sheet to enumerate all the records that match the one selected on the new sheet. You will also need an enumeration column on your new sheet that will count in succession the number of records that have been identifed on yourJOBS sheet. This is easily done by using MAX(JOBS!A1:A5000)...assuming your enumerating column is A1 through 5000. you will then write your VLOOKUP to look up each number on the JOBS sheet...using the MATCH as I indicated in the prior post.