Building A Lookup Table With 3 Variables/3 Answers

Mar 25, 2009

I'm trying to figure out how best to build a table to do:

IF A1="yes", "no", "na" and
IF B1="yes", "no","na" and
IF C1="yes","no","na" then
"AT" or "IC" or "VE"

So, I need a table that can look up responses to three questions, then lookup the corresponding answer (AT, IC or VE). Some of the combinations of anwers will yield the same anwer (i.e: yes, yes, yes=AT and yes,yes,no=also AT). I'm trying to come up with the most probable answer by evaluating these three questions; Thus; 10 combinations will yield an answer of "AT", 10 combo will yield "IC" and 7 combo will yield "VE". I've done this with 2 variables, but can't seem to get it to work with three...

ADVERTISEMENT

Building A Lookup

May 10, 2006

I need to build a formula that looks up a vertical value and a horizontal value and retrieves the value where those two values meet

for example

REGION
Grade ST LOUIS MILWAUKEE SOUTH DAKOTA

C10 30,000 35000 45000
C11 27500 25000 30000
C12 22000 60000 23500

I need the formula to retrieve that value for grade C11 and in milwaukee which is 25,000

View 4 Replies View Related

Lookup Formula To Pull Multiple Answers To A Row

Jul 2, 2014

Instead of trying to explain my challenge, the attached workbook should be self explanatory. My answer is surrounded by the box. I need a formula that would automatically provide this output.

Lookup Scenario.xlsx‎

View 9 Replies View Related

Building Stats From Pivot Table

Jan 10, 2014

I am new to pivot tables and charts and to build.... I heard that the best is to use pivot tables but no clue how to do it I have tried but no luck....

How can I see the following information in chart?

-Non con close out time by person- so who is failing to close out on time in this case is completion date
-Frequency of reports by area/ shift
-Number of reports by month, split into critical/major/minor
-Main reasons for reports so trend of type of record

View 3 Replies View Related

Sum A Table Without Putting Individual Answers On Each Row

Apr 22, 2009

Would like to sum a table without putting individual answers on each row and summing column. The table can consists of 100's of rows but only 3 columns.

lbsratehrs
Item 1100.10
Item 250.525
Item 3200.2550

Total Lbs/hr0.2 (answer)

Find lbs/hr for each item and sum all items lbs/hr
(lbs*rate)/hrs

View 3 Replies View Related

Pivot Table - Displaying Count Of Data In A Column With Y/N Answers

Dec 16, 2013

I am new to Pivot Tables and I am having difficulty displaying a count of data in a column with Y/N answers.

Previously I would have undertaken this using the SumProduct function in a standard table.

I attach an example workbook with my data, what I want it to look like and the pivot I have created.

Book1.xlsx‎

View 4 Replies View Related

Lookup With 2 Variables Returning A Third

Apr 8, 2014

So I'm trying to populated a price from a matrix.

I have the headings Rows as colours and the and the columns as quantity, actual matrix data is value.

Trying to work out how to look up the colour and the quantity and return the value.

# R B Y G
1 2 3 4 5
2 3 4 5 6
3 4 5 6 7
4 5 6 7 8
5 6 7 8 9

So
2 B are 4
3 Y are 6

View 6 Replies View Related

Lookup Table (adjust Which Column The Lookup Function Refers To)

Jun 12, 2009

I am trying to perform a lookup (vlookup) function in a cell in excel and wish to have the range as a variable, so that I can adjust which column the lookup function refers to.

View 4 Replies View Related

Lookup Function To Lookup For Data In Another Table

Jul 29, 2008

I am trying to use lookup function to lookup for data in another table (we call it table A). Unfortunately, whenever the code is not in the table A, Excel will return the data from the previous row.... is there any possible way to prevent this... in another word, if the code does not exist in the table A, I want Excel to return 0 or some other figures.

View 9 Replies View Related

Correlation From Pivot Table On 3 Variables?

Dec 6, 2013

see attached pivot table... I believe that there is a statistical relationship between column B & C (together, i.e. their combined effect) upon column D. i.e. (B & C) - have some relationship to effect D.

