Index, Indirect, Small, If, Len, Rows... My formula appears fine. 2nd eyes to look at it?

**xlyfe** · 06-13-2016, 06:46 PM

Formula:

Please Login or Register  to view this content.

Can somebody else look at this and tell if they see something that I'm missing? As far as I can tell everything is correct. Even when I enter the formula in, I receive no errors, but I'm still not getting the return values from my other workbook like I should be. What am I missing/doing wrong here?

Thank you for being a 2nd/3rd set of eyes on this for me. Nobody else in my office can decipher this so I'm left to the guru's on this website.

Thanks in advance to somebody who can share any wisdom with me.

**xlyfe** · 06-13-2016, 07:11 PM

I guess I should add that I am modifying this formula off of one that is currently working, but the working one is located within the same workbook. My new formula needs to exist outside of the workbook which holds all of the data.

Formula:

Please Login or Register  to view this content.

That is the currently working "local" formula.

**FlameRetired** · 06-13-2016, 07:14 PM

Are you array entering this formula? If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

If I've missed the mark here please upload a small Excel workbook with the data desensitized and that represents what you are working with. It's always easier to see things in context.

**Tony Valko** · 06-13-2016, 07:15 PM

=IFERROR(INDEX((INDIRECT("'"&$A$7&$D$4&"'!$C9")),SMALL(IF(LEN((INDIRECT("'"&$A$7&$D$4&"'!$C9"))),IF((INDIRECT("'"&$A$7&$D$4&"'!$D9"))>0,ROW(INDIRECT("1:"&ROWS((INDIRECT("'"&$A$7&$D$4&"'!$C9"))))))),ROWS(YY$1:YY1)),0),"")

Not sure what you want that formula to do.

You're using INDEX but you're only referencing a single cell, C9.

**xlyfe** · 06-13-2016, 07:18 PM

Yes. I'm array entering this. I'll see if I can upload an example with data cleansing.

**xlyfe** · 06-13-2016, 07:20 PM

Originally Posted by Tony Valko

Not sure what you want that formula to do.

You're using INDEX but you're only referencing a single cell, C9.

Basically, I was trying to get this formula to scan through a column of data in a different workbook and only return unique values from that column when the column next to it has a number that is greater than zero in it. Possibly I'm going about this in a wrong way? Maybe I should look into a different approach?

C9:C100 holds the data that I need copied. And D9:D100 hold the quantity values. When D9:D100 is greater than zero, I need it to return C columns value.

**Tony Valko** · 06-13-2016, 07:22 PM

If you're referencing another file then that file must be open for the INDIRECT function to work properly.

**xlyfe** · 06-13-2016, 07:24 PM

Originally Posted by Tony Valko

If you're referencing another file then that file must be open for the INDIRECT function to work properly.

The file will always be open when this INDIRECT function is operating. Otherwise I could use my INDIRECT.EXE function when it's closed. But that's not needed in this case.

**Tony Valko** · 06-13-2016, 07:29 PM

Originally Posted by mikerodrigueziii

Basically, I was trying to get this formula to scan through a column of data in a different workbook and only return unique values

Are those numeric values or text values (or maybe both)?

**xlyfe** · 06-13-2016, 07:37 PM

Tony. The values contain both numbers and text. Most of the time within the same cell.

**Tony Valko** · 06-13-2016, 07:43 PM

At this point I think we're going to need to see some sample data along with the result(s) you expect.

**xlyfe** · 06-14-2016, 10:27 AM

Ok. I've attached the two files that I'm currently working with.

The formula in question resides in the 8.5x11 INT workbook. The formula starts in the D8 merged cell. You will have to unmerge it in order for you to Array Enter the formula.

The data that I'm try to get over into that column comes from the "working" workbook in the C column. I only need my list to pull over unique values from that C column which have a QTY of greater than zero in the D column.

Please don't hesitate to ask me for any more information. And thank you all for giving your time and effort to this. Like I said before, this formula was working just fine when both of workbooks were combined into 1 single workbook. I just can't figure out why it's not working when they are separated.

**Tony Valko** · 06-14-2016, 10:58 AM

Sorry, I have download size limits plus I won't download files that contain VBA code.

**xlyfe** · 06-14-2016, 11:04 AM

Ok. No worries. I completely understand. Let me strip the VBA from the files.

**xlyfe** · 06-14-2016, 11:10 AM

Ok. Here are the Macro "free" versions of the files.

**xlyfe** · 06-14-2016, 04:38 PM

Nothing huh?

