creating a small list of needed data from a larger worksheet

[MontyUSMC]

i have a list of publications that i have to inventory and order. as there are a few hundred doing this manually would be very time consuming. i need a way to copy certain rows of data from the main list in one worksheet to another. the way my worksheet is set up is column a is the title of the book, column b is the control number to that specific book, column c is the long title of the book, column d is the gear the book has to do with (this i hyperlinked to the folders for each pdf file), column e is the location of the hard copies by binder number (1,2,3...) column f is the inventory for my electronic copies, column g&h are my hard copy libraries. withing the last three columns, i have entered a "1" if the publication is up to date and. an "M" if the pub is missing, a "U" if the pub needs updating and an "O" if the pub is on order. i am trying to create 3 other worksheets to give me lists for current pubs, missing, and on order.

i have tried researching formulas but i think i need a macro. i have no idea what this is or how to use it. could someone point me in the right direction?

[Azam Ali]

see the attatch file in which list is generated though column H

it may help

list on the basis of three columns is much complicated

[MontyUSMC]

see the attatch file in which list is generated though column H

it may help

list on the basis of three columns is much complicated

ok how did you do this? i see the formula but i am not understanding how it all works. i understand why the merged cells don't work out too. But do i need to enter this formula in each cell that i want a result in? also i want my current pubs on one worksheet, my missing on another and on order for another. i am also wondering if there is a way to add up the inventories so that i know how much of each i need to order. am i asking too much of this? i really don't know a whole lot.

[Azam Ali]

You need to understand three formulas in the attatched sheet i.e. formula in Cell # A3, A4 & B3
The formula in A4 is copied down in column A and formula in B3 is copied on all range of B140:I140

It is recommended to always use evaluate formula option in the formula tab in order to understand the formula

in order to get seperate worksheet for on order, just make a copy of current sheet and replace M in cell B1 with O

The addition of inventory may also be possible if you explain any logic of it.

[MontyUSMC]

so i read up on the formulas you used and i am still confused as to how they all work together. but i did see how to alter it the way i want to set it up. but you said that doing this kind of formula using 3 columns as referances is more complicated. is it posible?

[jaydeep]

hii i have a problem please some body help me out:

I have datas in 2 sheets:

sheet 1 sheet 2

Name number Name number

a 1235 a 1235
b 5689 b 5689
c 9876 c 9876
d 9876 d 9879
e 8796 e 8796

the names and numbers are same in both the sheets except name d is entered wrongly in sheet 2(9879).my question is how can i use v lookup to determine that the name d in sheet 2 has been entered wrongly, suppose its a huge sheet with many names and numbers so its impossible to track with ***** eye.please somebody help me out

[Azam Ali]

Hi jaydeep
yes it is possible however the formula will be long

further, you must start a new thread for you second problem of vlookup formula. in my opinion it is much easier.

[Cascus]

Here is another variation using Pivot Tables, I find it less confusing at times than a ton of formulas. You can copy the PIVOT tab to make separate sheets then change the filters at the top left to pull out what you need. Let me know if this works for ya.