(1) What formula would I use to measure this correlation?

View 3 Replies View Related

Vlookup, 9 Different Variables, 9 Different Table Array

Jun 11, 2009

I need to be able to do a look up in 9 different table arrays using 9 different ranges.

I have attached a worksheet to help explain. My problem is that I can't have 9 IF statements in one formula.

I am not sure how to make this smaller or work.

View 14 Replies View Related

Locate Value From Table, From Two Predefined Variables?

Oct 12, 2009

Struggling a bit on this one! I have tried the index function, but doesnt quite work how I need it. Consider the attachment as an example:

I have three cells, 2 of which are drop down lists, and the third is simply the result from the input variables. The yellow and green section contains the info for my drop down lists, and the orange contains the data range. Trouble is Index is looking at the contents of my cells, and not the corresponding cell address. So lets say I have cells F5 and A8, the answer I would be looking for is 23.7, but because the contents of my input cells are 185, and 5, its looking for column 185 and row 5, when I want it to look for the column/row that relates to these values, i.e. column 6, row 8.

View 5 Replies View Related

Lookup Up Entire Table Not Just Column In Table Like Vlookup Does

Jun 5, 2014

I'm trying to see if you can look up multi columns for a number and when it finds it return back to one column and return that data?

I am trying to sort out territories for a state I work on; the territories are divided up by zip codes.

i.e.
A B C D

territory 1 12345 54321 11222
territory 2 22222 33333
territory 3 44444 55555

and what I want is to say put a formula in cell B3 on Sheet 1 pointing to cell C3 that has zip code 33333. I want it to then take that and look in Sheet 2 and search A1:D3 and when it finds 33333, return "territory 2" back to cell B3 on Sheet 1.

View 3 Replies View Related

Two Variable Data Table - Both Variables In Rows?

Nov 29, 2013

I'm interested in creating a two variable data table with the two variables in the top two rows.

I'm aware that two variable data tables are usually done in an array with variables in the top row and leftmost column. However, as there are multiple outputs to the variations that I want to make it makes sense to have the variations in the two top rows.

View 8 Replies View Related

Solution/altenative To Data Table Using >2 Variables

Feb 9, 2010

Is there a way to test out a formula using Excel that has more than 2 variables? (If only 2 variables, I would use a simple data table). The formula I am testing has 50X50X20 variables (and as a subset, each variable has 2500 lines of assumptions!). I don't need to see all the results, only the result that produces the highest result. For simplicity (if someone has an answer) let's assume 3 variables are A1, A2 and A3. Formula is in A4 and formula is A1+A2+A3. Assuming A1 can be 1 to 5 and A2 same , A3 same, the long hand calc is for me to run a data table using variables in A1 and A2 the fixing A3 (as 1)..note maximum result....then run data table again using variables in A1 and A2 and changing fixed value in A3 to the number 2...and so on and so on....in this simple example I know the max result would be 15, but much more difficult in my spreadsheet test.

View 9 Replies View Related

Create Table Of Dimensions With Multiples Of Variables

Oct 11, 2007

I am trying to create a spreadsheet containing a table of dimensions that are multiples of up to six loadable variables. I work for a stainless steel tank manufacturer. The body of tanks are formed from coil stock of varying widths. Typically these might be 1200mm, 1500mm, 2000mm and possibly 1800mm as well as the imperial equivalents. This could mean up to eight variables but in reality all are unlikely at any one given time.

We are told the volume that the tank is required to contain and a target preferred diameter. The theoretical height is therefore derived from this information. But now comes the tricky bit. It is desirable, to minimise cost of welding by minimising the number of strakes or wrappers. Can anyone give me a formula or whatever that can be used to do this and hopefully generate a table of close heights up to say 40 meters. Thus allowing an operator to select from say five presented options.

I have a table in a spreadsheet that the three principal widths of 1200mm 1500mm and 2000mm generate. It was created for me a while ago by a gentleman working at the Amsterdam Museum that Im happy to send for perusal. The trouble is he never told me how he did it and no matter how hard I try, I cannot discover the method or formula. The first column gives the total combined height, the second the number of 1200mm wrappers, the third the number of 1500mm and the last the number of 2000mm. It is that simple

