Part of the array argument to the index function needs to come from a drop down list

[tonybeo]

I have a worksheet in which I have a drop down list where the user makes a selection. This is in cell C6. In another cell, I am entering a formula which contains an index function. The array for the index function is in another workbook. I have entered the name of the other workbook and sheet but I need for the range part of the argument to come from the selection that the user makes in the drop down box. I tried to add the cell reference to the end of the workbook/sheet name with the "&" sign to concatenate the text into one string that is needed to make the formula work but it does not work. When I manually type in the range into the formula, it works.

The formula I have is as follows:

=INDEX('S:\CRT-General Equipment Forecast Lists\2014 Revised Main Folder\TRS\2014 TRS Master Tracking Sheet.xlsx'!&C6,MATCH(C10,'S:\CRT-General Equipment Forecast Lists\2014 Revised Main Folder\TRS\[2014 TRS Master Tracking Sheet.xlsx]2014'!$C$3:$C$967,0)).

This formula gives a #REF! result.

Thanks in advance for any help with this problem.

[daffodil11]

You will need to use the INDIRECT function to have Excel evaluate the string as a reference.

INDEX(INDIRECT("'S:\CRT-General Equipment Forecast Lists\2014 Revised Main Folder\TRS\2014 TRS Master Tracking Sheet.xlsx'!"&C6),MATCH...

[tonybeo]

Thanks daffodil11

I entered the formula exactly as you have written it and I still get the #REF! result. Does the C6 need to have double quotes around it as well?

I was also wondering why you had removed the brackets from the beg. and end of the workbook name and why you had removed the worksheet name "2014"?

Thanks.

[daffodil11]

I was just showing the only relevant parts of your formula that needed update. You're concatenating C6 into the first part of the equation, not the second.

INDIRECT() works by evaluating whatever is in the parentheses as a reference, meaning INDIRECT("A"&1+2+3) really targets A6. The & just separates strings from numbers. The filepath will be the stringy portion of your formula, and you will concatenate the C6.

You posted this, which does not contain brackets in the indexed portion of the filepath:

=INDEX('S:\CRT-General Equipment Forecast Lists\2014 Revised Main Folder\TRS\2014 TRS Master Tracking Sheet.xlsx'!&C6,MATCH(C10,'S:\CRT-General Equipment Forecast Lists\2014 Revised Main Folder\TRS\[2014 TRS Master Tracking Sheet.xlsx]2014'!$C$3:$C$967,0))



This is the alteration am suggesting, I didn't mean to suggest you can delete the entire second half of your formula.

=INDEX(INDIRECT("'S:\CRT-General Equipment Forecast Lists\2014 Revised Main Folder\TRS\2014 TRS Master Tracking Sheet.xlsx'!"&C6),MATCH(C10,'S:\CRT-General Equipment Forecast Lists\2014 Revised Main Folder\TRS\[2014 TRS Master Tracking Sheet.xlsx]2014'!$C$3:$C$967,0))

[tonybeo]

Thanks. I understood that you had only entered the first part of the formula. When I entered your changes, I entered them along with the second part as you have listed above with the exception that the first part of my formula in my original post did not have the brackets and sheet name as it should have. Are those brackets and sheet name needed if the other workbook only has one sheet (2014) in it?

The formula I have now entered is as follows but I still get the #REF! result? Note that the text in cell C6 is Far_East and the cell is formatted as text.

=INDEX(INDIRECT("'S:\CRT-General Equipment Forecast Lists\2014 Revised Main Folder\TRS\[2014 TRS Master Tracking Sheet.xlsx]2014'!"&C6),MATCH(C10,'S:\CRT-General Equipment Forecast Lists\2014 Revised Main Folder\TRS\[2014 TRS Master Tracking Sheet.xlsx]2014'!$C$3:$C$967,0))

Thanks.

[daffodil11]

Is Far_East a Named Range?


You may want to try it with the brackets in place as in the 2nd half.

Better yet, for best results I'd throw together a mockup of what you're trying to accomplish and attach the file.
I can only guess as to what you're looking at.

It looks like you're searching a named range in workbook2 which is determined by the Data Validation.

[tonybeo]

Yes Far East is a named range. Please see the two workbooks attached. The workbook named "Automated Forecast Analysis Template" asks the user to select a region in cell E6. In cell G6, I need the result to be based on a search of the range (that the user selected) in the other workbook "2014 TRS Master Tracking Sheet".

Note that the formula in cell G10 works. In that formula, I manually entered the range. The formula in cell G9 is the one we have been discussing.

When a quantity is entered in the quantity column (column B) and a part number or asset code is entered into column C, columns G through H should be auto populated as is shown in row 10.

I have attached the 2 files.

Thanks again for the help.

[daffodil11]

It looks like your drop down is in E6.


=INDEX(INDIRECT("'S:\CRT-General Equipment Forecast Lists\2014 Revised Main Folder\TRS\2014 TRS Master Tracking Sheet.xlsx'!"&E6),MATCH(C10,'S:\CRT-General Equipment Forecast Lists\2014 Revised Main Folder\TRS\[2014 TRS Master Tracking Sheet.xlsx]2014'!$C$3:$C$967,0))


Worked like a charm for me.

[tonybeo]

Thank you soooo much . It works great.

[tonybeo]

Regarding the above, if the sheet "2014 TRS Master Tracking Sheet" has changed such that the data for each country/region is now in separate worksheets in this same workbook, how would that change the formula? Note that the name of each of these new worksheets is the same as the named range that is in each of these new worksheets.

Thanks for any help with this.

[tonybeo]

Further to the above..when a named range is called from an index function argument, does it search the whole workbook for that range, or does the argument to the index function need to include both the workbook name and the sheet name?

Thanks.

[daffodil11]

The index would still only search the cells listed in the Named Range that you define, not the whole sheet even if they have the same name.

So essentially, nothing would change. =index("bob",2,3) would return the value of the 2nd row, 3rd column no matter where I originally set "bob" to. It's all defined in the Name Manager on the data tab. It knows what sheet and reference automatically.

[tonybeo]

Thanks daffodil11,

I got this to work. I had the named range refer to a column of cells from rows 4 to 1008. Later I thought it would be better to have the named range refer to the entire column, in case new rows of data needed to be added later to the source workbook. When I changed all the formulas so that the look up array argument in the match function (in the destination workbook) was an entire column (not just cells in rows 4 to 1008) and I changed the named ranges in the source workbook in the same manner, the destination workbook is now very slow to do anything in it. Could this be due to changing to entire rows in the match argument and in the named ranges on the source?

Also, the INDIRECT function only returns the result when the source workbook is open. The source workbook is stored on the server and I do not want it to have to be open for the function to work. Is there a way to do this with a different function so that the source workbook does not have to be open?

Thanks,

[daffodil11]

As far as I know, it's a limitation of INDIRECT function that requires the source workbook to be open to update.

There are functions such as SUMPRODUCT that can reach through closed workbooks to update, but you would need to spell out the link instead of using INDIRECT.

As far as performance goes, referencing an entire column will most definitely create sluggish performance. Even if you reduced the reference to A1:A50000, that's still only processing 4.7% of A:A. A:A is every row possible, 1.048 MILLION of them. A couple of formulas that reference entire columns with undeclared rows is fine, but more than a dozen of those formulas and you'll begin to see performance just drop like a rock.

[tonybeo]

Thanks so much. So in my formula in the first post of this thread, how would I change this to use the sumproduct function that would give the same results?

thanks.

[daffodil11]

I apologize, it has been quite some time since I reviewed your data. Are we returning strings or values?

Assuming you're returning unique data points where there will only be one number matched, you can always use SUMPRODUCT.

SUMPRODUCT works by comparing arrays of data with criteria to evaluate them as True or False, then multiplying those T and F against your values and adding them together. When True and False are exposed to operators (+, -, *, ÷) they coerce to 1 and 0 respectively.

For example, imagine A1:A3 = tom, frank, dave, and B1:B3 = 1, 2, 3.

=SUMPRODUCT((A1:A3="frank")*(B1:B3))
=SUMPRODUCT((FALSE,TRUE,FALSE)*(1,2,3))
=SUMPRODUCT((0,1,0)*(1,2,3))
=SUM(0,2,0)
=2

=SUMPRODUCT((range1=criteria1)*(range2=criteria2)*(range3))

In some calculations I use 9-10 criteria statements to whittle down to the meat of what I need.
Feel free to hit Alt+Enter at key points of a long formula to insert line breaks so that you can keep things organized.



Maybe something like:

=SUMPROCT(('S:\CRT-General Equipment Forecast Lists\2014 Revised Main Folder\TRS\[2014 TRS Master Tracking Sheet.xlsx]2014'!$C$3:$C$967=C1)*
('S:\CRT-General Equipment Forecast Lists\2014 Revised Main Folder\TRS\2014 TRS Master Tracking Sheet.xlsx'!Far_East))

[tonybeo]

Thanks dafodol11,

The formula that I am currently using is the one you had provided in the thread you posted above on 1/9/14 @ 3:17 PM:

=INDEX(INDIRECT("'S:\CRT-General Equipment Forecast Lists\2014 Revised Main Folder\TRS\2014 TRS Master Tracking Sheet.xlsx'!"&E6),MATCH(C10,'S:\CRT-General Equipment Forecast Lists\2014 Revised Main Folder\TRS\[2014 TRS Master Tracking Sheet.xlsx]2014'!$C$3:$C$967,0))

Before I jump into the very much appreciated post that you just sent, I wanted to clarify that what you are describing will, in fact, be a replacement to this formula that will allow me not to have to open up the source file.

Thanks.

[tonybeo]

Automated Equip Forecast Analysis template rev3.xlsxDafodil11,

I am attaching the destination workbook for reference. Note that the formulas in columns G, H & I work when the source workbook "2014 TRS Tracking Sheet" is open but when it is closed, those formulas return #REF!

Thanks.

[tonybeo]

Dafodil11,

Can you please tell me if I provided a response that made sense to you? I think that your above post gave an alternative to using index/match but my problem was not with the index/match function, it was with the indirect function. That function requires the source spreadsheet to be open.

Thanks again