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

Question

=IF(OR(Table1[Status]="Non-Compliant", Table1[Status]="Action required"),Table1[Name],"")<br><br><br><br>I get #VALUE for cell C3 but C4 formula starts working<br><br><br><br>The data for the table I am referring to the data values start on row 4 and for the sheet I am using the formula on the function works for row 4 only why is that?? does it have to be on the same row where I am pulling the data from <br><br><br><br>Found a similar post #VALUE! error for structured formula reference - referring to table in - Microsoft Community

Answer 1

The table I am grabbing the data from starts on row 4 but the row I am placing the function starts on row 3

Answer 2

How would I do this one?

=IF(OR(Table1[[#This Row],[Status]]="Non-Compliant", Table1[[#This Row],[Status]]="Action required"), Table1[[#This Row],[Name]], "")

for the #This Row

Answer 3

The issue you're experiencing with the structured formula reference is likely due to Excel trying to perform operations on entire columns, which can lead to mismatched ranges when using tables in different sheets.

1. Check Table References: Ensure that the Table1 reference in your formula corresponds to the correct table in the other worksheet. If the table is in a different worksheet, you need to include the worksheet name in the reference.

For example, if the table is in a worksheet named "Sheet2", the formula should be:

=IF(OR(Sheet2!Table1[Status]="Non-Compliant", Sheet2!Table1[Status]="Action required"), Sheet2!Table1[Name], "")

1. Use Structured References Properly: Structured references in Excel tables are designed to work with entire columns by default. However, when referring to tables in different sheets, you need to ensure that the ranges match up correctly. If the table in the other sheet starts on row 4, you should adjust your formula accordingly.

For example, if the table starts on row 4, your formula should be:

=IF(OR(Table1[[#This Row],[Status]]="Non-Compliant", Table1[[#This Row],[Status]]="Action required"), Table1[[#This Row],[Name]], "")

This ensures that the formula operates on the current row of the table.

1. Verify Data and Formatting: Double-check that the data in your table is consistent and properly formatted. Even a small inconsistency, such as a missing or different formula, can cause errors in the calculations.

By following these steps and ensuring that your formula references are accurate and aligned with the structure of your data, you should be able to resolve the #VALUE! error and get your formula working correctly across different rows in the table.Formularbeginn The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.