View 4 Replies View Related

Lookup Table That Will Lookup The Tuition

May 1, 2007

I need to place a lookup table in a work book and I'm not sure how to do it.Below is what I specifically need in my workbook.

c. The workbook will need a lookup table that will lookup the tuition, clothing
and entertainment figures depending on the selection of college, and will
ensure that only the colleges on the list are selectable. That is, the
worksheet will not allow the user to enter another college not in the list.

The lookup list must be on a worksheet by itself at the end of the workbook.

View 13 Replies View Related

Copy Table Values To Array Variables And Use Them In Another Sheet?

Nov 20, 2012

I have a query about using Array Variables in excel VBA. I have a set of lookup tables and a main data table. The data table will be downloaded everyday. I need to replace the ID's in the main table with actual data from the Lookup tables.

e.g.
Main Data Table
Color Operator
005--325 005
004--326 004
003--327
001--328
002--322

Lookup Color
001 - Red
002 - Blue
003 - Green
004 - Violet
005 - White

Lookup Operator Table
323 - Operator 1
324 - Operator 2
325 - Operator 3
326 - Operator 4
327 - Operator 5
328 - Operator 6

I have a huge amount of data in the main table. So I can't use a lookup formula for automation. Rather I would like to use VBA to create array variables, scan and copy the lookup data into the array and replace the ID's in the main table.

Only that I am unable to achieve this using arrays. I am very basic in executing code related to Arrays.

View 2 Replies View Related

Code To Generate Data Table Based On Variables

Aug 26, 2008

I was trying to use the VBA code to generate a data table:

Private Sub createDataTable(WS As Worksheet, initialRow As Integer, numCol As Integer, numRows As Integer)
Dim initialCell As Range 'specify the upper left cell
Dim RefCell As Range 'the reference cell on the caculator sheet

'activate source sheet
ActiveWorkbook.Sheets("Calculator").Activate
Set RefCell = ActiveSheet.Cells(2, 3)
WS.Activate
Set initialCell = ActiveSheet.Cells(initialRow, 1)
initialCell.offset(numRows, numCol).Select
Selection.Table ColumnInput:=RefCell
'the calculation should be automatic, if not, then calculate
WS.Calculate
End Sub

and it kept giving me an input celll not valid error on this line: Selection.Table ColumnInput:=RefCell the thing is, I have defined RefCell as a range object, so it should be OK rite?

View 4 Replies View Related

Table Lookup (search On A 2 Dimensional Table)

Aug 4, 2009

I am attempting to do a search on a 2 dimensional table, based on Product Code/Month. Is there a function, addin or a macro that will allow me to do this? All I can come up with is a messy combination of VLOOKUP/HLOOKUP/COLUMN.

View 2 Replies View Related

Copy Input Data To Output Table Based On Variables

Mar 20, 2008

For what I'd like to do, the attached workbook actually represents 3 worksheet. one input sheet and two output sheets (output1 and output2)

I plan to use command button " CommandButton1" on input sheet to start the calculations

Here's what I want to do after pressing the " CommandButton1" :

1. start with case 1 and find the value of Input A to Input J from input table using Vlookup and paste those values to respective palceholder for inputs in blue colour area above the input table for each input.

2.for empty cell in the input table paste 0 (zero)

3. after this will give results for case 1 inputs on sheets "output1" and "output2" on basis of formulas used for each output

4.then copy the results for case 1 inputs from sheet "output1" and "output2" and paste them on "input sheet " in Output table (its below Input Table) next to case1

5.Do this for all cases (in actual workbook there will be more than 100 cases)

View 9 Replies View Related

Locating Important Columns In A Table Of Raw Data - Setting Variables Using A Loop

Feb 22, 2012

I need to be able to locate some important columns in a table of raw data (the column locations are not fixed).

I would like to identify the locations (based on the heading values in Row 1) and store them as Public variables. The Match function works fine for this, however I'd like to make a simple loop to set these variables (opposed to repeating the function for each).

