Help using INDIRECT or something like it

  • Thread starter Thread starter Good Try
  • Start date Start date
G

Good Try

Please help.

I have a sheet that contains a 2D array (12 columns by 11 rows) called
'Bonus_Percentage'. In the left column is a list of 11 Job titles. Across
the top is a list of months (Jan - Dec) Each row in the array is named
according to the Job Title. Each Column is named according to the Month.
In this way I can specify a formula in the form '=Job_Title Month' to return
the appropriate bonus percentage.

On a separate sheet in the same book, I have a list of Employees. One
column in this list allows the user to enter a job title for an employee.
The user can only enter a job title that exists in the Job Titles Column on
the first sheet. Based on the user's entry, I am trying to return the
appropriate Bonus_Percentage. If I enter '=Supervisor Jan', I get the
correct result. If I enter '=INDIRECT(D10) Jan' where D10 contains the
string 'Supervisor', I get #REF.

How would I make this formula return the appropriate intersection of month
and job title, without knowing the specific Job_title before it is entered?

--
Kerry Carroll

Please remove the spaces from the following address if you need to reply to
me directly
kerry . carroll @ cinbell . com
 
Kerry

I may have misunderstood you, but I'm assuming that you
want to enter the job title in (for example) cell A1 and
have cells B1:M1 show the corresponding bonuses... is
that right?

If you have the 'Jan' column in the same column on both
sheets, you could use =VLOOKUP(=VLOOKUP
($A1,Table_Range,COLUMN(),FALSE)

....where Table_Range is the lookup table on the other
sheet. Otherwise you could substitute "Column()"
for "Column()+6" or whatever the offset is.

That formula could be copied across the row for each of
the months.

I hope that's what you were looking for!

Julia
-----Original Message-----
Please help.

I have a sheet that contains a 2D array (12 columns by 11 rows) called
'Bonus_Percentage'. In the left column is a list of 11 Job titles. Across
the top is a list of months (Jan - Dec) Each row in the array is named
according to the Job Title. Each Column is named according to the Month.
In this way I can specify a formula in the
Click to expand...
form '=Job_Title Month' to return
 
Using the intersection of rows and columns based on the content of the
headers is intuitive and appealing. Unfortunately, it is also rather
limited and often leads to erroneous results.

Here's an alternative approach.

Suppose the bonus_Percentage matrix starts in A1 on Sheet1. So, the
first job title is in A2 and the first month name is in B1.

Suppose the employees and months are in Sheet2 with the employees in
col A and the months in B. Then, in C2, enter
=INDEX(Sheet1!$B$2:$M$12,MATCH(A2,Sheet1!$A$2:$A$12,0),MATCH(B2,Sheet1!
$B$1:$M$1,0))

Copy C2 down column C as required.
 
Appears to work? I tested it on a 11x12 matrix before sharing it.

I will leave the development of a simpler solution to you or someone
else who decides to pitch in and help.
 
...
...
On a separate sheet in the same book, I have a list of Employees. One
column in this list allows the user to enter a job title for an employee.
The user can only enter a job title that exists in the Job Titles Column on
the first sheet. Based on the user's entry, I am trying to return the
appropriate Bonus_Percentage. If I enter '=Supervisor Jan', I get the
correct result. If I enter '=INDIRECT(D10) Jan' where D10 contains the
string 'Supervisor', I get #REF.
Click to expand...
...

With the sample table (deleting underscores in the top row)

___ Jan__Feb__Mar__Apr__May__Jun__Jul__Aug__Sep__Oct__Nov__Dec
aaa .100 .067 .050 .040 .033 .029 .025 .022 .020 .018 .017 .015
bbb .150 .100 .075 .060 .050 .043 .038 .033 .030 .027 .025 .023
ccc .200 .133 .100 .080 .067 .057 .050 .044 .040 .036 .033 .031
ddd .250 .167 .125 .100 .083 .071 .063 .056 .050 .045 .042 .038
eee .300 .200 .150 .120 .100 .086 .075 .067 .060 .055 .050 .046
fff .350 .233 .175 .140 .117 .100 .088 .078 .070 .064 .058 .054
ggg .400 .267 .200 .160 .133 .114 .100 .089 .080 .073 .067 .062
hhh .450 .300 .225 .180 .150 .129 .113 .100 .090 .082 .075 .069
iii .500 .333 .250 .200 .167 .143 .125 .111 .100 .091 .083 .077
jjj .550 .367 .275 .220 .183 .157 .138 .122 .110 .100 .092 .085
kkk .600 .400 .300 .240 .200 .171 .150 .133 .120 .109 .100 .092

If I actually create defined names for each row and each column of this table,
i.e., if the table including month names in the top row and job IDs in the left
column were in Sheet1!A1:M12, so, e.g., the defined names Apr and eee would
refer to Sheet2!$E$2:$E$12 and Sheet1!$B$6:$M$6, respectively, then in Sheet2
with Apr in A1 and eee in A2, all four of the following evaluate to .120.

=Apr eee
=INDIRECT(A1) eee
=Apr INDIRECT(A2)
=INDIRECT(A1) INDIRECT(A2)

It appears you're trying to use labels as opposed to defined names. Labels can
only be used in the same worksheet as the table in which they appear, and they
can't be used with INDIRECT. If you want dynamic addressing into this table, you
need to define 23 defined names, one each for each row and each column of the
table. Or you need to use the formulas Tushar gave you.

There are no simple alternatives that involve both minimal setup and simple
formulas. You have to accept either some initial setup (the 23 defined names) or
moderately complex formulas. There's one more option: a user-defined function
written in VBA that takes 3 arguments - table range, row ID label and column ID
label. See one implementation indirectly referenced at

http://www.google.com/[email protected]

If this wraps in your newsreader, try this url instead.

http://makeashorterlink.com/?D21962E05
 
Back
Top