How to change cell reference within formulas

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CGSoniat - 27 Oct 2006 22:33 GMT

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?

Reply to this Message

RagDyer - 28 Oct 2006 00:06 GMT

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))

Signature

HTH,

RD

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

>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
[quoted text clipped - 6 lines]
> the
> VLOOKUP function, or is just entered?

Reply to this Message

RagDyer - 28 Oct 2006 00:21 GMT

Typo!

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

Signature

Regards,

RD

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

> Say your datalist runs from A1 to M15,
> With your lookup values in A2 to A15,
[quoted text clipped - 16 lines]
>> the
>> VLOOKUP function, or is just entered?

Reply to this Message

CGSoniat - 30 Oct 2006 19:56 GMT

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 D3:P32 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,
[quoted text clipped - 16 lines]
> > the
> > VLOOKUP function, or is just entered?

Reply to this Message

RagDyer - 31 Oct 2006 03:36 GMT

Post the actual formula you're having trouble with.

Signature

> Thanks for your suggestion.
>
[quoted text clipped - 47 lines]
>> > the
>> > VLOOKUP function, or is just entered?

Reply to this Message