Referencing a cell complication

I'm trying to setup a spreadsheet for calculating purchases and payments based on future data in the excel sheet. I've attached a copy of the setup to look at.

To describe the problem the best thing would be to give a scenario. In order to calculate purchases I have to multiply a % that is input in a separate cell by the sales figure in another cell. The sales number that needs to be used is pulled based on how many months ahead is put in another box. So if I need the sales figure pulled 2 months ahead of the current month I'm calculating payments for I need excel to figure that out based on the # i input in to cell B11 in my attached example. I would then have it multiply that number by the % listed in the other cell I have marked.

The same scenario applies for Payments row except that i just need it to reference a certain number of cells behind somewhat like the previous example but without a multiple.

I hope this question makes sense.

Excel Question.xlsx

Richard,

That worked perfectly. As for the Payments I needed it to pull the purchase from lets say the month before or 2 months before as if the company was going to pay for those purchases they made then. (e.g. purchases made in jan get paid for in march for example. I just took your OFFSET function and changed the parameters to the following:

Formula:

Please Login or Register  to view this content.

The one caveat it that in order for it to work, the input has to be put in as a NEGATIVE so that it knows to look back a column. So far so good and can't thank you enough.

Thanks Richard. I'll make that change to make things easier and cleaner.