0

I am on office 0365 enterprise version - Microsoft® Excel® for Microsoft 365 MSO (16.0.14228.20292) 32-bit. I am working on a table and would like to use structured referencing to dynamically retrieve the current selected row. I have gone through video tutorial where the syntax of using TableName[@columnname] works. Unfortunately when I attempt on my end either with @ or "#This Row" it show VALUE error as shown on screenshot below. Any ideas?

enter image description here

enter image description here

David Onyango
  • 77
  • 8
  • 1
    `@` does **NOT** return the ***current selected row***. It returns the value from the same row as the formula is in. Although you can detect the current selected row using VBA, there is no way to do that with a worksheet formula. – Ron Rosenfeld Oct 06 '21 at 01:39
  • Thanks for your feedback. My scenario is where in the same table one has a field with a drop down list that gets filtered based on the value captured in another cell in the same row of the table. Is that feasible? – David Onyango Oct 06 '21 at 05:58

1 Answers1

1

@Year refers to the current row of the Year column. For example, assuming that the value 1999 is in cell B3, putting the formula =Table1[@Year] in cell D3 will return the value 1999. But you've put the formula in what looks like cell B14. The table doesn't extend to row 14, so there is no value to return - hence the #VALUE! error.

Solver Max
  • 391
  • 1
  • 2
  • 6
  • Hi, I agree with your logic. However, my intention is to use this in a formula in another cell outside the table range – David Onyango Oct 06 '21 at 05:53
  • You'll need to be more specific. Describe exactly what you want to do, which cells things should occur in, and why. – Solver Max Oct 06 '21 at 06:38
  • My scenario is where in the same table one has a field with a drop down list that gets filtered based on the value captured in another cell in the same row of the table. Is that feasible? – David Onyango Oct 06 '21 at 07:13
  • I have no idea what that means. Explain your requirement with a specific example. – Solver Max Oct 06 '21 at 07:16
  • I had added a second picture in the main body. In that scenario, I need the filter list to show 2 names instead of the whole list of 4 names for the corresponding BirthYear selected in the Excel Table. The second screenshot shows the lookup range to filter against – David Onyango Oct 06 '21 at 07:37