Referencing Worksheet Cells In Database Arguments

[belmore]

This is driving me crazy. I am trying to use a worksheet cell as an argument. I can do it one way, but not another. 

 

First off:

This works: =DATABASE((L='Design Layer 1'))

I get a list of everything on that layer.

 

And instead of a hard value, I can substitute worksheet cell B1:

This works: =DATABASE((L=B1))

...and then I can modify cell B1 to change the database.

 

I can instead make a database based on a fieldvalue if it's hard-coded:

This works: DATABASE(('TestRecord'.'TestField'='101'))

....and it will return all objects that have that record field being 101.

 

But when I go to reference a cell in that formula, I get nothing but errors:

This does not work: DATABASE(('TestRecord'.'TestField'=B1))

 

What am I missing? I have tried everything I can think of. Thanks in advance!

[michaelk]

Is B1 formatted as text or as a general number?

[belmore]

3 minutes ago, michaelk said:

Is B1 formatted as text or as a general number?

Tried both, no dice.

[michaelk]

I do this all the time.  Can you post an example file?

 

(Or PM me if it's a file you can't share for whatever reason)

[belmore]

Here I made this using the examples above. The last database row does not function.

 

Referenced Cell Stumper.vwx

Edited December 7, 2022 by belmore

[michaelk]

I have seen this before and I don't know why this trick works.  But it does.

 

 

=DATABASE(('RecordFormat1'.'TextField'=CONCAT(B2)))

[michaelk]

* I don't know why it works most of the time without CONCAT and why it occasionally needs it.  

 

  I suspect that the CONCAT function might be forcing all values to be text.  

[Pat Stanford]

I have found that Criteria don't like to be partially text and partially cell references.

 

I have not tested, but I know that if you set the referenced cell to the entire criteria it will work.

 

so if you set B1 to:

 

((L='Design Layer-1'))

 

And then use

 

=Database (B1)

 

You should be OK.

 

I would "guess", but I have not tested that

 

=Database(Concat('((L=',CHR(39),B1,CHR(39),'))')

 

would work also.

 

The CHR(39) is the character for a single quote mark that you need to surround the string coming from cell B1. You can also use three single quotes in a row to "escape" the single quote marks, but I find that really hard to read and debug.

 

What this is doing is making a single string out of. "((L=",  single quote, string from B1, single quote, "))"

 

HTH

[belmore]

53 minutes ago, michaelk said:

I have seen this before and I don't know why this trick works.  But it does.

 

 

=DATABASE(('RecordFormat1'.'TextField'=CONCAT(B2)))

 

Great to know, thanks a fortune!