Calcs & Data Validation

Looking for an easy way to complet the following. I imagine their must be a 
quick & easy method.
Cell A5 has a drop down list which corresponds to a range on another 
worksheet. It has 300+ entries. In cell B5 i will enter a number/qty. What i 
would like is that as each of the items in the list has a corresponding cost 
(on other worksheet),  i would like to know an easy way to calculate the cost 
(in C5) based upon the qty entered (B5) & the cost of the item selected. 
I have done it before with 'IF' function for a small selection, but figure 
there must be an easier way with 300+ items. 
Hope i havent made this to confusing

Thanks in advance for any assistance
0
Utf
2/9/2010 12:32:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
364 Views

Similar Articles

[PageSpeed] 36

See if this is what you had in mind...

Assuming each item in the list is unique.

Sheet2 A2:A300 = items
Sheet2 B2:B300 = price

Sheet1 A5 = drop down list of items
Sheet1 B5 = qty
Sheet1 C5 = formula:

=SUMIF(Sheet2!A$2:A$300,A5,Sheet2!B$2:B$300)*B5

-- 
Biff
Microsoft Excel MVP


"Andrew" <Andrew@discussions.microsoft.com> wrote in message 
news:1FDC1256-2FE6-439B-A376-76A85EBE293C@microsoft.com...
> Looking for an easy way to complet the following. I imagine their must be 
> a
> quick & easy method.
> Cell A5 has a drop down list which corresponds to a range on another
> worksheet. It has 300+ entries. In cell B5 i will enter a number/qty. What 
> i
> would like is that as each of the items in the list has a corresponding 
> cost
> (on other worksheet),  i would like to know an easy way to calculate the 
> cost
> (in C5) based upon the qty entered (B5) & the cost of the item selected.
> I have done it before with 'IF' function for a small selection, but figure
> there must be an easier way with 300+ items.
> Hope i havent made this to confusing
>
> Thanks in advance for any assistance 


0
T
2/9/2010 5:49:10 AM
Genius Biff. works great
Thanks mate.

"T. Valko" wrote:

> See if this is what you had in mind...
> 
> Assuming each item in the list is unique.
> 
> Sheet2 A2:A300 = items
> Sheet2 B2:B300 = price
> 
> Sheet1 A5 = drop down list of items
> Sheet1 B5 = qty
> Sheet1 C5 = formula:
> 
> =SUMIF(Sheet2!A$2:A$300,A5,Sheet2!B$2:B$300)*B5
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "Andrew" <Andrew@discussions.microsoft.com> wrote in message 
> news:1FDC1256-2FE6-439B-A376-76A85EBE293C@microsoft.com...
> > Looking for an easy way to complet the following. I imagine their must be 
> > a
> > quick & easy method.
> > Cell A5 has a drop down list which corresponds to a range on another
> > worksheet. It has 300+ entries. In cell B5 i will enter a number/qty. What 
> > i
> > would like is that as each of the items in the list has a corresponding 
> > cost
> > (on other worksheet),  i would like to know an easy way to calculate the 
> > cost
> > (in C5) based upon the qty entered (B5) & the cost of the item selected.
> > I have done it before with 'IF' function for a small selection, but figure
> > there must be an easier way with 300+ items.
> > Hope i havent made this to confusing
> >
> > Thanks in advance for any assistance 
> 
> 
> .
> 
0
Utf
2/9/2010 10:11:01 PM
You're welcome. Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


