Search a specific row on a 2nd tab?

[jamb]

I have an excel sheet with 2 tabs. On the "1st sheet tab" I can choose from a pull down menu an item. In the "A" column a # will appear. This number fills in by and is unique to the "item" chosen from the pull down menu. The pull down menu items and the # returned comes from (referenced from) the "2nd tab" of the work sheet. So each number that appears in Column "A", of the "1st sheet tab", is unique to the selected item. Can I search the row on the "2nd tab" based on the # returned in column "a" on the 1st tab. That is how can I tell excel which row I want it to search through on the 2nd tab, based on the value in the "cell" on "1st sheet tab" (kind of a reverse Row() function). Please see the attached file.

Thanks. View attachment Cut List.xls

 

[HardlyWorkin]

I believe INDEX() is what you want, I see where you have used it from H9:H39 but you are only returning the value that has already been selected in the dropdown. What do you mean by "search the row" what criteria would the search be based off of?

 

[Zack Barresse]

Why are you using the forms controls for drop downs? You can use data validation with a list if you'd like, have it right in the cell.

To answer your question more directly, you are already doing what you're asking. For example the formula you have in cell H9...

=IF(E9=0," ",IF(A9=0," ",INDEX(Material!$B$7:$B$170,A9)))

You're using the INDEX() function for the right range (it's important you're starting at row 7, as that will be row 1 returned in the function array), and going to the row of the number specified by what is returned in A9, the index number of your list. The only thing you would need to do is expand your index array and specify the column number. For example, if you wanted to return column C (utilizing the above formula) instead of the value from column B, you would simply change the formula to ...

=IF(E9=0," ",IF(A9=0," ",INDEX(Material!$B$7:$C$170,A9,2)))

In this case the array looked at is B7:C170 on sheet 2, the row (of that array) returned in the row of the value found in A9 (your drop down index) and column 2 (which is column C, the second column in your indexed array). Not sure if that's what you're looking for or not.

Also, your formula in column L, couldn't that be simplified to...

'FROM:
=IF(A9=0," ",B9&"' - "&IF(D9=0,C9&"''",C9&" "&IF(OR(D9=4,D9=12),D9/4,IF(OR(D9=2,D9=6,D9=10,D9=14),D9/2,IF(D9=8,D9/8,D9)))&"/"&IF(OR(D9=4,D9=12),4,IF(OR(D9=2,D9=6,D9=10,D9=14),8,IF(D9=8,2,16)))&"''"))

'TO:
=IF(A9=0,"",B9+(C9/12)+(D9/(16*12)))

(I left the "16*12" in the formula to show you how I came up with that, not sure if that's right or not.)

You would need a format of "# ??/16" (without the quotes of course), or maybe even "#' #0/16''" (just inside the last quotes are double apostrophe's, as a quote mark in formatting means literal text), or select Fractions and choose Sixteenths. Unless I've misinterpreted your data scheme (entirely possible).

 

[jamb]

I know I am not responding to both reply's adequately because of time constraints, but the intention is to search "column" A on the 2nd tab (sheet) to match the number that appears in the cell on the 1st tab (ie. if the number 4, which is associated with the item on the pull down menu on the 1st tab, it would find the row in which the #4 appears on the 2nd tab). Since this value in the 1st tab "cell" can change from a 1 to up to a multiple digit number, we need it to find the row in corresponds to on the 2nd tab. Then we want excell to then search that row, on the 2nd tab, for a range of Column D to Column F. Ultimately, we want it to search for "20", "24", or "40" within the respective columns in that range. I know how to do the last part, but I am not sure if our formula is telling excel which row it should search on the 2nd tab is correct, since not all "20", "24", "40" values apply to each item (see previous attached excel sheet). Thanks for any updates.

 

[Zack Barresse]

Sorry I'm seeing this late. To 'search' for a certain row determined by data in a column, so long as the column contains unique values, you can use the MATCH() function. Assuming you did away with the controls combo boxes and went to data validation, you could use something like this...

'Stock Length 1
=IFERROR(INDEX(Material!$B:$F,MATCH('Shop Copy'!$H$9,Material!$B:$B,0),3),"")

'Stock Length 2
=IFERROR(INDEX(Material!$B:$F,MATCH('Shop Copy'!$H$9,Material!$B:$B,0),4),"")

'Stock Length 3
=IFERROR(INDEX(Material!$B:$F,MATCH('Shop Copy'!$H$9,Material!$B:$B,0),5),"")

If you don't have version 2007 or later, the IFERROR() will not work for you and you'll need to utilize an IF() function instead...

'Stock Length 1
=IF(ISNA(MATCH('Shop Copy'!$H$9,Material!$B:$B,0)),"",(INDEX(Material!$B:$F,MATCH('Shop Copy'!$H$9,Material!$B:$B,0),3)))

'Stock Length 2
=IF(ISNA(MATCH('Shop Copy'!$H$9,Material!$B:$B,0)),"",(INDEX(Material!$B:$F,MATCH('Shop Copy'!$H$9,Material!$B:$B,0),4)))

'Stock Length 3
=IF(ISNA(MATCH('Shop Copy'!$H$9,Material!$B:$B,0)),"",(INDEX(Material!$B:$F,MATCH('Shop Copy'!$H$9,Material!$B:$B,0),5)))

HTH