Excel - Table error - #value!

[MJexcel0001]

Per @NikolinoDE, adding pertinent information to the request. Thank you, NikolinoDE.

System information: Windows 11 Pro-64 bit. Microsoft 365 MSO version 2403, build 16.0.17425.20176 64 bit.

Overview: I have two tables on the same worksheet that pull from data on a different worksheet within the workbook. The two tables should be identical. The first table pulls the data correctly, but the second table results in #value! errors.

Detail: The original data is on tab LEVEL 1 PRICING + ADDS, located in table L1_TABLE_NP, column header row 10; data rows 11-30 as below—with range name L1_ROOM_NAME_NP.

This original data is referenced in a related worksheet and should populate two tables: NAME17_NP and NAME19_NP.

Table NAME17_NP is located on rows 10-30 of the separate worksheet. Row 10 is the column header and rows 11-30 reference =@L1_ROOM_NAME_NP. The names display correctly for this table as below.

For table NAME19_NP, the same formula results in #VALUE! error.

This same result occurs for every column in TABLE17_NP and TABLE19_NP. TABLE17_NP populates correctly, TABLE19_NP has all #VALUE! errors.

Thank you for any suggestions.

[#2]

@MJexcel0001 

How to correct a #VALUE! error in the IF function from Microsoft

How to fix the #VALUE! error form Exceljet

#VALUE error in Excel: causes and fixes from Ablebits

If I may recommend, add the formula/s that are causing the problem. Explain your problem using these formulas. At the same time, add information such as Excel version, operating system, storage medium, file extension, etc. This information can help provide you with a tailor-made solution proposal from other users.

Finally, here is a link with information on how you can get a quick and precise solution proposal.

Welcome to your Excel discussion space!

Thank you for your understanding and patience

NikolinoDE

I know I don't know anything (Socrates)

[#3]

@MJexcel0001 

It looks like you work with named ranges, not with structured table. If you have named range "MyRange" and reference on it as

=@MyRange

it returns the value from exactly the same row of MyRange where the formula is entered. If above formula is in row 31 it returns the value from MyRange from row 31. If MyRange is in rows 2-17 (i.e. not defined for row 31), formula returns #VALUE! error - nothing from above range in row 31.

If you would like to reference on MyRange in another rows, as one of variants that could be

=INDEX(MyRrange, ROW()-ROW(header) )

where "header" is reference on the cell with target header.

[#4]

Thank you kindly for the response. I will work my way through the INDEX process-this will be the first time for me to use it. Your explanation is much appreciated.

[#5]

Here's a step-by-step guide to troubleshoot and resolve the problem:

Check Data Source: Verify that the data source worksheet "LEVEL 1 PRICING + ADDS" and the table "L1_TABLE_NP" exist and contain the correct data. Ensure that the data range "L1_ROOM_NAME_NP" is properly defined.
Table References: Double-check the references used in the formulas of both tables. Make sure that the references are accurate and pointing to the correct data range on the "LEVEL 1 PRICING + ADDS" worksheet.
Formula Errors: Look for any errors in the formulas of the second table that could result in the #VALUE! error. Common errors include incorrect cell references, missing or extra characters, or formula syntax errors.
Data Type Mismatch: Verify that the data types of the cells being referenced in the second table match the expected data types. Sometimes, mismatches in data types can cause #VALUE! errors.
Formula Evaluation: Use the Formula Auditing tools in Excel to evaluate the formulas in the second table step by step. This can help pinpoint where the error is occurring and identify any issues with formula calculations.
Conditional Formatting: If you're using conditional formatting in the second table, check if any conditional formatting rules are causing the #VALUE! errors. Adjust or remove any problematic rules as needed.
Workbook Calculation Settings: Ensure that the workbook calculation settings are set to Automatic to allow Excel to recalculate formulas automatically. This can sometimes resolve calculation errors.

[#6]

@lunchtimeresults 

In this case question is not why in theory #VALUE! error could appear. It's definitely incorrect cell references. Actually question is how to restructure data and/or which formulas to use to have desired result.

[#7]

@Sergei Baklan 

Oh Sorry dear

[#8]

Thank you--this is a good list to keep for future reference.