"Andrew" <Andrew@discussions.microsoft.com> wrote in message 
news:0493159A-08AB-4314-8E23-384A675DBC03@microsoft.com...
> Genius Biff. works great
> Thanks mate.
>
> "T. Valko" wrote:
>
>> See if this is what you had in mind...
>>
>> Assuming each item in the list is unique.
>>
>> Sheet2 A2:A300 = items
>> Sheet2 B2:B300 = price
>>
>> Sheet1 A5 = drop down list of items
>> Sheet1 B5 = qty
>> Sheet1 C5 = formula:
>>
>> =SUMIF(Sheet2!A$2:A$300,A5,Sheet2!B$2:B$300)*B5
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Andrew" <Andrew@discussions.microsoft.com> wrote in message
>> news:1FDC1256-2FE6-439B-A376-76A85EBE293C@microsoft.com...
>> > Looking for an easy way to complet the following. I imagine their must 
>> > be
>> > a
>> > quick & easy method.
>> > Cell A5 has a drop down list which corresponds to a range on another
>> > worksheet. It has 300+ entries. In cell B5 i will enter a number/qty. 
>> > What
>> > i
>> > would like is that as each of the items in the list has a corresponding
>> > cost
>> > (on other worksheet),  i would like to know an easy way to calculate 
>> > the
>> > cost
>> > (in C5) based upon the qty entered (B5) & the cost of the item 
>> > selected.
>> > I have done it before with 'IF' function for a small selection, but 
>> > figure
>> > there must be an easier way with 300+ items.
>> > Hope i havent made this to confusing
>> >
>> > Thanks in advance for any assistance
>>
>>
>> .
>> 


0
T
2/10/2010 1:56:43 AM
Reply:

Similar Artilces:

Evening the data
Have potted some line charts from data tables relating to vehicle flow volumes. The maximum allowable flow is 24000, however, actual volumes exceed this. I can limit the chart to only show up to 2400 but does anyone know a way to automatically re-distibute the 'excess' flow and include it below the maximum volume and to the right of that point?? Does that make sense???? Thanks I don't quite see what you're after. Perhaps a small numerical example would help here. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Philip Drury" wrote: > Have...

How do I get all my data on one page instead of two
How do I get all of my data onto one page instead of two? One way: File/Page Setup. In the Page tab, scaling section, click the Fit to radio button and enter 1 page(s) wide by 1 tall There are other ways, undoubtedly, but you didn't give anything to go on. In article <1639B9EA-B185-4F24-AA27-809359B33C3C@microsoft.com>, rmikulich <rmikulich@discussions.microsoft.com> wrote: > How do I get all of my data onto one page instead of two? ...

Verify an xml file is valid
Hi; What's the fastest way to verify that an xml file is well-formed (and valid if it has a dtd)? I don't want to pull any data out, I just want to make sure it is a good file. (When the user selects an xml file to save and use in the future, we want to do a fast sanity check on it.) -- thanks - dave david_at_windward_dot_net http://www.windwardreports.com David Thielen wrote: > Hi; > > What's the fastest way to verify that an xml file is well-formed (and valid > if it has a dtd)? I don't want to pull any data out, I just want to make sure > it is a go...

Column calcs & Row calcs
I have a row calc doing B/C and the column doing B-C. I want the column formula to win but the row calculation is being performed. In Report options -> Advanced tab my Calculation priority is set to Calculate Columns first. What am I doing wrong? ...

Entering Interest Only ARM mortgage data into MM2005
How do I enter the data correctly in MM 2005 for my loan type. I have not been able to figure this out. Any help would be appreciated! See http://umpmfaq.info/faqdb.php?q=160. There is an update coming to this one as soon as I get to it, but it won't change the substance of the answer. The update will refer to http://support.microsoft.com/default.aspx?scid=kb;en-us;893719. "Jason Stout" <Jason Stout@discussions.microsoft.com> wrote in message news:2289EADB-28F9-4B21-A6B0-8F61FB36C8D1@microsoft.com... > How do I enter the data correctly in MM 2005 for my loan type....

How to pick a biggest data from a group of data? #2
Thanks,I meet another question: I have some data with a date in he format below 03/11/1998 03/12/1998 03/13/1998 03/16/1998 I want to convert them into 19980311 19980312 19980313 19980316 I tried to set the cell property,but it didn't work. Could you please help to give me a formular to do so? Thanks in advance -- yoyo200 ----------------------------------------------------------------------- yoyo2000's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1548 View this thread: http://www.excelforum.com/showthread.php?threadid=27212 Use the custo...

data range
Hi I have a problem with external data. In the Data Range Properties, under Data Layout. There are 3 options after 'If the number of rows in the data range changes upon refresh:' 1 - Insert cells for new data, delete unused cells 2 - Insert entire rows for new data, clear unused cells 3 - Overwrite existing cells with new data, clear unused cells. I want option 3 to be the default oprion but it always defaults back to option 2. Is there any way I can change this.?? Cheers Baz ...

