#VALUE! error for structured formula reference - referring to table in another worksheet

Question

Hello,

I am trying to refer to a table's column in another worksheet (note: this column has only text). Suppose Table1 is located in an Input Sheet. I opened a new Calculations Sheet and created a table to load column1 from Table1. When I first created this table, I used a reference like: =Table1[column1] to load the textual values and it worked! Then I created another table in the Calculations Sheet and tried doing it again, but I'm getting an error #VALUE! 

Unless I use individual cell references, like =Input!A1 for the second table I made, it's not working. I would rather use structured formula reference. Is there a limitation in Excel to only have 1 reference formula for a table? How come it worked for one table but not the other? How can I go about fixing this?

Thanks.

Answer 1

The text in the columns do not have any numbers, so unfortunately it didn't work. However, I figured out the problem. When I reference Table1 from the Input Sheet, it only gets the data based on the position. In other words, my Calculations Sheet referenced Table1 from A1 - A20. When I use another table below (with cells A21 - A40) it didn't pick up those values. It only picked up the values from the Input sheet with A1 - A20 because the positions in both worksheets must match. 

Hopefully this helps somebody out. In my case, I just needed a way to reference a table dynamically in another worksheet. After spending the entire weekend trying to solve this issue, I am thinking it would be best to use individual cell references.

Answer 2

Hi,

In regards with your query, you have to convert those column that contains text to value. There shouldn't be a limitation unless if, you want a different outcome. You can refer to the links below:

• How to convert text to numbers in Excel
• VALUE function

Keep us updated so that we can further assist you.