[Solved] Sporadic DGET #VALUE! results

[SCFrolich]

Okay, here is the issue;

I have three sheets: The second sheet is where my tables of data are entered. The third sheet is where my calculations are performed. The first sheet is used as the data entry and display sheet.

On sheet one the selections are made, mainly pull-downs to select data from the various tables on the second sheet. Based on the choices made on sheet 1 (referencing sheet 2), sheet 3 does all the math. The data is then shown on sheet 1. This is just to try to keep it all neat and clean. I am working on a system and I just need this to do all the various maths for me in a quick manner so that I can check I have constructed the system correctly.

Now, on Sheet 1 I have a column of pull-downs that all validate off of the same list on sheet 2.
On sheet three I am using DGET (from the entries on sheet 1) to pull all the rest of the relevant data from sheet 2.
I haven't even gotten passed the first row!

In the first column of data, for half of my choices all I am getting is #VALUE instead of what is actually on the table. All the other columns are working correctly for all of my different rows. It is only the first column that is having this problem.

These are simple formulas and I have been using them for years, but all of a sudden it's being a veritable pain in the butt and I just cannot fathom why.
I transferred all of the data to a new spreadsheet and the problem still persists. I can't figure this out....

I have attached the whole spreadsheet to this. I hope someone can help. There isn't really that much in it yet. I am just at the beginning.

I would be grateful for any assistance at all on this one....

[MrProgrammer]

On sheet three I am using DGET (from the entries on sheet 1) to pull all the rest of the relevant data from sheet 2.

For a search in Tables.A24:J52, your criteria for DGET should only check the single value in column A, not the ten values in columns A through J. Calculations.B18 works if I use formula
=IF($A18="";"";DGET($Tables.$A$24:$J$52;B17;$A17:$A18)) instead of
=IF(A18="";"";DGET(Tables.A24:J52;B17;A17:J18)). You can fill that formula right from B18 to J18.

[robleyd]

Note that some of the strings in your data have leading spaces, which may cause unexpected results in some calculations.

[SCFrolich]

On sheet three I am using DGET (from the entries on sheet 1) to pull all the rest of the relevant data from sheet 2.

For a search in Tables.A24:J52, your criteria for DGET should only check the single value in column A, not the ten values in columns A through J. Calculations.B18 works if I use formula
=IF($A18="";"";DGET($Tables.$A$24:$J$52;B17;$A17:$A18)) instead of
=IF(A18="";"";DGET(Tables.A24:J52;B17;A17:J18)). You can fill that formula right from B18 to J18.

Thank you, MrProgrammer sir! That did indeed fix it. Much thanks.

[SCFrolich]

You can fill that formula right from B18 to J18.

I am sorry to ask this, but exactly how can I fill it in automatically? All I have ever done in the past is cut and paste and edit each cell. Is there a way to do it more quickly? I am really getting tired of my own brute force methods...

[karolus]

You can fill that formula right from B18 to J18.

I am sorry to ask this, but exactly how can I fill it in automatically? All I have ever done in the past is cut and paste and edit each cell. Is there a way to do it more quickly? I am really getting tired of my own brute force methods...

there is tiny square on the lower|right "frame" of cell[s] in focus … click and move right

crosshair_cursor.png (2.01 KiB) Viewed 845 times

[SCFrolich]

.....oh wow.....

I never knew that. I never even NOTICED that!

Thank you. I appreciate the help. I feel like an idiot, but I thank you none-the-less, good folks!!

[SCFrolich]

Okay, so I know I said it was solved and it was. It was fixed! I swear!

However, now it's back.

On my sheet 1 with all of the pulldowns I am now suddenly getting random reappearances of the issue on random rows but with the same selections I was having issues with prior.

I changed nothing in any of the associated cells at all.

I can work around it, and seeing as this is mainly for my eyes only it's not a problem (per se) but why would this issue suddenly reappear?

[Alex1]

The same problem as in viewtopic.php?t=110151: disable regular expressions in formulas.