Placing results of a calculation a given number of columns to the right

**Baxterman** · 01-08-2009, 07:36 PM

I want o add values in two cells and place that value in a cell offset by a value that I specify in a third cell. Is there a way to do this in Excel2000 ? Thanks.

**mewingkitty** · 01-08-2009, 07:42 PM

Depends on how you intend on defining how many cells over it will be.
An example workbook would be handy

**shg** · 01-08-2009, 08:09 PM

Welcome to the forum.

If I follow your question, no.

Formulas return values to the cells in which they appear; you can't create 'pointers' to the cell that is to receive the result. You may be able to get the behavior you want, but not in the way you're thinking of it. As MK suggests, post a workbook and explain in context.

**Baxterman** · 01-08-2009, 08:09 PM

I am attaching a spreadsheet example of what I am trying to do.
In cell D4, I have beginning capacity in a year. In cell D6, I show what capacity I decide to add in the beginning of that year. In this case, I decided to add 2 units. But the addition has a lead time. It takes 3 years in this example - shown in cell C12. So, though I decided to add the capacity in Year 1, it does not become available until Year 4. In that year, capacity changes from 5 to 7. And so on.
The spreadsheet shows this, but the formulas are hard-wired for a 3 year lead time. If I now want to see what happens if this changes to 2 years or 4 years, I need to go change all the formulas.

What I am trying to do is find a way where when I change the value in the cell for lead time, the calculations get changed automatically to reflect the new lead time offset - e.g., if I change it to 1 year, the data will automatically update so the new capacity available at the end of Year 1 is 2 and therefore Beg capacity for Year 2 changes to 7.
Thanks for taking the trouble to help out.

**shg** · 01-08-2009, 08:20 PM

In D8 and copy right, =OFFSET(D8, -2, -MIN(COLUMNS($C8:D8), 3) )

This says get the value of the cell that is two rows above and 3 columns to the left, clamped to be not further left than column C.

**Baxterman** · 01-09-2009, 01:50 PM

Thanks for the suggestion. I tried it, but maybe I am not being clear in what I need.
As you said, the formulas pick up the value two rows above and 3 columns to the left. That works when the lead time offset is 3. But if I now want to see what happens if the lead time changes to 2, I need to change the formulas in row 8. I was looking for a way where the # columns to be offset is is based on the value in cell D12. Is there any way to do this in the formulas in row 8 ? Instead of picking a constant value (3 in the expample we are using so far), can I reference the value in cell C12 ? I just tried replacing the "3" in the formula by "C12" but that does not work. If I could find a way to do this, then changing the lead time in C12 will automatically adjust all the values in row 8 - that is the result I am looking for. Thanks again.

**Baxterman** · 01-09-2009, 01:51 PM

Sorry, I mistyped above - the cell I want to use as reference for the offset is C12 - I mistakenly typed D12 in the middle of the message above. Sorry .