automatically change column references in formulas

[Guest]

I have two worksheets. In worksheet A I have a table where the columns are
the months (0701 - 0712)

In worksheet B I have one table which contains formula's with calculations
for one month. So I made a dropdownlist with Data->Validation to select the
month.
What I'm looking for is to automatically change the references to the
columns in worksheet A, which are used in the forumulas in the table in
worksheet B.

So when the month 0702 (Column B) is selected, the formulas should reference
to column B. When the month 0703 (Column C) is selected, the formulas should
reference to column B.

Does anyone know how I can do this? Any help would be greatly appreciated.

 

[merjet]

Suppose the following. On Sheet A the dates are in row 1 and a number
is below each date in row 2. On Sheet B the validation cell is A1 and
you want to put in cell A2 the correct number from Sheet A, row 2.

The following VBA code in Sheet B's code module should work.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Target.Address = "$A$1" Then
For Each c In Sheets("A").Range("A1:M1")
If c = Target Then
Range("A2").FormulaR1C1 = "=A!R2C" & c.Column
Exit For
End If
Next c
End If
End Sub

Hth,
Merjet

 

[Guest]

Use the selectionchange event of the worksheet where you will be making the
selecitons and have it update your cell that contains the dropdown on the
other sheet.

http://www.cpearson.com/excel/events.htm

 

[Brian]

You need to use OFFSET() in your calculations in worksheet B. Make
each formula there relative to some Base Cell (probably in the left
column of your worksheet A table) and offset by some index.

The index will be determined by what you choose from your drop-down
list. You'll be picking from your months (0701-0712) so you'll need
to use MATCH() to resolve that into a actual index (1 to 12) to use in
the OFFSET() function.

I made up a rough example, if it helps:


first I defined these Names

