formula needed to bring values to another sheet

[veena]

i have a data list which indicates p.o. numbers in one column and the next to
it is the product and sku in another column etc. i want a formula that will
look for the p.o. number and then bring in the info that is with that p.o.
which is the product sku etc. for example if one p.o. has 5 items then that
p.o. is typed repeated 5 times so i need the formula to bring back those 5
items on a new sheet. hope this makes sense.

 

[Gord Dibben]

Makes sense and you could use a series of VLOOKUP formulas to return the data
based upon PO number chosen.

See Debra Dalgleish's site for more on VLOOKUP and Data
Validation lists for entering the choices.

http://www.contextures.on.ca/xlFunctions02.html

http://www.contextures.on.ca/xlDataVal01.html

Note the section on using DV lists from another worksheet by naming the list.


Gord Dibben MS Excel MVP

 

[Duke Carey]

Assuming your data has column headers - if it doesn't, add them. Let's say
they are PO and SKU.

On the sheet where you want to pull the data, find an empty area and type PO
in one cell. In the cell immediately below that type a PO number.

In the cell immediately above the range where you want the data to show up
type SKU

Now select several cells and use Data->Filter->Advanced filter
You'll get a warning, but just click on OK
In the dialog that comes up
1) click on Copy to a new location
2) clcik in the List range box & then select the data range
3) click in the criteria box and then select the cells where you typed the
'PO' header and a PO #
4) click in the Copy to box and select the cell where you typed SKU
5) click on OK

You can update this easily by changing the PO #, then going through the Data
menu. Better yet, record a macro while going through these steps. Then you
can change thee PO and simply re-run the macro