In this case I think you would be better to use a lookup table. Define
a table somewhere (eg from X1 onwards) elsewhere in the worksheet
which is made up like this:
Labour 25
'972 150
Dynapak 60
Loader 150
etc
and in this table you would list all your Equipments and the price for
each. Just keep adding items to the table - it may end up occupying
cells X1:Y50 or more. Notice that I have entered the number 972 with
an apostrophe in front of it - this will not show in the table, but it
is needed to turn the number into text.
Then in your rate column (B2) you would have this formula:
=IF(A2="","",VLOOKUP(A2,$X$1:$Y$100,2,0))
Copy this formula down column B for as far as you like. It will show a
blank if the cell in column A is blank, but otherwise it will give you
the rate for the item in column A. I have assumed here that the table
extends no further than X1:Y100, but you can adjust this if necessary
- it won't do any harm if it is too big (and allows your table to
grow).
You could then use this value elsewhere, eg:
=IF(B2="","",B2*D2)
to give you the hours times the rate - again, this can be copied down
as far as you need.
Hope this helps.
Pete
On Jul 6, 7:48 pm, Xina5280 <Xina5...@discussions.microsoft.com>
wrote:
> Maybe this will help. Here is a sample of the spreadsheet. I am trying to
> assign a rate to the specific equipment used so when I type in the equipment
> in the future, it will automatically have the rate pop up without me having
> to type =labor (or =whatever equipment used).
>
> Equipment Rate Description Hours Loads Price
> 972 150 Site fill w/ import-build pad 5.5 825
> Labor 25 Set up/tear down water hose 1 25
> 972 150 Site fill pad 10.5 1575
> 972 150 Site fill pad 10.5 1575
> 972 150 Site fill & place/bury topsoil 9.5 1425
> Labor 25 Expose and cap sewer line at east property 1 25
> Dynapac 60 Roll pad 5.5 330
> Dynapac 60 Compact site fill and pad 5.5 330
>
> --
> Christina
>
>
>
> "Bob Phillips" wrote:
> > 1) precede them with _
>
> > 2) select the range of labels and data and goto Insert>Name>create and
> > select the label range.
>
> > --
> > ---
> > HTH
>
> > Bob
>
> > (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> > "Xina5280" <Xina5...@discussions.microsoft.com> wrote in message
> >news:0937C22B-79D6-4C4E-9D35-(E-Mail Removed)...
> > > Yes it gets me started. I only have 2 issues. Some of my equipment is in
> > > numbers and Excel doesnt seem to allow me to define a name to a number.
> > > Is
> > > there any way around that? And I would like to automatically duplicate
> > > the
> > > value without me having to type =labor
> > > --
> > > Christina
>
> > > "Pete_UK" wrote:
>
> > >> You could define a name (Insert | Name | Define) and call it Labour
> > >> (or labor if you prefer) and in the Refers To box you can specify 25.
> > >> Then define another name Loader and have that refer to 150.
>
> > >> Then in your worksheet if you type =Loader into a cell, you will see
> > >> the value 150 appear. If you type =3*Labour + Loader then you will see
> > >> the result of 3 hours of labour plus the cost of the loader. Is that
> > >> what you want?
>
> > >> Hope this helps.
>
> > >> Pete
>
> > >> On Jul 6, 5:14 pm, Xina5280 <Xina5...@discussions.microsoft.com>
> > >> wrote:
> > >> > I am creating a spreadsheet for a project and I am trying to assign a
> > >> > numerical value to certain types of equipment used. For example,
> > >> > labor=$25
> > >> > Loader=$150 ect. I have multiple equipment that is used throughout the
> > >> > spreadsheet and I would like for them to duplicate the value when I
> > >> > enter in
> > >> > future information. Thanks!- Hide quoted text -
>
> - Show quoted text -
|
Similar Threads