**Tony Valko** · 06-15-2016, 05:07 AM

OK, I downloaded the files.

Just wondering why you didn't put the 8.5 sheet in the Working file. That would make things a whole lot easier.

In the 8.5 file here's the formula in cell D8:

=IFERROR(INDEX((INDIRECT("'"&$A$7&$D$4&"'!$C9")),SMALL(IF(LEN((INDIRECT("'"&$A$7&$D$4&"'!$C9"))),IF((INDIRECT("'"&$A$7&$D$4&"'!$D9"))>0,ROW(INDIRECT("1:"&ROWS((INDIRECT("'"&$A$7&$D$4&"'!$C9"))))))),ROWS(YY$1:YY1)),0),"")

The INDIRECT refers to A7 but it's an empty cell.

What would cells A7 and D4 contain?

**xlyfe** · 06-15-2016, 11:09 AM

A7 = ActiveWorkbook
D4 = ActiveWorksheet

From the "working" file

Also, the 8.5 sheet was in the main file originally, but it needs to move out now.

**Tony Valko** · 06-15-2016, 01:31 PM

ActiveWorkbook and ActiveWorksheet are VBA expressions.

Are you using VBA to create these formulas?

If so, I'm not much of a programmer and I probably can't help you with this.

**xlyfe** · 06-15-2016, 02:43 PM

Originally Posted by Tony Valko

ActiveWorkbook and ActiveWorksheet are VBA expressions.

Are you using VBA to create these formulas?

If so, I'm not much of a programmer and I probably can't help you with this.

Sorry, I meant to say...

A7 = Hardware Job Cost - Working.xlsx
D4 = Job Name

And thank you again again for helping take a look at this Tony. It is greatly, greatly appreciated.

Index, Indirect, Small, If, Len, Rows... My formula appears fine. 2nd eyes to look at it?

LinkBack

Thread Tools

Rate This Thread

Display

Index, Indirect, Small, If, Len, Rows... My formula appears fine. 2nd eyes to look at it?

Re: Index, Indirect, Small, If, Len, Rows... My formula appears fine. 2nd eyes to look at

Re: Index, Indirect, Small, If, Len, Rows... My formula appears fine. 2nd eyes to look at

Re: Index, Indirect, Small, If, Len, Rows... My formula appears fine. 2nd eyes to look at

Re: Index, Indirect, Small, If, Len, Rows... My formula appears fine. 2nd eyes to look at

Re: Index, Indirect, Small, If, Len, Rows... My formula appears fine. 2nd eyes to look at

Re: Index, Indirect, Small, If, Len, Rows... My formula appears fine. 2nd eyes to look at

Re: Index, Indirect, Small, If, Len, Rows... My formula appears fine. 2nd eyes to look at

Re: Index, Indirect, Small, If, Len, Rows... My formula appears fine. 2nd eyes to look at

Re: Index, Indirect, Small, If, Len, Rows... My formula appears fine. 2nd eyes to look at

Re: Index, Indirect, Small, If, Len, Rows... My formula appears fine. 2nd eyes to look at

Re: Index, Indirect, Small, If, Len, Rows... My formula appears fine. 2nd eyes to look at

Re: Index, Indirect, Small, If, Len, Rows... My formula appears fine. 2nd eyes to look at

Re: Index, Indirect, Small, If, Len, Rows... My formula appears fine. 2nd eyes to look at

Re: Index, Indirect, Small, If, Len, Rows... My formula appears fine. 2nd eyes to look at

Re: Index, Indirect, Small, If, Len, Rows... My formula appears fine. 2nd eyes to look at

Re: Index, Indirect, Small, If, Len, Rows... My formula appears fine. 2nd eyes to look at

Re: Index, Indirect, Small, If, Len, Rows... My formula appears fine. 2nd eyes to look at

Re: Index, Indirect, Small, If, Len, Rows... My formula appears fine. 2nd eyes to look at

Re: Index, Indirect, Small, If, Len, Rows... My formula appears fine. 2nd eyes to look at

Thread Information

Users Browsing this Thread

Similar Threads

Index Small Row Formula

Index, match and small (look up for differents rows based on one criteria)

[SOLVED] Formula using ROWS, COUNTIF, INDEX, and SMALL

[SOLVED] SumIfS works fine for 2 conditions, but when I try to add a third, #value appears ??

My Excel Sheet that worked fine for months opens small without controls to enlarge

[SOLVED] Fonts Too Small or Eyes Too Big?

Fonts Too Small or Eyes Too Big?

Bookmarks

Bookmarks

Posting Permissions