belmore Posted December 6, 2022 Share Posted December 6, 2022 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! Quote Link to comment
michaelk Posted December 6, 2022 Share Posted December 6, 2022 Is B1 formatted as text or as a general number? Quote Link to comment
belmore Posted December 6, 2022 Author Share Posted December 6, 2022 3 minutes ago, michaelk said: Is B1 formatted as text or as a general number? Tried both, no dice. Quote Link to comment
michaelk Posted December 6, 2022 Share Posted December 6, 2022 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) Quote Link to comment
belmore Posted December 7, 2022 Author Share Posted December 7, 2022 (edited) 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 Quote Link to comment
michaelk Posted December 7, 2022 Share Posted December 7, 2022 I have seen this before and I don't know why this trick works. But it does. =DATABASE(('RecordFormat1'.'TextField'=CONCAT(B2))) Quote Link to comment
michaelk Posted December 7, 2022 Share Posted December 7, 2022 * 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. Quote Link to comment
Pat Stanford Posted December 7, 2022 Share Posted December 7, 2022 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 1 Quote Link to comment
belmore Posted December 7, 2022 Author Share Posted December 7, 2022 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! Quote Link to comment
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.