| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
reference cells on one tab by value of cell on other tab
I have spreadsheet that holds all the information needed. The value in one
column must be one of three words. Manufactured, Purchased, Obsolete. I would like to pull a list in a new tab of all "Manufactured" parts. I do not need the entire row, just the cell values of a few columns. So for example: Master List (sheet 1): clm"A" clm"C" clm"F" Part1 qty manufactured Part3 qty purchased Part4 qty obsolete Part5 qty purchased Part6 qty manufactured If I enter "manufactured" in to A1 on sheet 2, part1 and part6 will fill-in in the rows below. I would also want the qty to fill in those rows as well. Hope this makes sense! Thanks in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
reference cells on one tab by value of cell on other tab
buscher75,
In cell A2, put the value Manufactured In Cell B2, array enter (enter using Ctrl-Shift-Enter) the formula =IF(COUNTIF('Master List'!$F$2:$F$20,$A$2)=ROW()-ROW($B$1),INDEX('Master List'!$A$2:$A$20, LARGE(('Master List'!$F$2:$F$20=$A$2)*(ROW('Master List'!$F$2:$F$20)-1),COUNTIF('Master List'!$F$2:$F$20,$A$2)-ROW()+ROW($B$2))),"") In Cell C2, array enter (enter using Ctrl-Shift-Enter) the formula =IF(COUNTIF('Master List'!$F$2:$F$20,$A$2)=ROW()-ROW($B$1),INDEX('Master List'!$C$2:$C$20, LARGE(('Master List'!$F$2:$F$20=$A$2)*(ROW('Master List'!$F$2:$F$20)-1),COUNTIF('Master List'!$F$2:$F$20,$A$2)-ROW()+ROW($B$2))),"") Change all instances of $20 to $ and a number that is at least as high as the row number of the end of your data set on Master List. Then copy B2:C2 and paste down until you start getting blank values. Then you can change A2 to purchased to get a list of the purchased parts. or obsolete...... HTH, Bernie MS Excel MVP "buscher75" wrote in message ... I have spreadsheet that holds all the information needed. The value in one column must be one of three words. Manufactured, Purchased, Obsolete. I would like to pull a list in a new tab of all "Manufactured" parts. I do not need the entire row, just the cell values of a few columns. So for example: Master List (sheet 1): clm"A" clm"C" clm"F" Part1 qty manufactured Part3 qty purchased Part4 qty obsolete Part5 qty purchased Part6 qty manufactured If I enter "manufactured" in to A1 on sheet 2, part1 and part6 will fill-in in the rows below. I would also want the qty to fill in those rows as well. Hope this makes sense! Thanks in advance. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
reference cells on one tab by value of cell on other tab
I plugged in your formulas and at first glance, they work. However, I
noticed it is pulling in part numbers that are not "manufactured" and after so many lines, the formula produces "#REF" results and eventually blank results. Any thoughts as to what I might have done wrong? I did alter the referenced cell values to reflect the correct cells. Here is a copy of the formula for cell B2. I stop at $F$34 because I am still designing master list. =IF(COUNTIF('Master List'!$F$7:$F$34,$A$2)=ROW()-ROW($B$1),INDEX('Master List'!$A$7:$A$34, LARGE(('Master List'!$F$7:$F$34=$A$2)*(ROW('Master List'!$F$7:$F$34)-1),COUNTIF('Master List'!$F$7:$F$34,$A$2)-ROW()+ROW($B$2))),"") Thank you for helping me. I would NEVER have been able to figure this out on my own. I really appreciate it. "Bernie Deitrick" wrote: buscher75, In cell A2, put the value Manufactured In Cell B2, array enter (enter using Ctrl-Shift-Enter) the formula =IF(COUNTIF('Master List'!$F$2:$F$20,$A$2)=ROW()-ROW($B$1),INDEX('Master List'!$A$2:$A$20, LARGE(('Master List'!$F$2:$F$20=$A$2)*(ROW('Master List'!$F$2:$F$20)-1),COUNTIF('Master List'!$F$2:$F$20,$A$2)-ROW()+ROW($B$2))),"") In Cell C2, array enter (enter using Ctrl-Shift-Enter) the formula =IF(COUNTIF('Master List'!$F$2:$F$20,$A$2)=ROW()-ROW($B$1),INDEX('Master List'!$C$2:$C$20, LARGE(('Master List'!$F$2:$F$20=$A$2)*(ROW('Master List'!$F$2:$F$20)-1),COUNTIF('Master List'!$F$2:$F$20,$A$2)-ROW()+ROW($B$2))),"") Change all instances of $20 to $ and a number that is at least as high as the row number of the end of your data set on Master List. Then copy B2:C2 and paste down until you start getting blank values. Then you can change A2 to purchased to get a list of the purchased parts. or obsolete...... HTH, Bernie MS Excel MVP "buscher75" wrote in message ... I have spreadsheet that holds all the information needed. The value in one column must be one of three words. Manufactured, Purchased, Obsolete. I would like to pull a list in a new tab of all "Manufactured" parts. I do not need the entire row, just the cell values of a few columns. So for example: Master List (sheet 1): clm"A" clm"C" clm"F" Part1 qty manufactured Part3 qty purchased Part4 qty obsolete Part5 qty purchased Part6 qty manufactured If I enter "manufactured" in to A1 on sheet 2, part1 and part6 will fill-in in the rows below. I would also want the qty to fill in those rows as well. Hope this makes sense! Thanks in advance. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
reference cells on one tab by value of cell on other tab
The specific rows where your data resides is important - these were written for data starting in row
2: ROW('Master List'!$F$7:$F$34)-1) needs to be changed to ROW('Master List'!$F$7:$F$34)-6) or ROW('Master List'!$F$7:$F$34)-(ROW('Master List'!$F$7)-1)) HTH, Bernie MS Excel MVP "buscher75" wrote in message ... I plugged in your formulas and at first glance, they work. However, I noticed it is pulling in part numbers that are not "manufactured" and after so many lines, the formula produces "#REF" results and eventually blank results. Any thoughts as to what I might have done wrong? I did alter the referenced cell values to reflect the correct cells. Here is a copy of the formula for cell B2. I stop at $F$34 because I am still designing master list. =IF(COUNTIF('Master List'!$F$7:$F$34,$A$2)=ROW()-ROW($B$1),INDEX('Master List'!$A$7:$A$34, LARGE(('Master List'!$F$7:$F$34=$A$2)*(ROW('Master List'!$F$7:$F$34)-1),COUNTIF('Master List'!$F$7:$F$34,$A$2)-ROW()+ROW($B$2))),"") Thank you for helping me. I would NEVER have been able to figure this out on my own. I really appreciate it. "Bernie Deitrick" wrote: buscher75, In cell A2, put the value Manufactured In Cell B2, array enter (enter using Ctrl-Shift-Enter) the formula =IF(COUNTIF('Master List'!$F$2:$F$20,$A$2)=ROW()-ROW($B$1),INDEX('Master List'!$A$2:$A$20, LARGE(('Master List'!$F$2:$F$20=$A$2)*(ROW('Master List'!$F$2:$F$20)-1),COUNTIF('Master List'!$F$2:$F$20,$A$2)-ROW()+ROW($B$2))),"") In Cell C2, array enter (enter using Ctrl-Shift-Enter) the formula =IF(COUNTIF('Master List'!$F$2:$F$20,$A$2)=ROW()-ROW($B$1),INDEX('Master List'!$C$2:$C$20, LARGE(('Master List'!$F$2:$F$20=$A$2)*(ROW('Master List'!$F$2:$F$20)-1),COUNTIF('Master List'!$F$2:$F$20,$A$2)-ROW()+ROW($B$2))),"") Change all instances of $20 to $ and a number that is at least as high as the row number of the end of your data set on Master List. Then copy B2:C2 and paste down until you start getting blank values. Then you can change A2 to purchased to get a list of the purchased parts. or obsolete...... HTH, Bernie MS Excel MVP "buscher75" wrote in message ... I have spreadsheet that holds all the information needed. The value in one column must be one of three words. Manufactured, Purchased, Obsolete. I would like to pull a list in a new tab of all "Manufactured" parts. I do not need the entire row, just the cell values of a few columns. So for example: Master List (sheet 1): clm"A" clm"C" clm"F" Part1 qty manufactured Part3 qty purchased Part4 qty obsolete Part5 qty purchased Part6 qty manufactured If I enter "manufactured" in to A1 on sheet 2, part1 and part6 will fill-in in the rows below. I would also want the qty to fill in those rows as well. Hope this makes sense! Thanks in advance. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
reference cells on one tab by value of cell on other tab
Here's another way using the same basic technique...
Create these named ranges: InsertNameDefine Name: Part Refers to: ='Master List'!$A$2:$A$6 Name: Qty Refers to: ='Master List'!$C$2:$C$6 Name: Status Refers to: ='Master List'!$F$2:$F$6 On the sheet where you want the results: A1 = manufactured or purchased or obsolete I'd use a drop down list for this. How to setup a data validation drop down list: http://youtube.com/watch?v=t2OsWJijrOM B1 = a formula that returns the count of records =COUNTIF(Status,A1) C1 = Enter this array formula** to extract the part numbers =IF(ROWS(C$1:C1)<=B$1,INDEX(Part,SMALL(IF(Status=A $1,ROW(Part)),ROWS(C$1:C1))-MIN(ROW(Part))+1),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. You need to drag copy down the column to a number of cells that is at least equal to the maximum count for the "status". For example, if obsolete appears the most times in your master list, say 20 times, then you need to copy the formula to at least 20 rows. Assuming that the part numbers are unique... D1 = formula to return the Qty =IF(C1="","",SUMIF(Part,C1,Qty)) Copy down to the same number of rows as you copied the formula in cell C1. -- Biff Microsoft Excel MVP "buscher75" wrote in message ... I have spreadsheet that holds all the information needed. The value in one column must be one of three words. Manufactured, Purchased, Obsolete. I would like to pull a list in a new tab of all "Manufactured" parts. I do not need the entire row, just the cell values of a few columns. So for example: Master List (sheet 1): clm"A" clm"C" clm"F" Part1 qty manufactured Part3 qty purchased Part4 qty obsolete Part5 qty purchased Part6 qty manufactured If I enter "manufactured" in to A1 on sheet 2, part1 and part6 will fill-in in the rows below. I would also want the qty to fill in those rows as well. Hope this makes sense! Thanks in advance. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
reference cells on one tab by value of cell on other tab
Thank you for the help. It works just like I need it to.
"Bernie Deitrick" wrote: The specific rows where your data resides is important - these were written for data starting in row 2: ROW('Master List'!$F$7:$F$34)-1) needs to be changed to ROW('Master List'!$F$7:$F$34)-6) or ROW('Master List'!$F$7:$F$34)-(ROW('Master List'!$F$7)-1)) HTH, Bernie MS Excel MVP "buscher75" wrote in message ... I plugged in your formulas and at first glance, they work. However, I noticed it is pulling in part numbers that are not "manufactured" and after so many lines, the formula produces "#REF" results and eventually blank results. Any thoughts as to what I might have done wrong? I did alter the referenced cell values to reflect the correct cells. Here is a copy of the formula for cell B2. I stop at $F$34 because I am still designing master list. =IF(COUNTIF('Master List'!$F$7:$F$34,$A$2)=ROW()-ROW($B$1),INDEX('Master List'!$A$7:$A$34, LARGE(('Master List'!$F$7:$F$34=$A$2)*(ROW('Master List'!$F$7:$F$34)-1),COUNTIF('Master List'!$F$7:$F$34,$A$2)-ROW()+ROW($B$2))),"") Thank you for helping me. I would NEVER have been able to figure this out on my own. I really appreciate it. "Bernie Deitrick" wrote: buscher75, In cell A2, put the value Manufactured In Cell B2, array enter (enter using Ctrl-Shift-Enter) the formula =IF(COUNTIF('Master List'!$F$2:$F$20,$A$2)=ROW()-ROW($B$1),INDEX('Master List'!$A$2:$A$20, LARGE(('Master List'!$F$2:$F$20=$A$2)*(ROW('Master List'!$F$2:$F$20)-1),COUNTIF('Master List'!$F$2:$F$20,$A$2)-ROW()+ROW($B$2))),"") In Cell C2, array enter (enter using Ctrl-Shift-Enter) the formula =IF(COUNTIF('Master List'!$F$2:$F$20,$A$2)=ROW()-ROW($B$1),INDEX('Master List'!$C$2:$C$20, LARGE(('Master List'!$F$2:$F$20=$A$2)*(ROW('Master List'!$F$2:$F$20)-1),COUNTIF('Master List'!$F$2:$F$20,$A$2)-ROW()+ROW($B$2))),"") Change all instances of $20 to $ and a number that is at least as high as the row number of the end of your data set on Master List. Then copy B2:C2 and paste down until you start getting blank values. Then you can change A2 to purchased to get a list of the purchased parts. or obsolete...... HTH, Bernie MS Excel MVP "buscher75" wrote in message ... I have spreadsheet that holds all the information needed. The value in one column must be one of three words. Manufactured, Purchased, Obsolete. I would like to pull a list in a new tab of all "Manufactured" parts. I do not need the entire row, just the cell values of a few columns. So for example: Master List (sheet 1): clm"A" clm"C" clm"F" Part1 qty manufactured Part3 qty purchased Part4 qty obsolete Part5 qty purchased Part6 qty manufactured If I enter "manufactured" in to A1 on sheet 2, part1 and part6 will fill-in in the rows below. I would also want the qty to fill in those rows as well. Hope this makes sense! Thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to reference last non-blank cell value from a column cells? | Excel Worksheet Functions | |||
| How to use number in some cell as a part of other cells reference? | Excel Worksheet Functions | |||
| Creating a reference from one cell to several other relavent cells | Excel Discussion (Misc queries) | |||
| Formulas that reference cells that reference another cell | Excel Discussion (Misc queries) | |||
| How do I reference the same cell as I move through range of cells. | Excel Worksheet Functions | |||
Linear Mode