Pick = $A$1 (the cell to model your drop down list)
Headings = $C$1:$N$1 (the headings of your months (0701-0712)

I filled "Headings" with the values 1/1/2007 through 12/1/2007,
formatted MMYY.
I configured cell A1 to validate based on a list, and gave "Headings"
as my list. I formatted A1 as "MMM YY" for clarity.

I made up 5 rows worth of data below each month heading.
In cell A2 I entered this formula:

=OFFSET(A2,0,MATCH(Pick,Headings,0)+1)

This is the interesting part. This formula will look at what you have
chosen from the dropdown list in cell A1 ("Pick") and it will then
look into the table of months ("Headings") based on your selection and
it will pull out the value from the proper month. Of course this is
just a simple assignment and your formula is sure to be more complex,
but without knowing anything about your purpose I can't be more
specific.

I copied cell A2 into A3, A4, A5, A6.

Now I when I choose a new value from the drop down list, my formulas
in A2:A6 will extract the values from the "months" table to its
right. I could do whatever calculations I want at this point.

Brian Herbert Withun

 

[Brian]

My previous post seems not to have taken. A shame. I had typed a lot
there...

In a nutshell, what you need to do is change any references in your
worksheet B from absolute references such as "=D7*1234" to relative
references like ="OFFSET(A7,0,Month)*1234" and then you can modify the
value of Month to obtain your indirection.

You'll probably end up with something like this:

A1 = OFFSET(B1,0,MATCH(A1,Headings,0)) * 1234

Where Headings is a Named Range referring to the headings of your
months (0701-0712)
And A1 uses Data->Validate to provide a drop-down list of the headings
to pick from.

This function will look at what you have chosen from your drop down
list (A1), then calculate which column (in Headings) corresponds to
that selection and will calculate (multiply by 1024) based on the
value found there.

Brian Herbert Withun

 

[Brian Withun]

You want your formulas in SheetB to be relative references, not
absolute references.

If, for instance, you have SheetB hard-coded to calculate its values
for the month of January (SheetA ColumnB),
SheetB!$C7 = SheetA!B2-SheetA!B3

change it to:
SheetB!$C7 = OFFSET(SheetA!A2,0,Month)-OFFSET(SheetA!A3,0,Month)

This new formula will allow SheetB to calculate based on whichever
column/month in SheetA you want, depending on the value of "Month".
(where 1 <= Month <= 12)

You could then create a Validate cell (SheetB!C4) which allows you to
pick a month from a drop-down list. If SheetA has headings (in SheetA!
$B$1:$M$1), and your drop-down picklist lets you select one of those
very headings, then all you have to do is lookup (using MATCH()) your
drop-down selection within the range of your column headings and
you'll get an actual index between 1 and 12. Substitute this in for
"Month" below:

(I've created the name "Headings" to refer to the range =SheetA!$B$1:$M
$1, which contain my column headings [Jan-Dec] )

SheetB!$C7 =OFFSET(SheetA!A2,0,MATCH(C4,Headings,0))-OFFSET(SheetA!
A3,0,MATCH(C4,Headings,0))

Having done this, I can pick a new month in cell SheetB!C4 and my
formula in SheetB!C7 does its calculation for the month I chose.

Hope this is clear.

Brian Herbert Withun

 

[Brian Withun]

You want your formulas in SheetB to be relative references, not
absolute references.

If, for instance, you have SheetB hard-coded to calculate its values
for the month of January (SheetA ColumnB),
SheetB!$C7 = SheetA!B2-SheetA!B3

change it to:
SheetB!$C7 = OFFSET(SheetA!A2,0,Month)-OFFSET(SheetA!A3,0,Month)

This new formula will allow SheetB to calculate based on whichever
column/month in SheetA you want, depending on the value of "Month".
(where 1 <= Month <= 12)

You could then create a Validate cell (SheetB!C4) which allows you to
pick a month from a drop-down list. If SheetA has headings (in SheetA!
$B$1:$M$1), and your drop-down picklist lets you select one of those
very headings, then all you have to do is lookup (using MATCH()) your
drop-down selection within the range of your column headings and
you'll get an actual index between 1 and 12. Substitute this in for
Month below:

(I've created the name "Headings" to refer to the range =SheetA!$B$1:$M
$1, which are my column headings [Jan-Dec] )

SheetB!$C7 =OFFSET(SheetA!A2,0,MATCH(C4,Headings,0))-OFFSET(SheetA!
A3,0,MATCH(C4,Headings,0))

Having done this, I can pick a new month in cell SheetB!C4 and my
formula in SheetB!C7 does its calculation for the month I chose.

Hope this is clear.

Brian Herbert Withun

 

[Brian Withun]

You want your formulas in SheetB to be relative references, not
absolute references.

If, for instance, you have SheetB hard-coded to calculate its values
for the month of January (SheetA ColumnB),
SheetB!$C7 = SheetA!B2-SheetA!B3

change it to:
SheetB!$C7 = OFFSET(SheetA!A2,0,Month)-OFFSET(SheetA!A3,0,Month)

This new formula will allow SheetB to calculate based on whichever
column/month in SheetA you want, depending on the value of "Month".
(where 1 <= Month <= 12)

You could then create a Validate cell (SheetB!C4) which allows you to
pick a month from a drop-down list. If SheetA has headings (in SheetA!
$B$1:$M$1), and your drop-down picklist lets you select one of those
very headings, then all you have to do is lookup (using MATCH()) your
drop-down selection within the range of your column headings and
you'll get an actual index between 1 and 12. Substitute this in for
Month below:

(I've created the name "Headings" to refer to the range =SheetA!$B$1:$M
$1, which are my column headings [Jan-Dec] )

SheetB!$C7 =OFFSET(SheetA!A2,0,MATCH(C4,Headings,0))-OFFSET(SheetA!
A3,0,MATCH(C4,Headings,0))

Having done this, I can pick a new month in cell SheetB!C4 and my
formula in SheetB!C7 does its calculation for the month I chose.

Hope this is clear.

Brian Herbert Withun