In the example below, I can't figure out how to reference "List1(Count)" as the name of the variable I'm trying to set.

Code:

Public Field1 As Long, Field2 As Long, Field3 As Long 'The column numbers will be stored here
Sub FindFields()
Dim List1(3), List2(3)
Dim Count As Long
'Public variables (declared above)

[Code] ......

So after running FindFields(), the Test1() macro should give "1 - 2 - 3" (for example) as the locations of the fields in Sheet1. But currently this doesn't work.

View 2 Replies View Related

Lookup Table In VBA Rather Than VLookup Table?

Nov 23, 2011

I get a file every day that contains online orders for a client. They have made the state and country fields on the order form free text, so sometimes the State field has CA, sometimes California, sometimes even Cal or Cali. Oregon may be represented as OR, Oregon, or Ore (and one time even Orygon.)

The country field may have US, USA, United States and sometimes United States of America.

Well, they need this data imported to their system with the states fully spelled out: Oregon, California. And the country spelled out as well: United States, Canada.

Right now I have a lookup tables worksheet that I just have a macro run a vlookup on. But I'd like to know if there is a way I could just put it all in the code, without the external tables.

i.e. some code that, in English, would basically say, "If the value of any cell in column K is among these values, replace it with those values, otherwise just leave it as it is". Could I do that while containing the two sets of values right in the code rather than on a worksheet?

I really just want to replace the standard abbreviated state/country codes with the full text, and if the value does contain Cali or Ore, let them manually make the change. Hopefully that will finally nudge them to just make the online order form utilize a drop-down list of states and countries, rather than free text.

View 1 Replies View Related

Building A Chart

Oct 8, 2007

I am building a chart from a pivot table and the data changes weekly therefore my chart changes weekly and I want to have the same colors on my graphs from week to week. I created this code to keep everything the same. Problem is when some of the data is not there for a given week (say no calls from a certain city) a corresponding data set doesnt get created and the macro wont work. Is there a way to run if the a data set is not available and to keep the colors the same?

ActiveChart.SeriesCollection(3).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With..............

View 9 Replies View Related

Building The Name Of A Variable

Nov 5, 2007

I need to update a Variable I called "Croy70" by making up its name from two cells

Left(Range("L" & i), 4) & Right(Range("J" & i), 2)) = i
Where i=2 and L2 is "Croydon" and J2 is "ABC70"

I apologise for this simple question, but I appear to have left my brain at home this morning and all the VBA help files are missing from the computer I'm using!

View 9 Replies View Related

Building Worksheet

Nov 23, 2009

I can start building.

1. User will first input Medication type in first column. Second Column - Dosage Third Column - Start Date, Fourth Column - End date

There will be multiple entries for the same medication because of dosage change. I can simply get them to insert a new row underneath each medication, and they can then input new dosage, new start/end dates

There will be a cell entry for the form start date, as it will be populated on the main page.

The solution I believe will involve an if statement, with argument being if date is still within range of start/end date, that it will generate text on particular date.

View 9 Replies View Related

If- Returning 2 Answers

Aug 28, 2008

I have a sheet with this:

cell a1 = numbers from 1 to 10 or nothing
cell b1 = TO or OFF depending on cell a1
cell c1 = numbers from 1 to 10 or nothing

Now I have a formula in cell b1 that detects if theres text in cell a1.
IF A1 = nothing, then b1 = off, else b1 = to.

What I want to achieve is to do 2 things on the IF.

If a1 = nothing, then c1 = nothing and b1 = off. else, b1 = to.

how can I do this?

I looked everywhere and no one speaks of double answers on IF.

View 11 Replies View Related

Different Lists For Different Answers

Dec 3, 2008

Cell C7 is a drop down list with 3 possibles (Consumer,Commercial, Public). Depending on which is chosen, I want Cell C10 to provide a different list of possibles (Consumer Products, Commercial Products, Public Products).

I have tried to create a custom IF statement with the list choice & sometimes it refuses & sometimes it gives me the list, but with the formula string as the drop down options.

