Validation, VlookUp

I have a table with 4 columns.  3 columns (Product, Mode, 
City) have data that I choose in 3 validation lists.  In 
turn, I want to match up (link) the Product, Mode and 
City from the lists, look them up in the table to get the 
number of units.  I tried to use the offset /vlookup 
options but could not match the 3 items.  As an example: 
Product: A, Mode: TC, City: Salem.  In matching these the 
Number of units is 34.  Formula would go in the yellow 
cell.  Attached is the spreadsheet.  Thanks......Comicfly
Product	Mode	location	Number of		
	
A	TT	NYC	 20			A
A	TC	Salem	34			TC
A	DR	Richmond 6			Salem
A	Tote	Houston	45			
B	TC	Miami	23			
B	DR	Chicago	12			
C	TT	LA	46			
C	TC	San Diego	4			
C	DR	Tulsa	16			

						

0
comic_fly (3)
9/12/2004 10:03:07 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
721 Views

Similar Articles

[PageSpeed] 5

=SUMPRODUCT((RangeA=Cellref1)*(RangeB=Cellref2)*(RangeC=Cellref3)*(RangeD))

Assuming you have 3 cells cellref1,2,3 that contain the criteria for your 3 
columns Prod/Mode/City, then the formula above when substitued with real ranges 
will sum all units found in RangeD that match criteria for A/B/C

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Comicfly" <comic_fly@hotmail.com> wrote in message 
news:0cb901c49914$4924a330$a501280a@phx.gbl...
>I have a table with 4 columns.  3 columns (Product, Mode,
> City) have data that I choose in 3 validation lists.  In
> turn, I want to match up (link) the Product, Mode and
> City from the lists, look them up in the table to get the
> number of units.  I tried to use the offset /vlookup
> options but could not match the 3 items.  As an example:
> Product: A, Mode: TC, City: Salem.  In matching these the
> Number of units is 34.  Formula would go in the yellow
> cell.  Attached is the spreadsheet.  Thanks......Comicfly
> Product Mode location Number of
>
> A TT NYC 20 A
> A TC Salem 34 TC
> A DR Richmond 6 Salem
> A Tote Houston 45
> B TC Miami 23
> B DR Chicago 12
> C TT LA 46
> C TC San Diego 4
> C DR Tulsa 16
>
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.760 / Virus Database: 509 - Release Date: 10/09/2004 


0
ken.wright (2489)
9/12/2004 10:19:56 PM
That worked very well.  Thank you...........
Comicfly
>-----Original Message-----
>=SUMPRODUCT((RangeA=Cellref1)*(RangeB=Cellref2)*
(RangeC=Cellref3)*(RangeD))
>
>Assuming you have 3 cells cellref1,2,3 that contain the 
criteria for your 3 
>columns Prod/Mode/City, then the formula above when 
substitued with real ranges 
>will sum all units found in RangeD that match criteria 
for A/B/C
>
>-- 
>Regards
>           Ken.......................    Microsoft MVP - 
Excel
>              Sys Spec - Win XP Pro /  XL 97/00/02/03
>
>---------------------------------------------------------
-------------------
>It's easier to beg forgiveness than ask permission :-)
>---------------------------------------------------------
-------------------
>
>
>
>"Comicfly" <comic_fly@hotmail.com> wrote in message 
>news:0cb901c49914$4924a330$a501280a@phx.gbl...
>>I have a table with 4 columns.  3 columns (Product, 
Mode,
>> City) have data that I choose in 3 validation lists.  
In
>> turn, I want to match up (link) the Product, Mode and
>> City from the lists, look them up in the table to get 
the
>> number of units.  I tried to use the offset /vlookup
>> options but could not match the 3 items.  As an 
example:
>> Product: A, Mode: TC, City: Salem.  In matching these 
the
>> Number of units is 34.  Formula would go in the yellow
>> cell.  Attached is the spreadsheet.  
Thanks......Comicfly
>> Product Mode location Number of
>>
>> A TT NYC 20 A
>> A TC Salem 34 TC
>> A DR Richmond 6 Salem
>> A Tote Houston 45
>> B TC Miami 23
>> B DR Chicago 12
>> C TT LA 46
>> C TC San Diego 4
>> C DR Tulsa 16
>>
>>
>>
>
>
>---
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system 
(http://www.grisoft.com).
>Version: 6.0.760 / Virus Database: 509 - Release Date: 
10/09/2004 
>
>
>.
>
0
anonymous (74722)
9/13/2004 1:13:18 AM
My pleasure, and thank you for the feedback  :-)

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



