auto entry of data on keystrokes

[gatz]

If we set up 1 sheet of sorted data within a "book", then go to another
sheet 2 to have some (or many) of these data copied to a column in sheet 2
that is then used for a customer quote sheet, with perhaps subtotals and
grand totals

For instance, say we have 150 products which we can sort by name/size/price
that we've entered in sheet 1.

Then, on the 2nd sheet, after placing the cursor on a particular column/row
we'd like to use a simple numeric keyboard (AltXXX, maybe?) entry that
corresponds to what's on sheet 1.
We'd print out sheet 1 for quick reference.

Or, has anyone got a better idea/plan ?
Thanks

 

[Shane Devenshire]

Hi,

There is no built in key to do this. You could write a macro and assign a
shortcut key.

Cheers,
Shane Devenshire

 

[Frank]

I think what you need is VLOOKUP. Say on Sheet 1 you have your complete list
of Part #s in column A, Descriptions in column B and Prices in column C. In
Sheet 2 you have a Customer Quote Form where you type part numbers in the
frst column (e.g. B5:B20) and the corresponding Descriptions and Prices
automatically appear in the 2nd (e.g. C5:C20) and 3rd (e.g. D520) columns.
To get this to work, in Sheet 2 in cell C5 you would use a formula such as
=VLOOKUP($B5,Sheet1!$A$1:$C$200,2,0) and in D5 you would use
=VLOOKUP($B5,Sheet1!$A$1:$C$200,3,0). When you type a Part # in B5, the first
VLOOKUP up formula in C5 looks in the first column of the table in Sheet 1
for a match for the value in B5 on Sheet 2. If it finds it, it returns the
corresponding value from column 2 (the column # is the 3rd argument of the
VLOOKUP function) of that table. The VLOOKUP formula in the Price column on
Sheet 2 does the same lookup but returns the corresponding value from the 3rd
column of the table.

To prevent #N/A errors in your VLOOKUP formulas, modify the formula with...
=IF(COUNTIF(Sheet1!$A$1:$C$200,$B5),VLOOKUP($B5,Sheet1!$A$1:$C$200,2,0),"")

Hope this is what you need.

Francis J Hayes (The Excel Addict)

P.S. I publish a weekly newsletter called 'Spreadsheet Tips From An Excel
Addict' at http://www.TheExcelAddict.com