Is this forumula possible or is it beyond excel's capability?

View 7 Replies View Related

Consolidate Y Answers To One Row

May 16, 2013

I have a spread sheet that looks like the following. I need to take all of the Y answers and make one line.

Client
ProductA
ProductB
ProductC
ProductD
ProductE

ABC Inc
N
Y
N
Y
N

[Code] ......

If any of the Product columns had a Y in the row, the line would get the Y. If not, an N.

View 5 Replies View Related

IF OR Formulas With 7 Answers

Apr 26, 2006

I am trying to achieve the following in one formula.

IF an amount is greater than 0 and less than 500,000 put in 490
IF an amount is greater than 500,000 and less than 1,000,000 put in 1000
IF an amount is greater than 1,000,000 and less than 4,000,000 put in 2205
IF an amount is greater than 4,000,000 and less than 9,000,000 put in 5000
IF an amount is greater than 9,000,000 and less than 10,000,000 put in 6500
IF an amount is greater than 10,000,000 and less than 15,000,000 put in 7500
IF an amount is greater than 15,000,000 put in 9500

View 3 Replies View Related

MAX Multiple Answers

May 8, 2006

here's the formula that I'm using: =INDEX($C$35:$AF$35,MATCH(MAX(C41:AF41),C41:AF41,0 )) where $C$35:$AF$35 contains names of people & C41:AF41 contains #'s. This works great if there isn't a tie. Is there a formula to search the range, find the max # and if there are two answers display both names. ex. the max# is 3 and both joe and sam have 3. it would then show joe / sam or something like that. Is this possible? If not, then if there is a tie for the max #, to have the cell just display tie.

View 4 Replies View Related

Start Day Building Out 365 Days

Sep 21, 2009

I have Sheet 1 with a series of 10 lists. Sheet 2 will display the information on Sheet 1 based on the day. For example, the user will input a start date. Excel should display item 1 in lists 1-10 on day 1 in the respective column. Then item 2 in lists 1-10 on day 2, etc. When it reaches the end of a list it should start at the beginning. Day 1 shold be the start date. Day 2 shold be the next day, etc. I have lists 1-10 all ready to go, but I am having trouble figuring out how to go about populating the other columns.

View 6 Replies View Related

Building Form With Checkoffs

May 24, 2013

I am building a form with a list of names and check offs next to them. There will be a list of 150 names and 3 check boxes next to each name. I want to be able to create a running total of each check mark. So I have a name with a check mark that marks off SSN, Drivers Licence, drug test At the bottom I want it to give me the total names that have a SS Card, Total that have Drivers Licence and so on.

View 1 Replies View Related

Building A Dynamic Database

May 23, 2006

I have a workbook with 3 worksheets titled Numbers, Letter and NumLtr DB.

The Numbers worksheet is a dynamic listing of numbers in Column A, which also is a Dynamic Named Range using the formula: OFFSET(' Num'!$A$1,0,0, COUNTA('Num'!$A:$A),1)

The Letters worksheet is a dynamic listing of letters in Column A which also is a Dynamic Named Range using the formula: OFFSET('Ltr'!$A$1,0,0,COUNTA('Ltr'!$A:$A),1)

I need a macro to duplicate in column A, on the worksheet titled NumLtr DB, the first number down as many rows as are needed to equal the full count of letters, and then, in column B, list each letter in succession. This process needs to be repeated once for succeeding each number and then stop.

View 9 Replies View Related

Building A Database For Dogs

Sep 14, 2006

We have a number of dogs and information about their sire (male parent), dame (female parent) - and those dogs parents.

In essence something like this:

DOG1: DOGDAD, DOGMOM, DOGDAD's DAD, DOGDAD's MOM, DOGMOM's DAD, DOGMOM's MOM

So...I'm thinking 7 fields for each dog.

I started out with an Excel s/ sheet that looked like this:
Dog Sire Dame SireD SireM DameD DameM
DOG1 DOG2 DOG3 DOG4 DOG5 DOG6 DOG7
DOG8 DOG9 DOG10 DOG11 DOG12 DOG13 DOG14
DOG2 DOG15 DOG16 DOG17 DOG18 DOG19 DOG14

