Inventory Spreadsheet

C

creese

I don't know if this is possible or not. I am trying to build a
spreadsheet that has 2 pages. On sheet 1 there is an entry blank for a
serial number (B3), and and entry blank for a receipt # (C3). On Sheet
2 is a listing of serial numbers and blanks for receipt #'s. I have
built a formula that will apply the receipt number to the correct
serial number when entered on sheet 1, but whenever it is changed on
sheet 1 it removes the receipt number on sheet 2. What type of formula
could be used (if there is one) to type in entries on one page and have
them match results and leave the receipt number on the second page?????
Please help:mad: :mad: :mad:
 
C

creese

I was using =IF(C2=Sheet1!B3,Sheet1!C3) which I know is only a true
false type formula, but I am a excel novice. I am not familiar with
VLOOKUP so any help would be greatly appreciated
 
T

tim m

Here is an example of what I did using a VLOOKUP formula.

In Column A of sheet 1 I had a heading of Serial Numbers, in column B of
sheet 1 I had a heading of Recipt numbers, I entered data in the receipt
number column and left the serial number column blank.

In Sheet 2 I did the exact same thing except I entered Serial numbers and
left the reciept numbers blank.

I then put the following formula in the receipt numbers column of sheet 2

=VLOOKUP(A2,Sheet1!$A$2:$B$6,2,FALSE)

When nothing is entered into Serial number column in sheet 1 the results
will be #N/A, however when you enter data into sheet 1 the VLOOKUP function
will look at the serial number (this is the A2 part of the formula), it will
then look at all the information on sheet 1 (in this case I only went from A2
to B6, in your formula it would encompass the whole of the two rows), it then
looks at the 2nd column to the right and sticks this value in the cell.

Give it a test and see if it is what you want.
 
C

creese

Sort of, I have some spreadsheets already made with serial numbers
listed by brand, model, then serial number. Then as we sell them we
type the receipt number beside the serial. So my problem with this
solution is I have no clue what the receipt number will be until I sell
the item, as several locations use our POS system and each entry
generates a new receipt number, for example I may sell something on
receipt # 611987 and the store 20 miles from me may use receipt #
611988, so I don't have a range I can put in. So I was trying to come
up with a spreadsheet where on sheet 1 I could have two boxes, one for
serial number, and one for receipt number. Then have it match the
serials and add the receipt number to that serial number. For me it is
way out of my league, and may not be possible the way I want to do
it.:confused:
 
Top