How to change cell reference within formulas

[Guest]

I have some formulas that refer to other workbooks. I want the formula to
point to a different column, depending on the current month; e.g., in
January, the data will be in column E (and relative row references), in
February, the data will be in column F, and so on.

How do I create the formula with the cell reference as a "Variable" that can
refer to other cells where the correct column identifier is indicated via the
VLOOKUP function, or is just entered?

 

[RagDyer]

Say your datalist runs from A1 to M15,
With your lookup values in A2 to A15,
And your months in B1 to M15.

Enter value to lookup in A20,
And month to return in B20,
And try this:

=INDEX(B2:M15,MATCH(A20,A2:A15,0),MATCH(B20,B1:M1,0))

 

[RagDyer]

Typo!

Months are in B1 to M1 ... *not* M15.

 

[Guest]

Thanks for your suggestion.

When I use the Match function to determine the column number for the current
month's data, e.g., =MATCH(R35C1,R36C2:R36C13), where R35C1 contains "Jan",
and R36C2:R36C13 contains "Jan", "Feb", "Mar", etc, it returns a value of 1,
which is correct, the first column in the array.

However, when I imbed the Match function inside of the Index function, e.g.,
=INDEX('[Copy of 2005 Production
Archive.xls]Banner'!RC5:RC16,3,MATCH(R35C1,R36C2:R36C13)+4), I get a
reference error #REF!.

If I manually substutute the correct column number (5) in the INDEX
function's syntax (removing the MATCH syntax), it retrieves the correct data.


My data is in D332 of the spreadsheet, 'Copy of 2005 Production
Archive.xls', worsheet named 'Banner'

My reference cell is A35 in the current worksheet, and my Month names to
lookup, are in B36:M36, of the current worksheet.

Thanks for the help.

 

[RagDyer]

Post the actual formula you're having trouble with.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Thanks for your suggestion.

When I use the Match function to determine the column number for the
current
month's data, e.g., =MATCH(R35C1,R36C2:R36C13), where R35C1 contains
"Jan",
and R36C2:R36C13 contains "Jan", "Feb", "Mar", etc, it returns a value of
1,
which is correct, the first column in the array.

However, when I imbed the Match function inside of the Index function,
e.g.,
=INDEX('[Copy of 2005 Production
Archive.xls]Banner'!RC5:RC16,3,MATCH(R35C1,R36C2:R36C13)+4), I get a
reference error #REF!.

If I manually substutute the correct column number (5) in the INDEX
function's syntax (removing the MATCH syntax), it retrieves the correct
data.


My data is in D332 of the spreadsheet, 'Copy of 2005 Production
Archive.xls', worsheet named 'Banner'

My reference cell is A35 in the current worksheet, and my Month names to
lookup, are in B36:M36, of the current worksheet.

Thanks for the help.

Say your datalist runs from A1 to M15,
With your lookup values in A2 to A15,
And your months in B1 to M15.

Enter value to lookup in A20,
And month to return in B20,
And try this:

=INDEX(B2:M15,MATCH(A20,A2:A15,0),MATCH(B20,B1:M1,0))