Working but not Working Function

[JayReina]

I am attaching a File and is working with the Dummy Data but not with Actual Data

So what you're going to see is that you can select a product, the product number will change accordingly and you will get a list of "Storage Groups"

So it works...until I add the Actual data to the "Storage Group List" The only difference is that you will get a Storage Group name that it could contain 25 characters, or it may contain dashes or Underscores.

Example of a storage Group name: AFgtrs_23tf-54 01-or53

So I am giving up on this function. I am hoping to get this done thru VBA.... Can anyone take a quick peak?

Thx... J

DevFile.xlsx

[Ron Coderre]

I *think* the issue is that you are not compensating for your referenced range beginning in Row_2
Try this ARRAY FORMULA (completed with CTRL+SHIFT+ENTER)

Please Login or Register  to view this content.

Copy E9 and paste into E10:E506

Does that help?

[XOR LX]

Hi,

Don't give up yet!

Question: where am I to add this example of a real Storage Group name: (AFgtrs_23tf-54 01-or53) in order to reproduce the error you are experiencing?

I replaced the entry in cell K2 of the StorageGroups tab (i.e. the first in the Data Validation list) and no errors were produced - can you clarify?

Regards

[JayReina]

Nope

When I Copy and Paste, I get one initial value but the moment I convert it to an Array it goes back to "No Value"

It is the weirdest think I ever seen in excel. Just an FYI I even created a new sheet from scratch to make sure that i had a clean slate.

[Ron Coderre]

You'll need to post a sample workbook that exhibits the problem.

[JayReina]

XR LX

AFgtrs_23tf-54 01-or53 will go under the Storage Group Tab under SG ... the data usually contains about 15 to 30 characters

in my data list I have like 2000 of them so from C:2 to C:2002

[JayReina]

Sence i can share the Data ... My Scinceir apologies ....is there a way to do this in VBA?

[ricardotablas]

Hi
I agree with Ron that reference has been slipped, but I differ for the position of the -1

Please Login or Register  to view this content.

I corrected it and if you use Adobe as a product you'll see that the first product is not cable is home.

Remember to Ctrl+Shift+Enter

[JayReina]

This

=IFERROR(INDEX(StorageGroups!$C$2:$C$3000,SMALL(IF(StorageGroups!$E$2:$E$3000=$C$6,ROW(StorageGroups!$E$2:$E$3000)),ROWS($E$9:$E9))-1),"No Value")

It didn't work ...

[Ron Coderre]

Comments removed
...it appears that both our formulas will do the same thing...return the matched row number minus 1

[Ron Coderre]

We can't offer a confident solution if we don't really know what's happening. Your formula doesn't seem to have a text-length limit and "Didn't work" is a bit vague.
Can you post your sample workbook again...but with changes that demonstrate the problem?

[ricardotablas]

Hi

I'm supposing it didn't work for the actual data but it does has corrected one mistake, am I right?.

As you can't share the data then I would answer to your question: yes that it can be done with VBA, but I suspect maybe you're having troubles with whitespaces or trash characters. Before creating a VBA procedure I would suggest that you make a little bit of cleaning your databse with functions

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

.

Plese tell me if this helps, if not give a little bit of time and I'll create a simple procedure template.