Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Provide a match from 2 cells to give a result from another
Hi I hope you can help with this is should be simple but I can not get it to
work. I have a worksheet set out like this... Sheet 1 layout A - B - C - 1 Fixture - Mode - DMX Chan - (colum headings) 2 Robe - Mode 1 - "Value" 3 Robe - Mode 2 - "Value" 4 Robe - Mode 4 - "Value" 5 Vari - Mode 3 - "Value" Colums A & B are both drop down menus using text validation. The source is in sheet 3. We have 40 fixture types each with between 2 and 5 mode settings, I want the user to be able to select a fixture and what mode setting they want it to be used in and it will then return a value from sheet 2. Sheet 2 - Layout A - B - C - 1 Fixture - Mode - DMX Chan - (colum headings) 2 Robe - Mode 1 - 20 3 Robe - Mode 2 - 22 4 Robe - Mode 3 - 33 5 Robe - Mode 4 - 45 2 Robe - Mode 5 - 54 3 Vari - Mode 1 - 24 4 Vari - Mode 2 - 28 5 Vari - Mode 3 - 36 Etc.... I have this speadsheet doing many other tasks but I can make the rest work,I just can not get excel to see if cells A and B match and return a value from sheet 2. I would also like to have all of the rows blank unless data is in colum A, so that i do not have pages of formular errors. idealy if a copy row above to next free row feature is avalible it would be perfect as I will not have to copy the formulars throughout the sheet. I hope that this makes sense. Many thanks in advance. Lee |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Provide a match from 2 cells to give a result from another
leefarrell wrote: Hi I hope you can help with this is should be simple but I can not get it to work. I have a worksheet set out like this... Sheet 1 layout A - B - C - 1 Fixture - Mode - DMX Chan - (colum headings) 2 Robe - Mode 1 - "Value" 3 Robe - Mode 2 - "Value" 4 Robe - Mode 4 - "Value" 5 Vari - Mode 3 - "Value" Colums A & B are both drop down menus using text validation. The source is in sheet 3. We have 40 fixture types each with between 2 and 5 mode settings, I want the user to be able to select a fixture and what mode setting they want it to be used in and it will then return a value from sheet 2. Sheet 2 - Layout A - B - C - 1 Fixture - Mode - DMX Chan - (colum headings) 2 Robe - Mode 1 - 20 3 Robe - Mode 2 - 22 4 Robe - Mode 3 - 33 5 Robe - Mode 4 - 45 2 Robe - Mode 5 - 54 3 Vari - Mode 1 - 24 4 Vari - Mode 2 - 28 5 Vari - Mode 3 - 36 Etc.... I have this speadsheet doing many other tasks but I can make the rest work,I just can not get excel to see if cells A and B match and return a value from sheet 2. I would also like to have all of the rows blank unless data is in colum A, so that i do not have pages of formular errors. idealy if a copy row above to next free row feature is avalible it would be perfect as I will not have to copy the formulars throughout the sheet. I hope that this makes sense. Many thanks in advance. Lee Hi Lee On sheet 2, put the values from columns A and B in column A (i.e.A2 is "RobeMode1"). Delete column B. In sheet 1, cell C2 enter =if(or(A2="",B2=""),"",Vlookup(A2&B2,Sheet2!A2:B10 ,2,FALSE)) Adjust the lookup range (Sheet2!A2:B10) as required. Regards Steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Provide a match from 2 cells to give a result from another
Thats fab many thanks, I need the user to be able to add many rows all with
the same formulars but differant products an modes, When i copy the formular I have to change the "lookup Range" Is their a quick way to do this or do I have to chage each row individulay? Many Thanks Scoops wrote: Hi I hope you can help with this is should be simple but I can not get it to work. [quoted text clipped - 42 lines] Lee Hi Lee On sheet 2, put the values from columns A and B in column A (i.e.A2 is "RobeMode1"). Delete column B. In sheet 1, cell C2 enter =if(or(A2="",B2=""),"",Vlookup(A2&B2,Sheet2!A2:B1 0,2,FALSE)) Adjust the lookup range (Sheet2!A2:B10) as required. Regards Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Provide a match from 2 cells to give a result from another
One way ..
In Sheet1, Put in C2's formula bar, then array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(OR(A2="",B2=""),"",INDEX(Sheet2!$C$2:$C$100,MA TCH(1,(Sheet2!$A$2:$A$100=A2)*(Sheet2!$B$2:$B$100= B2),0))) Copy C2 down as far as required. Adapt the ranges to suit the actual extent of data in Sheet2 before copying C2 down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "leefarrell" wrote: Hi I hope you can help with this is should be simple but I can not get it to work. I have a worksheet set out like this... Sheet 1 layout A - B - C - 1 Fixture - Mode - DMX Chan - (colum headings) 2 Robe - Mode 1 - "Value" 3 Robe - Mode 2 - "Value" 4 Robe - Mode 4 - "Value" 5 Vari - Mode 3 - "Value" Colums A & B are both drop down menus using text validation. The source is in sheet 3. We have 40 fixture types each with between 2 and 5 mode settings, I want the user to be able to select a fixture and what mode setting they want it to be used in and it will then return a value from sheet 2. Sheet 2 - Layout A - B - C - 1 Fixture - Mode - DMX Chan - (colum headings) 2 Robe - Mode 1 - 20 3 Robe - Mode 2 - 22 4 Robe - Mode 3 - 33 5 Robe - Mode 4 - 45 2 Robe - Mode 5 - 54 3 Vari - Mode 1 - 24 4 Vari - Mode 2 - 28 5 Vari - Mode 3 - 36 Etc.... I have this speadsheet doing many other tasks but I can make the rest work,I just can not get excel to see if cells A and B match and return a value from sheet 2. I would also like to have all of the rows blank unless data is in colum A, so that i do not have pages of formular errors. idealy if a copy row above to next free row feature is avalible it would be perfect as I will not have to copy the formulars throughout the sheet. I hope that this makes sense. Many thanks in advance. Lee |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Provide a match from 2 cells to give a result from another
leefarrell wrote: Thats fab many thanks, I need the user to be able to add many rows all with the same formulars but differant products an modes, When i copy the formular I have to change the "lookup Range" Is their a quick way to do this or do I have to chage each row individulay? Many Thanks Scoops wrote: Hi I hope you can help with this is should be simple but I can not get it to work. [quoted text clipped - 42 lines] Lee Hi Lee On sheet 2, put the values from columns A and B in column A (i.e.A2 is "RobeMode1"). Delete column B. In sheet 1, cell C2 enter =if(or(A2="",B2=""),"",Vlookup(A2&B2,Sheet2!A2:B1 0,2,FALSE)) Adjust the lookup range (Sheet2!A2:B10) as required. Regards Steve Sorry Lee, my mistake for "live" typing, try: =if(or(A2="",B2=""),"",Vlookup(A2&B2,Sheet2!$A$2:$ B$10,2,FALSE)) The $ symbol fixes the range reference Regards Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
How to Convert Figures into Text in Excel | Excel Worksheet Functions | |||
Conversion of Numercials to Figure | Excel Worksheet Functions | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
EXCEL:NUMBER TO GREEK WORDS | Excel Worksheet Functions |