Recurring Items in Spreadsheets

[Scaper]

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 m
clients. There are certain items that I use in my proposals over an
over and over again, almost every day. But every day I have to typ
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 o
normal items that I use in my worksheets regularly so that when I star
to type them, it asks me to auto-populate that cell with a memorize
item. Does that make sense?

For instance, one of the items I am specifying for jobs regularly i
Anchor Diamond SRW Blocks. And these blocks are $6.50 a piece. And ye
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
could just create a database of these 100 items so that whenever I star
typing them, they'll automatically come up or ask me if I want them t
come up.

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

Thanks in advance

 

[GS]

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

 

[Spencer101]

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 m
clients. There are certain items that I use in my proposals over an
over and over again, almost every day. But every day I have to typ
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 o
normal items that I use in my worksheets regularly so that when I star
to type them, it asks me to auto-populate that cell with a memorize
item. Does that make sense?

For instance, one of the items I am specifying for jobs regularly i
Anchor Diamond SRW Blocks. And these blocks are $6.50 a piece. And ye
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
could just create a database of these 100 items so that whenever I star
typing them, they'll automatically come up or ask me if I want them t
come up.

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

Thanks in advance.

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

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

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

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

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