Referring to an "Active Cell" in an INDEX MATCH formula?

[dougmarkham]

Hi Guys,

I have a task I've not done before involving referring to an active cell within an index match formula.

Goal: to be able to click on any cell in the 'Job number' column (table 2 in sheet 2) and have that Job number used to match the job number column table 1 in the sheet 1.

Situation:
Worksheet 1 - Contains a table showing all current jobs: There are three named ranges corresponding to JobNumber, JobName and Detail.

Table 1

JOB NUMBER (a2)	TASK STATUS (b2)	JOB TYPE (c2)	DATE (d2)	TIME (e2)	JOB NAME (f2)	JOB DETAIL (g2)	PRIORITY (h2)	JOB DEADLINE (i2)
1	1	Admin	01/12/17	09:00	JOB A	LAJDFLKJDLFJSDJFLJDLJKFLDJFLKDJFLKJD	1	15/12/17
2	0	Lookup	01/12/17	09:00	JOB B	AKAKLGHHAFGKLJLEGJLKAJKDGLJALKGJ	2	15/12/17
3	1	Other	01/12/17	09:00	JOB C	AGLAJKLFJLKJKDGLJALRKDJLSAJDK	3	15/12/17

<tbody>
</tbody>

Worksheet 2 - Contains table 2 which allows me to type in a job number into column 2, and an index match formula pulls the job name from table 1.

Table 2

PRIORITY (B4)	JOB NUMBER (C4)	JOB NAME(D4)
1	1	JOB A
2	2	JOB B
3 etc...	3	JOB C

<tbody>
</tbody>

What I want to do is create a fairly big cell to the right of table 2, and put an index match formula in it that will pull the JOB DETAILS corresponding to an active cell which the user will select one of the job number cells in table 2. So the idea is that the user will click on a job number in table 2 and the corresponding Job Details from table 1 will show in the big cell.

So far

Formula in C5 of table 2: =IF(C5:C294="","",INDEX(JobName,MATCH($C$5:$C$294,JobNumber,0)))
Formula in Big cell: =INDEX(Details,MATCH(Active Cell,JobNumber,0))

How do I refer to the selected cell (active cell) in table 2 column C within the index match formula in the big cell?

Kind regards,

Doug

 

[wideboydixon]

The good news is that you can refer to the contents of the active cell in a formula. The bad news is that it won't update automatically when you select another cell. So your "Big cell" formula would be:

=INDEX(Details,MATCH(INDIRECT(CELL("address")),JobNumber,0))

But after selecting a job number, you'd have to hit F9 to force the calculation. The other method would be to capture the change of selection in VBA and update the cell that way.

WBD

 

[dougmarkham]

The good news is that you can refer to the contents of the active cell in a formula. The bad news is that it won't update automatically when you select another cell. So your "Big cell" formula would be:

=INDEX(Details,MATCH(INDIRECT(CELL("address")),JobNumber,0))

But after selecting a job number, you'd have to hit F9 to force the calculation. The other method would be to capture the change of selection in VBA and update the cell that way.

WBD

That's great! I think my team will be ok with refreshing using F9. If not, I'll set up a macro to do it lol. Thank you for your help wideboydixon!!

Just out of interest, how does INDIRECT work guys?

Kind regards,

Doug.

 

[wideboydixon]

CELL("address") returns the address of the active cell. E.g. "$C$5"

INDIRECT("$C$5") fetches the contents of cell C5.

WBD

 

[dougmarkham]

CELL("address") returns the address of the active cell. E.g. "$C$5"

INDIRECT("$C$5") fetches the contents of cell C5.

WBD

Interesting, so INDIRECT fetches what ever is in it's brackets?
Thanks for the explanation!

Kind regards,

Doug.