Easy worksheet question for someone

[Tom W.]

In a spreadsheet cell =OBJECTDATA(PON='Space', 'universal value', '', 'EnergyArea') will return the total combined area of all the Spaces in my file.

 

How do I modify the formula so that it only looks for Spaces on a certain design layer for example?

 

I was thinking something like this would work but it doesn't: 

 

=OBJECTDATA((L='Ground Floor_SPACES') & (PON='Space', 'universal value', '', 'EnergyArea'))

 

Yet the same arrangement has worked in other cases...?

 

I could really do with better understanding how to put these kinds of formulae together as I'm just guessing. I can get the data I need perfectly easily in a database but there are times I need to do it in a spreadsheet instead.

 

Appreciate any guidance thank you.

 

@Pat Stanford, @michaelk, etc

[Pat Stanford]

10 minutes ago, Tom W. said:

=OBJECTDATA((L='Ground Floor_SPACES') & (PON='Space', 'universal value', '', 'EnergyArea'))

Close, but your parentheses are wrong.  I think the below will work but have not tested it.

 

=OBJECTDATA(((L='Ground Floor_SPACES') & (PON='Space')), 'universal value', 'Space', 'EnergyArea')

 

ObjectData requires four "parameters" that have to be independent and separated by commas (semi-colons in languages that use commas as decimal point)

1.  The Criteria. This specifies the objects to be used. You can use the Worksheet Insert menu can click Criteria to open the criteria builder with all the correct parentheses.

2. The ObjectData "Function". In this case it is 'universal value'

3. The Record Name.  If this is left blank and the criteria is for a single object type the object parameter record will be used. In this case since there is a complex criteria (even though it specifies a single object type) I am not certain if it will find the correct record, so I have manually entered the name of the Space object Parameter Record.

4. The Field Name.

 

HTH.

 

[Tom W.]

9 minutes ago, Pat Stanford said:

Close, but your parentheses are wrong.  I think the below will work but have not tested it.

 

=OBJECTDATA(((L='Ground Floor_SPACES') & (PON='Space')), 'universal value', 'Space', 'EnergyArea')

 

ObjectData requires four "parameters" that have to be independent and separated by commas (semi-colons in languages that use commas as decimal point)

1.  The Criteria. This specifies the objects to be used. You can use the Worksheet Insert menu can click Criteria to open the criteria builder with all the correct parentheses.

2. The ObjectData "Function". In this case it is 'universal value'

3. The Record Name.  If this is left blank and the criteria is for a single object type the object parameter record will be used. In this case since there is a complex criteria (even though it specifies a single object type) I am not certain if it will find the correct record, so I have manually entered the name of the Space object Parameter Record.

4. The Field Name.

 

HTH.

 

 

Thanks Pat that helps a lot, and makes complete sense now I look at your example: the criteria need to be grouped together (in parentheses) as they form part of the same parameter in the function.

 

However, the new formula is returning 0 sq m! The same layer in a database is giving me 339.56 sq m... Any ideas why that might be?

 

Or if I posted a file would you take a look...?

 

Be great to understand all this a bit better. I'm using layer as an example but there are other criteria I'd like to use as well if I can understand the principle: 

 

'Space'.'11_NameDisplay'

'Space'.'11_User-Def Info 1'

'Space'.'11_Number'

etc

 

Thanks

 

[Pat Stanford]

Post the file happy to look.

 

If you use the same criteria in a database row, how many objects does it return?  It is likely that there are no objects that satisfy the criteria which is why it is returning zero.

 

For the record.field functions, the criteria would be something like:

 

('Space'.'11_Name'='Room123')

 

All three of the field you named are defined as Text, so you will need the single quotes around the value you are comparing them to.  if you have a Space with the 11_Number of 99 then ('Space'.'11_Name'=99) won't work, but ('Space'.'11_Number'='99') should.

[Tom W.]

Thanks Pat please see VW2024 file attached. You can see in the 'Space Spreadsheet' worksheet several examples of things I'm trying to do which are all returning 0 sq m!

 

Space Test.vwx

 

1 hour ago, Pat Stanford said:

All three of the field you named are defined as Text, so you will need the single quotes around the value you are comparing them to.  if you have a Space with the 11_Number of 99 then ('Space'.'11_Name'=99) won't work, but ('Space'.'11_Number'='99') should.

 

Thank you I am going to make a note about this as I always forget the reason for the quotes!

[Pat Stanford]

I filed a bug last October (VB-199689) on the fact that ObjectData is not accepting compound criteria.  There does not seem to be any progress on the bug since it was filed.

 

So short answer today is you can't do what you are trying to do.  😞

 

[Tom W.]

OK that's a shame! But all those formulas should work (are correctly written) if it weren't for the bug right? And I can add as many criteria as I like in the same manner, separated by 'and' or 'or' + enclosed within parenthesis + they will still work?

 

If that's correct then at least I finally understand how to properly write the formulas despite the fact that at current moment they are not working...!

 

Is it possible to get an update from VW on the bug?

 

Thank you 

[Tom W.]

11 hours ago, Pat Stanford said:

You can use the Worksheet Insert menu can click Criteria to open the criteria builder with all the correct parentheses.

 

Pat I've only just seen/understood this comment. That is a good tip + gets to the root of my original question. I will try + remember to use that in future. I tend to forget those commands are there: I wonder why 'Function...' + 'Criteria...' aren't included in the right-click 'Insert' menu, only in the 'Insert' menu on the top menu bar? Thanks

[Pat Stanford]

18 minutes ago, Tom W. said:

I wonder why 'Function...' + 'Criteria...' aren't included in the right-click 'Insert' menu, only in the 'Insert' menu on the top menu bar?

Because worksheet are older than dirt and there was not right click menu when they were created. 😉