Keeping socket events coming and receiving all data
We're using CSocket, CSocketFile, and CArchive in our project. In an effort to keep this posting as streamlined as possible, I ask you to read first my original posting which describes the problem I was seeing and the environment in more detail. Please refer to "WM_SOCKET_NOTIFY just stops", dated 8/4/2004. The reply I received from George N to the original posting boosted my confidence in my approach to a workaround. I understood George's answer to mean this: as long as I reenable socket event notification before our thread's message loop runs again, there lik...

Query: Link data between worksheets?
I'm trying to find a shortcut to save me some time. This is the problem: I'm setting up a master worksheet, and 30 to 40 "slave" worksheets tha need to run off it (all within the same workbook). The slave worksheet all retrieve data from the master. The data is setup to run across i rows (i.e. Row 1 contains 1 record). Each record has an individua identification number entered in column A. Is there a way that I can tell the slave sheets to copy an entire row based purely on the identification number entered in column A? Thanks in advance if anyone can help, as this could ...

Project Accounting
Hi, When I run the Cycle Biller, it is automatically computing a state sales tax amount. The project budget for all cost categories are set as non-taxable. Where is the tax schedule defaulting from? Thanks. I believe it is default setup from the customer record under "Ship to" and depends on what sales tax id you have set-up at that screen. "Glen" wrote: > Hi, > > When I run the Cycle Biller, it is automatically computing a state sales tax > amount. > The project budget for all cost categories are set as non-taxable. > > Where is the t...

filter data #2
Hi This formula searches an entire column returning unique answers =IF(B11="","",IF(COUNTIF($B$4:B11,B11)>1,"",B11)) what i need to do is limit the range it searches over ie from row 4 to row 20 thanks in advance kevin I suggest setting up a name range by going to Insert - Name - Define an then using the name range in your formula. =IF(B11="","",IF(COUNTIF("named range",B11)>1,"",B11)) See how that works -- Message posted from http://www.ExcelForum.com Chewy667 <<Chewy667.1bnfuj@excelforum-nospam.com&g...

Plotting data in Excel
I would like to control where my secondary data is plotted along the x axis. Currently, the graph clusters the data together (even though it is spread out on the worksheet). I would like to line up the monthly totals (secondary data) with the last plot for that month (primary data). That is, directly behind the last date of the month, the month's total will also be plotted. Any advice? Can you give an example of your primary and secondary data. I can't seem to envision what you want. "Dave Mac" <Dave Mac@discussions.microsoft.com> wrote in message news:A3E0FA49-...

Wpf
I have a couple instances where I am using a set of data to define a window for testing purposes but I can't seem to find any place that really explains it. <Path Fill="#80D0E0FF" Stroke="Red" StrokeThickness="3" HorizontalAlignment="Center" VerticalAlignment="Center" Data="M79,3L65,82 17,91 50,138 96,157 104,192 175,154 190,167 218,78 156,76 157,9 111,39z"/> In the Data attribute, I am trying to figure out what that means with the M's, Ls and z's. I have seen this shown ...

Drop and Drag Cell Data
I have an application where I need to Drop and Drag Cell Data. I have followed the suggestions that I've gotten from several Forums, but none seem to work. Basically they suggest that I put the following statement in the Workbook_Open routine: Application.CellDragAndDrop = True Nothing happens! I have a feeling that more code is required. Any suggestions will be greatly appreciated. TomD i have a feeling some more information is required. please give some more specific details of what you want to accomplish. -- Gary Keramidas Excel 2003 "TomD" ...

Calc
Is there a way to stop a workbook from calculating in mid stream? I have some large workbooks that often get stuck when I move or copy a worksheet within or into the file. Hi goto 'Tools - Options - Calculate' and set the calculation mode to manual -- Regards Frank Kabel Frankfurt, Germany anonymous@discussions.microsoft.com wrote: > Is there a way to stop a workbook from calculating in mid > stream? I have some large workbooks that often get stuck > when I move or copy a worksheet within or into the file. ...

transfer data files Outlook 2007
have transferred data files following the hopefully correct procedure from an XP set up to a Win7. The date files are big. When I try and do the final step to import the pst files to the (single)user profile, having copied the files to the hard disc after external drive transfer, the message "the messaging interface has returned an unknown error etc" appears and of course I then can't proceed. From reading this site I can see there may be problems inside the files to generate this message. How to resolve as I can't find how to defrag or whatever it is necess...

