drop down list selection

I have an estimating form that we use for estimating service jobs. I entered 
a dropdown box at the very top to allow the user to select the correct 
customer. However, there are special pricing rates that pull from another 
sheet ('Special Rates') this sheet is in order by customer account number. 
However, when a customer name is selected from the dropdown box in C83, the 
formula set up in the preceding column E84, at needs to pull the correct 
customer account number to allow for the rest of the sheet to calculate 
properly is, =IF(E84=FALSE,LOOKUP(C83,CustomerName,'SPECIAL 
RATES'!A:A),"STANDARD") this only allows for the next, "near match" to pull 
up and is resulting in the selection of the wrong account number which 
results in the wrong pricing to be selected. Can anyone help?
0
Utf
11/30/2009 9:06:06 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
882 Views

Similar Articles

[PageSpeed] 35

Formula to find exact match and return the same relative position from 
Special Rates:

=IF(E84=FALSE,INDEX('SPECIAL RATES'!A:A,MATCH(C83,CustomerName,0)),"STANDARD")

-- 
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"CandiC" wrote:

> I have an estimating form that we use for estimating service jobs. I entered 
> a dropdown box at the very top to allow the user to select the correct 
> customer. However, there are special pricing rates that pull from another 
> sheet ('Special Rates') this sheet is in order by customer account number. 
> However, when a customer name is selected from the dropdown box in C83, the 
> formula set up in the preceding column E84, at needs to pull the correct 
> customer account number to allow for the rest of the sheet to calculate 
> properly is, =IF(E84=FALSE,LOOKUP(C83,CustomerName,'SPECIAL 
> RATES'!A:A),"STANDARD") this only allows for the next, "near match" to pull 
> up and is resulting in the selection of the wrong account number which 
> results in the wrong pricing to be selected. Can anyone help?
0
Utf
11/30/2009 9:34:02 PM
Reply:

Similar Artilces:

a comment box that only displays when the cell is actually selected
I have an Excel 2002 worksheet that I created a few years ago, and I can't remember how I got a certain cells to do what it's doing. Whenever you select that cell, a box appears just below that cell that looks like a comment box, but without the connecting arrow. But unlike a comment box, this one doesn't appear when you hover the cursor above the cell. It only appears when you select the cell; and it remains visible as long as the selection cursor remains in the cell, regardless of where you move the cursor. I dont' think it's any kind of a comment box, because w...

Generate list
I have a spreasheet with over 25000 records, in one of the columns I have the salesperson's name. There are over 60 different names in that column. Is there a way (apart from creating a pivot table) that excel can generate a list of the names contained in that column? Basically what I'm asking is if it's possible to convert the "autofilter" content into a list... Hi R4ym0nd Use Advanced filter http://www.contextures.com/xladvfilter01.html#FilterUR -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "R4ym0nd" <R4ym0nd@discussions.microsoft....

Change Pivot Table Field List
I'm looking for an example of how to turn on/off certain fields in a pivot table. What version of Excel do you have? I am using Excel 2007 and here's what I do. Right click anywhere in the pivot table and say "Show Field List". From there I can check or uncheck fields I want or do not want to show. If you right click the pivot table again you can hide that list. "S Himmelrich" <himmelrich@gmail.com> wrote in message news:a54c0a1a-476c-4682-bc5f-95620be56ef6@f20g2000yqg.googlegroups.com... > I'm looking for an example of how to turn on/off certain...

HELP! How to transfer Block Senders List
Help..... I have been using Outlook Express for a long time, but I haven't yet found a way to transfer a 'Block Senders List' from one PC to another PC. Am I missing any simple way to do this or this cannot be done without re-typing the whole list ... Any suggestions? Okienuah wrote: > Help..... > > I have been using Outlook Express for a long time, but I > haven't yet found a way to transfer a 'Block Senders List' > from one PC to another PC. Am I missing any simple > way to do this or this cannot be done without re-typing > the whole lis...

drop down selection determines other drop down content
I'd like to set up dropdown lists in which their content (list) is determined from a selection of another dropdown. Example: If you select 'project1' from columnA's dropdown list, columnB's dropdown list content will feature task1, task2, & task3. While if you select 'project2' from columnA's dropdown list, columnB's dropdown list content will feature task4, task5, & task6. I've tried using VLOOKUP to accomplish this task with no success; any advice will be greatly appreciated. See http://www.xldynamic.com/source/xld.Dropdowns.html a...

How do I create a form with drop downs that narrows choices?
Hi. Imagine I have 10 brands, 5 sizes, 5 states, etc. Brand 1 has size 1 and 3, and is in states 3,4 and 5 Brand 1 has size 4 in state 1 Brand 2 has size 3, and is in states 1,2 and 3 I would like to set up a form or template with drop downs that enable you to choose a number of selections (brand, size, state, etc.), and return a specific value in each column. So, if the columns are brand, size, state, you choose brand 1. The only options in the size column/drop-down menu would be 1, 3, and 4. If you choose 4, the only choice is state 1. If you choose 1 or 3, you have the choice of...

Partially invoicing a Drop-Ship Order Linked to a Drop-Ship PO
Can you partially invoice a drop-ship order in SOP that is linked to a drop-ship PO? We received an invoice from one of our vendors that was for a drop- ship PO we issued to them. They did not ship the complete order...only a partial. We matched the partial invoice to the drop- ship PO and processed it, but now need to invoice the customer for the partial. In the drop-ship order in SOP, it shows the quantity fulfilled for the line item as the amount from the vendor invoice, but in the Quantity to Invoice field, it shows the total quantity ordered when the customer placed the order. Is the...

Remove mailbox from Everyone distribution list?
I have a mailbox user that I do NOT want to be part of the "everyone" distribution list. I don't want to make it a "hidden" mailbox since I do want people to send emails to him. Any ideas if this is possible? I'm on Exch 5.5 in a Windows 2000 environment. thanks "Pablo" <pmosca@citizensconferencing.com> wrote in news:1fdc01c44a45 $8113e4e0$7d02280a@phx.gbl: > I have a mailbox user that I do NOT want to be part of > the "everyone" distribution list. I don't want to make it > a "hidden" mailbox since I do want...

drop down box linking worksheets
I want to create a drop down box on 30 different worksheets so that when I want to go to a certain worksheet I can just look through the drop down list. I know how to create a drop dowwn list for a single worksheet, but not one like this. I have data for the last 24 weeks on different worksheets and I need this list. thanks. Did you know that you could rightclick on the VCR like controls (to the left of the worksheet tabs) and see a list of all the worksheets? Another option is to add hyperlinks to each sheet. But I think I'd use a toolbar. You could use it for more than this workbook ...

drop and drag not working properly
One of my users says that all of a sudden when she drags and drops attachments it appears in the body of her email rather than setting up the attachment line. It was fine until last week. Then, on Friday, it started working correctly and today it is going wrong. What do I do to fix this? Thank you for any assistance. Where the attachment is placed on a mail message is governed by the format of the mail message. Within the body means that the message is in Rich Text and within its own field means the format is either Plain or HTML. "Beth O" <bowen@wwwinsagency.com> wrote ...

Drag & Drop into CTreeCtrl
I am unable to get the MyTreeCtrl::OnDropFiles handler to respond to the WM_DROPFILES message. I have set the Accept Files flag to true. Using Spy I can also see that the WM_DROPFILES message does arrive at MyTreeCtrl. I can not look at the message properties because Spy freezes the system for some reason, but that is a side issue. According to this thread, it should work and I should be able to break in the handler. I am using VS8. RT Problem solved. Handler was in wrong CWnd. Should have been in derived CTreeCtrl class. Works fine now. RT ...

Distribution List members gets a message more then once
Hi, I created a Distribution List group that contains some members with an external EMail Address(regular smtp address) and some with an Exchange Mail box. It Appears that the users with the Exchange EMail Address gets each message more then once.They get once in an hour the same message again. Where did I go wrong? Please help. Thanks, Shif Arad HI I am also getting this problem We have just changed our Internet Connection, and therefore created a new Routing Connection. Does anyone know if this is related Thank Mat ----- Shif Arad wrote: ---- Hi I created a...

drop down values based on the another drop down
There are two drop down columns A & B, I want the drop down values of to be displayed based on the value selected for A -- Gupt ----------------------------------------------------------------------- Gupta's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2782 View this thread: http://www.excelforum.com/showthread.php?threadid=47330 Hi Gupta take a look at Debra Dalgleish's site for detailed examples of how to achieve this. http://www.contextures.com/xlDataVal01.html Regards Roger Govier Gupta wrote: >There are two drop down columns A & B...

Drop-down list #2
Any one able to share with me 1)how to create a drop down list 2) how to have a scroll bar relate to a cell? Hi Susan 1. One way: Use data validation. Have a look at http://www.contextures.com/xlDataVal01.html for more information 2. Not sure what you mean? Frank Susan wrote: > Any one able to share with me 1)how to create a drop > down list 2) how to have a scroll bar relate to a cell? ...

Bank balance issue on Account List page
Just noticed after converting to '08 that two accounts are showing Zero (0) Adj Balance but the Bank Balance column show a negative entry. I viewed each account and confirmed that the starting balance was 0 and the current balance was 0 so not sure why these two accounts are showing up having a negative Bank Balance. I also confirmed that at least one account was Balanced to the bank but that didn't fix the issue. Besides a possible repair fixing the issue, is there another solution or something else I didn't do? Thanks In microsoft.public.money, Geekster wrote: >...

Help: automaticaly adding reply address to contact list.
I thought that there was a way in Outlook to automatically add the email address of individuals that I reply too, to my contact list. Let me know if you have any ideas. Thanks for any advice. -- Dab Dab <noThanks@hotmail.com> wrote: > I thought that there was a way in Outlook to automatically add the > email address of individuals that I reply too, to my contact list. > Let me know if you have any ideas. Not in any recent version of Outlook. I think OL 2000 in IMO could do it. -- Brian Tillman See http://www.slipstick.com/contacts/addauto.htm for various ways to sa...

Update Excel Data List for Drop Down use
I have created data lists that were named (name field left of formula field) and have those lists tied to cells as drop down data validation options. I now need to add additional entries to the data lists, but am unable to get them added into the existing groups. I've tried re-highlighting and naming again - no go. I am an admitted novice, but really look forward to getting this recurring document as drop and click as possible. Appreciate any help. You could use a dynamic range, that would automatically expand to include new entries. There are instructions here: http://www....

Drop down list question...
Here is one for you... I want to make a workbook with the following ability. Pick an item from that drop down list in column "A" and it shows a price in column "B". I would assume I need a second sheet with the data paired up, but how do I set it up to pull said data to another sheet? Hi see: http://www.contextures.com/xlDataVal01.html and http://www.contextures.com/xlFunctions02.html -- Regards Frank Kabel Frankfurt, Germany "GR" <gordonrmac@NOSPAMgmail.com> schrieb im Newsbeitrag news:fGcqd.225798$9b.182025@edtnps84... > Here is one for you... ...

Linking drop down boxes
I'm attempting to create a form where I have a number of columns of dro down boxes of lists. I have one column where I have created drop down boxes linked to a mai list. Each item of the main list has a separate "daughter" list. Each "daughter" list is different. In the form, I want the adjacent column related to the main list dro down choices to contain a drop down list of the relevant "daughter information only; in other words, when one item from the main list dro down box is selected, the choices in the adjacent drop down list mus relate to the selected main...

Personal Distribution List
Hello All: Is it possible to hide the membership from the Personal Distribution List (the one setup in Outlook Contacts folder) in Outlook 2003? Thanks. no, because AD/Exchange doesn't really recognize the PDL...if you need to send email to a PDL and not have anyone know who it went to, use the "bcc:" field line to address it... -- Susan Conkey [MVP] <ocean> wrote in message news:%23LmbXe2AHHA.4348@TK2MSFTNGP04.phx.gbl... > Hello All: > > Is it possible to hide the membership from the Personal Distribution List > (the one setup in Outlook Contacts...

dropdown list...no help in the help files
I need to create a drop-down list that contains catalog items & prices, & when the selection is made I need the prices from this list & others to all be summed up automatically in a one cell. The process described in the help file on drop-down lists produces some weird result...it can't be published as a web page. I need this to work as a standard editable Excel file from my company's end, & work as a non-editable webpage to online users. Any advice? Should I (or could I) import the Excel data we already have, & create it in FrontPage? :confused: -- miketv -...

distribution list not being updated after changes?
we have a distribution list in our public folders for our customer service people. last week we had one leave, and one new person come on, so we removed one email address, and added another. but- people are still sending emails to the customer service DL that goes to the old person, and leaves the new one out. I checked, the the new person is in the DL, and the old one is not. is this a cache issue of some kind? How do you get around this? gary Not sure how you've got this setup, but the right way to do this would be to create the Distribution Group in AD instead, imo. -- Bh...

Question about lists ?
Hello all I use a drop down list to allow users of a worksheet to select customer names. Is it possible to open the list and press the first letter of the customer name and be taken to the first occurance of that letter to save having to scroll down the entire list ? My thanks in anticipation Nigel. That's not possible in a standard data validation list. You can use a combo box instead, as described here: http://www.contextures.com/xlDataVal11.html Or add letters in the source list, as shown in Roger Govier's sample file here: http://www.contextures.com/excelfiles.htm...

Drag Drop problem inCListCtrl
Hello , I have a owner drawn list view which displays the thumbnail images of image files along with other related information like destination of file and file properties. I am trying to drop drop the images in the list view but the following takes place : CreateDragImage returns me a CImageList pointer which is not null , when i do a GetImageInfo using the CImageList pointer the IMAGEINFO structure contains NULL values.Also BeginDrag returns FALSE. What am i doing wrong, please do help me. Thanks in Advance example: www.codeguru.com/Cpp/controls/ listview/dragdrop/article.php/c941/ &quo...

ComboBox drop up instead drop down
Hello Clicking on the Combobox right box it show by default an SelectionWnd BELOW the combobox, but I want it show ABOVE the Combobox. How can I achieve this? Thanks. Tom AFAIK, you cant do this. It does it by itself whenever it finds that the listbox will go offscreen. --- Ajay "Ajay Kalra" <ajaykalra@yahoo.com> wrote in message news:1147880303.638355.7200@j73g2000cwa.googlegroups.com... > AFAIK, you cant do this. It does it by itself whenever it finds that > the listbox will go offscreen. > Couldn't you subclass the listbox and somehow reposition it w...