Space name in door schedule when door has same number as space

[EliM]

Hi All, I'm trying to figure out how to use the GetSpaceNumber and GetSpaceName functions to include a single space name for each door on a door schedule.

 

In an older thread, Pat Stanford explained that when using the GetSpaceName function, you can display the first space the door is attached to and drop the second. And then the stacking order of the space objects can be manipulated so that the right space name shows up first and is kept.

 

I have another idea, but I'm not much good at worksheet formulas...In my office, doors are numbered by the space they are associated with (as I think is a common industry practice). I'm wondering if there's anyway to take advantage of this fact in order to display the right space name without having to closely manage the stacking order of the spaces. Conceptually, the formula would do something like this:

 

1) Compare the two space numbers returned by GetSpaceNumber with the door number returned by IDLabel to return either (true, false) or (false, true)

2) Use the boolean to specify whether to return either the first or second space yielded by the GetSpaceName function

 

If this is easy-peasy for anyone, please post. Otherwise, I'll tinker a little later and post my solution if I can figure it out.

[EliM]

Got it (I take just the first 3 characters of the door id label since we use the fourth character when there are multiple doors into a room)

 

=IF(EXACT(LEFT(('Door'.'IDLabel'), 3), SUBSTRING(GETSPACENUMFOROBJ, ',', 1)), SUBSTRING(GETSPACENAMEFOROBJ, ',', 1), SUBSTRING(GETSPACENAMEFOROBJ, ',', 2))

[EliM]

This is longer but better because it returns an empty cell if the door numbering is off and doesn't match either of the abutting spaces.

 

=IF(EXACT(LEFT(('Door'.'IDLabel'), 3), SUBSTRING(GETSPACENUMFOROBJ, ',', 1)), SUBSTRING(GETSPACENAMEFOROBJ, ',', 1), IF(EXACT(LEFT(('Door'.'IDLabel'), 3), SUBSTRING(GETSPACENUMFOROBJ, ',', 2)), SUBSTRING(GETSPACENAMEFOROBJ, ',', 2), ''))