exporting data #2
can excel data be exported into quicken? abbylulu2 wrote: || can excel data be exported into quicken? Have you asked Quicken? (Hint - look under Import....) -- Interim Systems and Management Accounting Gordon Burgess-Parker Director www.gbpcomputing.co.uk ...

Not all my data is showing up in my chart...
I highlight all the data I want to plot in the XY Scatter chart (8 rows of data) Only 7 rows are plotted though. There are no formulas or anything. Can someone please help me. What does in show under "Source Data" for the chart? My guess is that it may have taken the first row of data and used that as the X values. The usual advice from Jon Peltier (the charting guru) is that you leave the top-left hand corner cell empty, so that Excel recognises the first row (if your data is in rows) as X values, and also recognises the first column as series labels. Even if you haven'...

show only fields where data is inside access 2002
Hello, is it possible to generate a report where only these fields are shown, in wich data can be found? I have a large table and everyone has somthing in another colum. so if i make a report i have all the coloums in there but only need some of them. please excuse my english ( if this is english at all) Peter If you set the Can Shrink property of a text box in your report, it will take up no vertical height in the report when the field is Null. It only works if there is nothing else beside it (overlapping it vertically.) Typically you have a label beside it, and so the label prevents ...

Can't edit data in query
Hi everyone. Using A02 on XP. This may be a dumb question but I have a UNION ALL SELECT SQL query showing me all records from 3 tables [tDistributions], [tDistributionsArchive] and [tDistributionsArchiveOld]. Why can I not edit any fields? I receive an Excel file with uncashed check numbers, date mailed and payable to. I have to search through all distribution records to identify which distribution record's check did not cash yet so we can pursue payment. Because the tables are old and inherited, my unique identifier is only unique in 2 of the tables so there is a bit of sea...

Microsoft CRM could not validate this instance of SQL Server ------HELP!
We get the above error when trying to install CRM on a WIn2K Server running SQL 2000 SP3a. We are trying to install with an administrator account which has local admin accounts on this box. Microsoft CRM support is lost. Any helpers?? PLEASE! BJ Did you install SQL before or after promoting the server to a DC? If so, try re-installing SQL. The SQL install makes changes to AD that are needed by CRM. It has to do with how the authentication schemes work. http://groups.google.nl/groups?hl=nl&lr=&threadm=cdoc405c085kpfgqmqg372sghdvhqmvjc7%404ax.com&rnum=2&prev=/groups%3Fq%3D...

missed data
The following Vlookup formula has this link to another workbook supposedly in the same folder where the workbook is placed, but there is no such a file with this name, where is this DATA.xls stored =BUSCARV($B76;'C:\Documents and Settings\rjimenez\Mis documentos\[DATA.xls]Recursos'!$B$2:$E$1501;2;FALSO) thanks for your help -- rjl ------------------------------------------------------------------------ rjl's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30117 View this thread: http://www.excelforum.com/showthread.php?threadid=498475 rjl schreef: &...

adding one same data to each of four other data points in column c
My chart has four seperate options, I am wanting to add one same data to each of the four options to have a total amount, including the one like data, to each of the four options. I want this chart to be a column chart. ...

arrange data
Hi, I've got certain data in an excel sheet horizontally and vertically. Eg Product A Product B Product C Sales xxxxxxx xxxxxx xxxxxx Gross margin xxx xxx xxxxx Marketing expenses xxxx xxx xxxxx Selling expenses xxxxx xxxx xxxxxx Admin expenses xxxxx xxxxx xxxxxx Profit xx xx xx...

Arranging a data in required format
Hi, I have attendence data of all the employees in sequence of swipes done on the machine. Eg. Employee Number Date Time 1 - 11/29/2009 - 08:32:20 3 - 11/29/2009 - 08:37:59 9 - 11/29/2009 - 08:46:21 5 - 11/29/2009 - 08:57:59 9 - 11/29/2009 - 08:59:21 3 - 11/29/2009 - 18:37:59 9 - 11/29/2009 - 18:46:21 5 - 11/29/2009 - 18:57:59 This continues f...