0

I have realised that structured references are clean to write when making formulas, however when using them in a formula to refer to values in columns using structured references this only returns the values on the same row as your formula. This is not ideal.

Is there a way to refer to values in a structured reference column from the top of the column with a formula on a different row? I looked into this answer but it was only for the top value in the column.

So for example say I have a Table1 with 3 data columns and then I want to make a Table2 with a calculation column that is as simple as =Table1[Data1]*Table1[Data2], how would I get that formula to work down the column of Table2 even if Table2 is not on the same rows as Table1?

| Data1 | Data2 | Data3 |
|------------------------
|  22   |   7   |  Joe  |
|  24   |   2   |  Bob  |
|  44   |   7   |  Ben  |
|  29   |   8   |  Sue  |

|  Calc1  |
|----------
| Formula | (Formula = 22 x 7)
| Formula | (Formula = 24 x 2)
| Formula | (Formula = 44 x 7)
| Formula | (Formula = 29 x 8)

Edit just so readers understand, this is an oversimplification of what I am trying to achieve, managing to use structured references as stated above will help solve the bigger problem.

Community
  • 1
  • 1
Fiztban
  • 283
  • 2
  • 9
  • 22
  • You could just use the formula A1*B1 in table 2 and drag it down. What difficulty you face? – Gowtham Shiva Mar 31 '17 at 16:03
  • That much I gathered but I want it specifically to work in the above mentioned way as I'd be using multiple tables spread across multiple sheets that are different in size. The problem is bigger than I am stating, it would help to know the above. You know, simple question, simple answer. – Fiztban Mar 31 '17 at 16:05
  • can you post the formulas that you have tried so far? – Gowtham Shiva Mar 31 '17 at 16:08
  • With pleasure, it'll have to be this evening as I'm not on my pc at the moment but assume I have used `Table1[Data1]*Table2[Data2]` and various other permutations with @ and the absolute column reference such as `Table1[[Data1]:[Data1]]` – Fiztban Mar 31 '17 at 16:12

0 Answers0