Improve company productivity with a Business Account.Sign Up

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

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
Asked:
garyrobbins
2 Solutions
 
NorieVBA 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
 
nutschCommented:
Your error comes from the sore lack of a 11-08-01 tab
0
 
nutschCommented:
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
 
barry houdiniCommented:
To return "test" in D6 try

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

regards, barry
0
 
garyrobbinsAuthor 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.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now