Can you move the row information by just using one numbered cell?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What I was wondering is if it was possible to tranfer all the row data on to
a printable sheet with boxes using just the I.D. number from the orignal
database. ie. taking the information from the database below and by typing
"213" (the ID number) into a new printable sheet (basic example below) it
will take all the row information with it, so that i can take any shipment
number type it in and print off a delivery note, but only have one
interchangable printable sheet?

DATABASE

A B C D
E
1 213 23 24.02.05 DAVE 13.50
2 214 15 16.04.07 BILL
12.99

PRINTABLE SHEET

Shipment No. 213
Number of boxes 23
Date of order 24.02.05
Customer DAVE
Price 13.50


Thanks.
 
Yes, you can use VLOOKUP to achieve this. Assume on your printable
sheet you want the values to go into column B, and that you enter your
Shipment Number into B2, and assume you have your main data in a sheet
called database covering A1:E100. Put these formulae in the cells
stated on your Print sheet:

B3: =IF(B$2="","",VLOOKUP(B$2,database!A$1:E$100,2,0))
B4: =IF(B$2="","",VLOOKUP(B$2,database!A$1:E$100,3,0))
B5: =IF(B$2="","",VLOOKUP(B$2,database!A$1:E$100,4,0))
B6: =IF(B$2="","",VLOOKUP(B$2,database!A$1:E$100,5,0))

Notice that the only thing which differs is the third parameter of the
VLOOKUP formula - this controls which column of the main data to get
the corresponding data from. Consequently, you can copy the formula
from B3 into B4:B6, then just edit it to change this value.

Hope this helps.

Pete
 
Look at the VLOOKUP function, using the ID number as your lookup parameter

If A5 contains the Shipment Number on your printable sheet

=VLOOKUP(A5,Sheet1!A:E,2,0) will return value in Column B fRom Sheet1 (# of
boxes)

=VLOOKUP(A5,Sheet1!A:E,3,0) will return value in Column C from Sheet1 (date
of order)

HTH
 
Back
Top