Recurring Items in Spreadsheets
 

I'm not sure if this is a Worksheet Function or not. If not, I'm sorry. Not familiar with all the correct terminology in Excel.

I am a contractor I use Excel daily to prepare bid proposals for my clients. There are certain items that I use in my proposals over and over and over again, almost every day. But every day I have to type these items in manually, type in their corresponding price, etc.

I'm thinking there's gotta be a way for me to create a database of normal items that I use in my worksheets regularly so that when I start to type them, it asks me to auto-populate that cell with a memorized item. Does that make sense?

For instance, one of the items I am specifying for jobs regularly is Anchor Diamond SRW Blocks. And these blocks are $6.50 a piece. And yet every time I write a bid I have to write in "Anchor Diamond SRW Block" in one cell, and then tab over to another cell to put in the each price. I do that for a hundred or so different items each day. I'm thinking I could just create a database of these 100 items so that whenever I start typing them, they'll automatically come up or ask me if I want them to come up.

I can figure it out myself, if someone just points me in the right direction. I don't even know what you would call this. Once I know what it's called I can probably look up tutorials online. Just looking for someone to point me in the right direction.

Thanks in advance.

Recurring Items in Spreadsheets
 

One way is to make a price list on a separate sheet. Give this list
(items only) a dynamic defined name of global scope so it auto-adjusts
when you add/remove items. Now you can add Data Validation dropdowns in
the item column. In the price column you can use a formula to lookup
the price of the selected item.

Another way is to use a combobox control on the worksheet. There are
examples of how to do this out there, but I haven't seen anything
reliable and without issues. A better approach is to use the
BeforeRightClick event to open a dialog with a combobox so you can
cycle through items by the 1st character. This means if there's 5 items
that begin with "A" then press the "a" key continuously until the item
you want appears, click "Insert" and it fills in the cells with item
and price. (IOW, no need for formulas) The dialog can be programmed to
advance to the next row if desired, so it remains open until you close
it but still allows you to edit the worksheet while the dialog hovers.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

How about "data validation" drop down lists for the items and then VLOOKUPS to bring in the costs etc.?

Although if you have hundreds of items that could be a little clunky with all the scrolling up and down the lists.

Maybe give each item a code and just type that in and have VLOOKUPS bring back the full item name, costs etc.?

The second approach would involve learning a list of codes, but I wouldn't of thought that would be too difficult for the oft used items and a cheat sheet for the rest wouldn't be drastically difficult to use.

Just a few ideas for you there..
Happy to help out with it if you need.