Need help with formula

[CaptRDMretired]

I am in the process of creating a workbook for my wife's use at work and, as yet, do not have a viable sample file to include - I will attempt to explain. Here are the two pertinent areas of concern, Table1 and Table2.

Table1: Date Code Cases Net Lbs

Date = date of data entry;
Code = produced item;
Cases = number of cases produced;
Net Lbs = (a calculated poundage based upon the Code and Lbs in Table2.
​

Table2: Date Code Lbs

Date = date of entry of this particular Code/Lbs entry into Table2;
Code = produced item (as in Table1);
Lbs = a conversion factor to calculate the number of Lbs per Case.
​

Everything works perfectly until the Lbs/Case figure in Table2 is changed to reflect a newer, up to date, poundage for a case of any particular product.

Ex: On Jan 1, 2000 the Lbs/Case of Widgets was 50. Then, on Oct 5, 2002 the Lbs/Case of Widgets changed to 45.​

If the data in Table2 is altered to reflect the new Lbs/Case for Widgets then all entries in Table1 change to reflect the new data. This, in turn, changes all the Weekly, Monthly, Quarterly and Yearly dashboard type worksheets that pull data from Table1.

So-----Is there a way to change/add updated data into Table2 that would only affect the calculations in Table1 and subsequent areas of the workbook such that, in the example, when the Code and Cases were entered into Table1 the calculated Net Lbs figure would reflect the post Oct 5, 2002 poundage calculation? Please keep in mind that the conversion factors could change many times over.

 

[Villalobos]

Hi,

Please, post a sample file with the desired results, that will help a lot.

 

[Shteven]

There is a solution I can imagine if your Code values are numbers. Change the (TEXT) references to whatever the appropriate cell(s) is/are. It's the best I can do without seeing a sample file:

1) Insert a helper column into Table2 before the Lbs column with the formula =(CODE) & (DATE). New Table2 format will be Date, Code, Helper, Lbs.
2) Whenever a new line needs to be added to Table2, make sure that the table is always resorted in ascending order by Date.
3) Your Net Lbs formula in Table1 can then be =VLOOKUP((CODE) & (DATE),(TABLE2HELPERCOLUMN) : (TABLE2LBSCOLUMN),2,TRUE) * (CASES)

 

[CaptRDMretired]

There is a solution I can imagine if your Code values are numbers. Change the (TEXT) references to whatever the appropriate cell(s) is/are. It's the best I can do without seeing a sample file:

1) Insert a helper column into Table2 before the Lbs column with the formula =(CODE) & (DATE). New Table2 format will be Date, Code, Helper, Lbs.
2) Whenever a new line needs to be added to Table2, make sure that the table is always resorted in ascending order by Date.
3) Your Net Lbs formula in Table1 can then be =VLOOKUP((CODE) & (DATE),(TABLE2HELPERCOLUMN) : (TABLE2LBSCOLUMN),2,TRUE) * (CASES)

I will try this out tonite - thank for the assistance, I will let you know if this works.