Now here comes the issue....what we are trying to do is to find and exclude any dog (in the first 'Dog' column that share ANY grandparent (SireD, SireM, DameD or DameM) with any other dog from the first column.
(As an example above - DOG8 and DOG2 share the same grandparent (DOG14) and needs to be excluded from the final list).

View 9 Replies View Related

Building A Cash Register

Mar 6, 2007

I want to build a cash register from excel.

I want to put many buttons, but lets say one on A1 that says $2 dollar item and name and lets say this $2 doillar item button is pushed(clicked on), it will add $2 to lets say the total say A15.

Now I can't even get a Macro to add $2 dollars to A15(or any macros to run at all, anybody have code I can copy, what I understand is you create macro name(give it letter, so yo can press letter and Ctrl to start) then start recording, then you do key strokes or mouse? Then press stop recording and you have macro?).

To get buttons to be viewed I have to go to Tools - Macros - script editor, and drag and drop(button on coding) or double click on button tool bar then File - View in Browser - . I get button but can't get it to do anything.

How do you put say A1 in code to A1=A1+5 or make a memory location out of A1. Now you can go to(while A1 is selected cell) "Insert" then "Name" then "Define" and give A1 a "Name". I can't get this name to work in coding(but I can get the name to work in cells like "Name" +5, will add cell 1 and 5, if you do it in different cell than A1).

View 5 Replies View Related

Different Answers In Excel Than Calculator?

Jan 18, 2010

I have created Excel spreasheets for our payroll. The answers on my calculator are sometimes different than my spreadsheet. How can I make them tie?

View 9 Replies View Related

Search And Compile Answers

Oct 25, 2007

I have a workbook filled with many tabs / sheets. Each sheet contains the same structure of information and isn't an important aspect of this question.

I've looked at this topic http://www.mrexcel.com/board2/viewtopic.php?t=16683 and I'm unable to follow it well.

Is there a way to search all the tabs for a word and if it matches to copy the entire row into the search tab / sheet? Specifically for each and every searched match.

It would save my users trying to search each tab individually and possibly miss the data they are actually looking for. Hundreds of people use this reference workbook which stores where all our documentation is stored across numerous servers. Each row is one document and its corresponding storage information.

View 9 Replies View Related

Vlookup And Multiple Answers

Jun 26, 2008

I have a table whereby I Vlookup a different spreadsheet on an order number (in this instance Z011352/001). There are multiple sketch numbers associated to this Z number.

I would like to be able to input the Z number and then obviously the Vlookup pulls up the first sketch number. This works fine.

If I then input the same Z number again it pulls up the same sketch number, but I want it to look in the current table (the one i'm entering the Z number onto), see if the sketch number is already present and if it is pull up the next sketch number in the list on the separeate spreadhseet. (I hope this is clear enough)

My Vlookup code is:

=IF(C1070"",VLOOKUP(C1070,'[CrossingsSchedule.xls]Update Worksheet'!$E$1:$AC$10000,20,FALSE)," ")

View 9 Replies View Related

Mutilpe Answers To Cell

Dec 12, 2008

How would I write out a formula if I currently have this formula/ =COUNTIF(D6:Q6,"a")*8 on cell S6 but I also want to include/ =COUNTIF(D6:Q6,"a-1")*1 and =COUNTIF(D6:Q6,"a-2")*2 and so on till a-7 and i want all this to read on the same cell.

View 9 Replies View Related

Multiple Answers And Message Box

Jan 12, 2009

I have a message box in my spreadsheet. I want it so if the user puts in multiple answers that certain message box appears, if they put in a different set of multiple answers it shows up. For example, the code below is what I am using now. It says that if the user types 5225 in C16 and 3000 in C17 then a specific msg box appears. I want it to say if the user types in 5225 or 5226 or 5227 the same msg box appears but if they type in 5665 a different msg box appears. How would I change this code? ...

View 9 Replies View Related

ADVERTISEMENT