Inventory Spreadsheet

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

What kind of formula were you using? Sounds like you should be using VLOOKUP.

"creese" wrote:

>
> 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
>
>
> --
> creese
> ------------------------------------------------------------------------
> creese's Profile: http://www.excelforum.com/member.php...o&userid=35508
> View this thread: http://www.excelforum.com/showthread...hreadid=552802
>
>

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

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.

"creese" wrote:

>
> 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
>
>
> --
> creese
> ------------------------------------------------------------------------
> creese's Profile: http://www.excelforum.com/member.php...o&userid=35508
> View this thread: http://www.excelforum.com/showthread...hreadid=552802
>
>

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.

You can't use functions if you are going to change the content of cells later. You need some VBA code. Here is a spreadsheet that will do what you want. You will need to change the VBA code to point to your spreadsheet. Not sure if you will know how to do this or not.

http://members.optusnet.com.au/~alli...dateserial.xls

Matt

How do you enable macros? Says the macros in this project are disabled