Reference via SELECT, ignoring blank cells

[Marvin-JR]

Hi,

I have tried many ways of accomplishing a reference from one sheet to another and the only only one I've found to work is using a virtual column like this:

SELECT(SLT MASTER[Status], ([SKU] = [_THISROW].[SKU]))

There are two issues with this method that I've come across:

1. It does slow down the app. Both of my sheets are quite large. One is 25,000 rows and the other is 17,000.
2. If there are any blank cells in the source "SKU" column, I get a result. I'd like no result in those cases.

Is there a better way to do this? I've looked and looked for help but all of my efforts have failed except this one.

Thank you.

[Joseph_Seddik]

Welcome to the community!

1. When you use a virtual column, your expression will be recalculated with every sync for all rows in the table, even if you need it to calculate only once. So if you have 25k rows it will calculate 25k times. Sure there are better ways, if you can share your tables and what are you trying to achieve we'll be better able to help. It might that you won't need to use virtual columns or you don't even need this expression from the first place. 
   
   
2. To exclude blank values you'll have to specify that to the expression, using AND(), inside the SELECT statement , like this:
   
   SELECT(SLT MASTER[Status],
     AND(
       ISNOTBLANK([SKU]),
       ISNOTBLANK([another column you won't include if empty]),
       [SKU] = [_THISROW].[SKU]
     )
   )
   

[Marvin-JR]

Hi,

Thanks for the quick reply. First, here are links to some test docs that I set up for this purpose. I cannot share the real docs, unfortunately.

https://docs.google.com/spreadsheets/d/19tUhf8iWmiJvSSpcY-BKpkJMCrTASfzpLrFzJRF6V5s/edit?usp=sharing

https://docs.google.com/spreadsheets/d/1DQJNEmhfJzRHCAsPof17oOefAiQhi_mUyPsd7gIHyDw/edit?usp=sharing

In my data, SKU is king. Everything hinges on SKU. In my 25,000 row table (Reference test - ATW Month), I have need of data from the 17,000 row table (SLTMSR - MSR). I simply have not been successful at showing the data there. I also need to be able to apply formatting rules.

(As a bonus, I'm also going to be doing this same task with data from SmartSheet.)

Please let me know what additional info you need.

Thank you!