<anonymous@discussions.microsoft.com> wrote in message 
news:ac0401c4992e$da77dbd0$a601280a@phx.gbl...
> That worked very well.  Thank you...........
> Comicfly
>>-----Original Message-----
>>=SUMPRODUCT((RangeA=Cellref1)*(RangeB=Cellref2)*
> (RangeC=Cellref3)*(RangeD))
>>
>>Assuming you have 3 cells cellref1,2,3 that contain the
> criteria for your 3
>>columns Prod/Mode/City, then the formula above when
> substitued with real ranges
>>will sum all units found in RangeD that match criteria
> for A/B/C
>>
>>-- 
>>Regards
>>           Ken.......................    Microsoft MVP -
> Excel
>>              Sys Spec - Win XP Pro /  XL 97/00/02/03
>>
>>---------------------------------------------------------
> -------------------
>>It's easier to beg forgiveness than ask permission :-)
>>---------------------------------------------------------
> -------------------
>>
>>
>>
>>"Comicfly" <comic_fly@hotmail.com> wrote in message
>>news:0cb901c49914$4924a330$a501280a@phx.gbl...
>>>I have a table with 4 columns.  3 columns (Product,
> Mode,
>>> City) have data that I choose in 3 validation lists.
> In
>>> turn, I want to match up (link) the Product, Mode and
>>> City from the lists, look them up in the table to get
> the
>>> number of units.  I tried to use the offset /vlookup
>>> options but could not match the 3 items.  As an
> example:
>>> Product: A, Mode: TC, City: Salem.  In matching these
> the
>>> Number of units is 34.  Formula would go in the yellow
>>> cell.  Attached is the spreadsheet.
> Thanks......Comicfly
>>> Product Mode location Number of
>>>
>>> A TT NYC 20 A
>>> A TC Salem 34 TC
>>> A DR Richmond 6 Salem
>>> A Tote Houston 45
>>> B TC Miami 23
>>> B DR Chicago 12
>>> C TT LA 46
>>> C TC San Diego 4
>>> C DR Tulsa 16
>>>
>>>
>>>
>>
>>
>>---
>>Outgoing mail is certified Virus Free.
>>Checked by AVG anti-virus system
> (http://www.grisoft.com).
>>Version: 6.0.760 / Virus Database: 509 - Release Date:
> 10/09/2004
>>
>>
>>.
>>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.760 / Virus Database: 509 - Release Date: 10/09/2004 


0
ken.wright (2489)
9/13/2004 7:42:50 PM
Reply:

Similar Artilces:

#NA Problem using VLOOKUP
Hi All, Im currently having an issue using VLOOKUP in that Im trying to get a football league table to sort automatically. My problem is that when two teams have the same points value the table will only display the first team it recognizes will that point value and display any other teams with that value as #N/A Can anyone please advise on how to rectify this problem. (I am new to using formulas etc so please be gentle with me) Thanks in advance for any assistance :) -- kingrobbo ------------------------------------------------------------------------ kingrobbo's Profile: http...

Msgbox entry validation
The following Macro will not loop more than twice the error entries. Will someone help me correct it to loop indefinely? Thanks Sub Entry_validation() Dim Teststring As String Dim Inputstring As String Cells(1, 1) = "ABC" 'as given filename Cells(2, 1) = "DEF" 'as given filename Cells(3, 1) = "GHI" 'as given filename Cells(4, 1) = "JKL" 'as given filename errorloop: Inputstring = InputBox(prompt:="Enter Filename" & vbLf & "Enter abort to abort en...

Data Validation -> Validation list is larger than the cell width
I have a workbook that I've just added data validation to one cell. FWIW, the data validation is based on a named range. Here's the (perceived) problem. When I've set up data validation in the past, the selection is the same width as the cell. In this case, the list starts almost a full cell width to the left. What am I missing? Thanks, Barb Reinhardt Barb, In Excel2003 I can replicate what you describe under the following conditions: 1. the named range contains entries that are wider than the data validation cell 2. The data validation cell is r...

Validating against a DTD
Hello All, I am trying to validate an xml file against a DTD. I went through a lot of examples especially: http://www.xmlforasp.net/codebank/util/srcview.aspx?path=../../codebank/System_Xml/XmlValidatingReader/Validator/validator.src&file=validator.cs&font=3 but its not working in my case. First of all, when i try to add my DTD file in XmlSchemaCollection object, it throws an error saying DocType element is expected. I am using NITF DTD for this and there is no DocType element in it. Any help will be appreciated. Thanks. VD You should not add DTDs to XmlSchemaCollection. XmlSchema...

Data Validation
Hi all I want to set data validation on a cell so that it will accept the following (and only the following) text strings: (1) "BR" (literally) or (2) "NT" (literally) or (3) "nA" where n is any positive integral numerical value including zero and A may take any of the values "L", "P", "T", "V" or "Y" or (4) "An" where n is any positive integral numerical value including zero and A may take either of the values "K" or "D". Is this possible, please, and if so how? thanks -- Return e...

Validation dropdown list is not coming visible
Hi, I'm using XP and 2003. I have a problem that I have once found resolution, but now it came again and cannot remember what needs to be done. Here is the problem: There is a column where Validation is specified in right manner with error message in case of error. When selecting the cell - dropdown list is not coming visible. In case I write something wrong it gives error message. If I recall correctly - it was something to do with some kind of list. How can I change this to work properly? I tried to paste new column from another file where the column is working as I want...

combination IF- and VLOOKUP-function fails
Hello, I've following problem: The first table shows freight rates for different locations in relation to specific base ports (AA and HH)! Table 1 LOC AA - AA AA - HH HH - AA HH - HH DKAAB 1001 1007 1013 1019 DKAAL 1002 1008 1014 1020 DKAEY 1003 1009 1015 1021 DKAZS 1004 1010 1016 1022 DKAAP 1005 1011 1017 1023 DKAGD 1006 1012 1018 1024 The second table actually shows the same data in a different format but without the freight rates. Table 2 From To LOC Result HH HH DKAAB ??? HH AA DKAZS ??? Now I would like to retrieve the freight from the first table with ...

Vlookup, COUNTIF, IF or Nested
Hello, I have a work sheet "Raw Data" of about 1500 rows and 30 Columns. In columns K, K, L, M, T and W are names of staff. In worksheet "Lookups" I have a defined list of "Leavers". I need to return the names of the leaver/s in the last column of the "Raw Data" sheet. eg: Row 15 Column K contains "Joe Blog" Column M contains "Micky Mouse" and Column W contains "Jane Doe". in my defined list of leavers "Joe Blog" and "Jane Doe" are listed, in the last column of the "Raw Data&quo...

reinstall-validation problem
Hardware hit from lightning so new hd and video card and start over. All goes well with install using a slipstreamed XP-Pro SP3 disk but I notice it never asks for the Product Key during install yet it Activates itself during one of the boots and I think little of it until ... During the updates which followed (100+!!!) I notice Windows Defender doesn't seem to run or leave itself installed. As it is running on my laptop figured I just download it. When I run it, it wants to first do the Genuine Advantage bs which I let it, at which time it says "bogus". Ugh, g...

Validation
Julie the same can be achieve without going thru the double drop down boxes. You could do it just using the combobox alone. Thank again Please stay in the ORIGINAL thread. The archives will thank you. -- Don Guillett SalesAid Software donaldb@281.com "JLong" <anonymous@discussions.microsoft.com> wrote in message news:034101c49c3b$889b5020$a501280a@phx.gbl... > Julie the same can be achieve without going thru the > double drop down boxes. You could do it just using the > combobox alone. Thank again ...

validating macro
Hi I am sending a spread sheet out to branchs I need them to complete a sheet (common info) before they do anything else on the workbook. I am looking for a macro or code for VBA which who check (common info) see if the cells are complete if not bring up a error screen informing the user to complete the form and then take them straight to the (common info) sheet. and if possible let them go to the tab which they want to go to. Use an extended version of something along these lines: (if there are lots of cells use a 'for row numbers x to y' or 'do until' statment, o...

Data Validation
I have set up a data form and have data validation rules on the various input cells. I see that there is an option called "Ignore blanks" in the data validation settings, which is supposed to restrict someone from entering past an input cell without entering valid data, if the "Ignore blanks" box is unchecked. I have tried to do this, but it does not seem to work. Please could someone tell me what I am doing wrong? Provide information, or better yet, a screen capture of the Data Validation dialog box with the Setting tab selected (User ALT+PrintScreen to copy the imag...

Reboot Validator
I'm running XP Pro, SP3. Attempting to install a program from Nero. Software keeps failing at startup, despite numerous re-installs. During installs, it keeps attempting to update the 'reboot validator' which it does not seem to be able to do. What is the reboot validator, and how do I update it. Couldn't find anything useful on MS site and Nero Tech support just says its an MS problem. -- Thank you, B. Parker Never heard of it but Nero Support should know about it: Reboot Validator appears to be a Nero component; cf. http://www.brighthub.com/comput...

vlookup: can i specify the column with a name rather than a number
eg. if a setup an array and name it "array" and the column header in column 6 is "column 6 header", then rather than specify: vlookup(A1,array,6,FALSE) can i put the column header in rather than the number 6 (noting that) vlookup(A1,array,column 6 header,FALSE) doesnt work ...

Conditional validation?
Hello All I have a sheet that is essentially just columns of data (except for the column headings in the first row), and two of the columns are what I would call 'mutually exclusive': i.e. if a value is entered in a row in either column, the user must be prevented from entering any value in the same row of the other column. The two columns already have some basic validation applied (ranges of permissable numerical values), and this validation would need to be applied for any values that are entered. I am sure this can be done, but cannot see how! Hope someone can help. Many thanks L...

Excel 2000 vs 2002 Sheet protection & Validation cells
Hello, I have an Excel spreadsheet built on Excel 2000. I have set sheet protection with password, and I have some unprotected cells with drop down menu (validation with 50 choices) Everything works very well. I sent this file to a friend who has Excel 2002. The same "unprotected" validation cells cannot be modified in his version as the message says that these cells are protected What can be done in order to make it work on his version?? I do have some "very hidden" sheet with password protected VBA properties, if that is an issue. Similarly there are some macros o...

REPOST: dynamic validation
I think I wasn't clear that I need the Validation option under the Data menu, List, source values. That is what I am trying to fill with this equation. In other words, range1 and range2 are sets cells which contain values. I want those values to be the dropdown options in the cell based on what the previous cell to the left is selected as. So if Range1 is three cells with Tall, Dark, Handsome and Range2 is Sweet, Pretty, Nice, then based on the selection of F or M in A1, I want the choices for B1 to be one of those lists. I know that quotes are required for the IF. My probl...

How to use data validation
[NB: I re-structure my question since people got misled from my previous one] What I really wish to ask is: I wish to use data validation for a particular cell. But it doesn't work if I choose "custom" in the "allow" box. 1) In Data | Validation | Settings Tab | Validation Criteria, there is an "Allow" Box. 2) I chose "custom", there is a formula. 3) I type in some formula, eg: =J3 (Just Reference) =J3-J1 (Reference-type formula) =round(J1) (Function-type formula) None of them can activate the validation fu...

vlookup combined with INDIRECT
I am using a vlookup to retrieve info from other files (number of hour spent on a specific job number). I am using INDIRECT to specify th RANGE for the VLOOKUP as I want to access many files (employe timesheets). I am using a CONCATENATE to build file names for all the timesheets fo each employee for each time period - this is the cell that the INDIREC function points to for the range of the VLOOKUP. This is close to working for me except that INDIRECT requires al external files to be open othewrwise it returns a #REF! does anyone have any ideas on this -- Wes ---------------------------...

Email Validation
I was testing this email validation rule. Is Null Or ((Like "*?@?*.com") Or (Like "*?@?*.org") Or (Like "*?@?*.net") Or (Like "*?@?*.mil") Or (Like "*?@?*.US") And (Not Like "*[ ,;]*" And Not Like "*.@*")) ...but noticed I was able to input .@any.com ... e.g., I was trying to prevent user typing a .@ [that is a dot@] using the ... And Not Like "*.@*")) .. but it must be getting cancelled out by something previous in the validation string. Any suggestions? You could use a routine such as: P...

Refer to seperate workbook for validation list for drop downs?
Id like to keep my validation data for drop downs in a seperate workbook so that many users/worksheets can reference it. However- data validation doesnt seem to let me reference named ranges in a seperate workbook- any advise out there? -- Dee Visit Debra Dalgleish's site: http://www.contextures.com/xlDataVal05.html But take note that the other workbook has to be open. Dee wrote: > > Id like to keep my validation data for drop downs in a seperate workbook so > that many users/worksheets can reference it. However- data validation doesnt > seem to let me reference named r...

Data Validation: Validate Specific Day of the Week?
Users will enter a date in cell D6. The date they enter must be a Monday. How can I validate for that? Thanks, -Ted I entered dates in E1:E10. I selected E1 In the Data | Validation dialog, I used Custom and entered =WEEKDAY(E1)=2 I selected all the dates and opened Data | Validation; it offered to apply the validation to all the cells Done best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Ted" <ted.gallagher@gmail.com> wrote in message news:#o$yRC6vKHA.404@TK2MSFTNGP02.phx.gbl... > Users will enter a date in ce...

If no value found in vLookup, I need to return a NULL to the Cell
I'm trying to setup a Lookup Table and if the value is not found, I need it to return Null. The closest I can seem to get is it is returning #NA. How do I get around this? Example: Formula in cell C1 is =vLookup(A1,G:H,2,False) I've tried incorportating If Conditions, =If(Match; =if(vLookup ......nothing is working. Thanks for your help! You can't return a null, buy you can return a null string... =IF(ISNA(MATCH(A1,G:G,FALSE)),"",VLOOKUP(A1,G:H,2,FALSE)) Or, in XL07 =IFERROR(VLOOKUP(...),"") In article <1177081611.084201.120520@b75g2000hs...

=IF(ISERROR(VLOOKUP(A3,contacts!B:I,5,FALSE)), " ", VLOOKUP(A3,con
Hello, I'm using this vlookup, however it's returning a blank if the cell it's referencing is blank. how do I write this formula so the return of a blank cell is blank? =IF(ISERROR(VLOOKUP(A3,contacts!B:I,5,FALSE)), " ", VLOOKUP(A3,contacts!B:I,5,FALSE)) Thanks You lost us. You just told us the formula is doing exactly what you want ("return of a blank cell is blank"). You'll need to provide a clearer explanation of your problem. Examples are normally the best way. Regards, Fred "Jim" <Jim@discussions.microsoft.com&g...

Data validation for 2 valid values
I want a cell to allow only 2 valid values but not have a drop-down list appear. How do I do that with Data->Validation? Data>validation>allow>list, put the 2 values separated by a comma in the source box, uncheck in cell dropdown -- Regards, Peo Sjoblom "Hall" <hall@garp.org> wrote in message news:ujuB%23w52DHA.2000@TK2MSFTNGP11.phx.gbl... > I want a cell to allow only 2 valid values but not have a drop-down list > appear. > > How do I do that with Data->Validation? > > Using a list, as Peo suggested, will force users to match the ...