x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 240

# How do I use INDIRECT function to insert data from a reference cell?

Hello experts,

It's Friday afternoon and I want to go home...   but I need some help here.

I want to create a cell formula to pull in reference data based on the header of the column.  See attached example.  Can you suggest a fix or a better way?

Thanks,
Gary
LCS-by-Activity-Code-08-2011-EE-.xls
0
garyrobbins
2 Solutions

VBA ExpertCommented:
Gary

The formula in E6 refers to a worksheet using the value in E4 which is 11-08-01

There is no worksheet called that.
0

Commented:
Your error comes from the sore lack of a 11-08-01 tab
0

Commented:
the #REF avoidance formula is
=IF(ISERROR(COUNTIF(INDIRECT("'"&E\$4&"'!\$D\$5:\$AC\$100"),\$B6)),"",COUNTIF(INDIRECT("'"&E\$4&"'!\$D\$5:\$AC\$100"),\$B6))

your activity code formula could be
=VLOOKUP(\$B6,'Activity Code Table'!\$B\$4:\$O\$14,MATCH(D\$5,'Activity Code Table'!\$B\$3:\$O\$3,0),0)

T
0

Commented:
To return "test" in D6 try

=INDIRECT("'Activity Code Table'!"&'Activity Code Table'!C23&COLUMN())

regards, barry
0

Author Commented:
Imnorie - I misled you in thinking I wanted help with the formula in E6. I wanted D6. My error.

nutsch - I like how you combined Vlookup with Match and avoided my kludgy double approach using an Indirect approach.  Your solution also allows formula to be drawn down and across.

Hope you think my point assignment is fair.

Thanks for the speedy response - now I can go home.  Have a great weekend!

Gary
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.