Excel 2010 :: Lookup From Variable Table

Mar 13, 2012

I need to return a value dependent on a few criteria. Type will be selected via a drop down and then it must lookup the same type in the top row and then the value which will be between two values and then return the grade on the far left.

GradeG4M5M6TypeValueGradeA
160016501800M5850CB600700650780900950C7008007809109501100D8009009101040
10001250E90010001040117010501400F100011001170130011001550G>1100>1300>1150

I am using Excel 2010 on Win 7

View 6 Replies


ADVERTISEMENT

Multi-Variable Lookup Table

Jun 12, 2014

I make budgets that have line items that have standard price based on 2 factors: a description, and a number. I want to keep these items in 2 different cells. I also have different clients, and the costs are different. I could simplify so that I can use a VLOOKUP with HLOOKUP and separate the clients but I would prefer not to.

Screen shot 2014-06-12 at 12.36.47 PM.png

View 1 Replies View Related

Excel 2010 :: How To Auto Update PowerPoint Table When New Data Entered Into Table

Jun 12, 2013

I have created a table in Excel 2010 (pls see attached table named post.xlsx).

Then copied the above table into PowerPoint 2010, using "paste link" (I tried to attach the PowerPoint file but the system says "invalid file type" and I cannot attach it).

Question:

I have received income data for another month - the new month is 13 and the corresponding new income is 100.
I typed 13 and 100 into the Excel table post.xlsx and thus extended the table by another column.

Then I went back to PowerPoint slide, then right clicked on the table there, then clicked "update link".

Specific Question:

The newly-typed column in Excel table is not get updated in PowerPoint table.

View 2 Replies View Related

Excel 2010 :: Create Sheet With Table From Pivot Table?

Apr 26, 2012

Recently, the boss showed me a Pivot table & chart, which consists of a list of about 30 user names in the first column. The row headings were the different items they purchased from a vending machine. & when he clicked on any name in the first column, this created a new sheet, renamed with users name, with a small table of results showing what that person purchased.

Problem is, none of us can figure out how to do this. I have created a new Pivot table & chart exactly like the original, but I cannot get the smaller sheet to generate. (Excel 2010)

View 3 Replies View Related

Excel 2010 :: Lookup With Different Columns

Mar 11, 2012

On my "Order" sheet, I enter an account no. that looks up the company name, address, etc on my "Accounts" sheet. Below the address info on the "Accounts" sheet is a list of the salespersons with their email address.

After entering the account no., I want to enter the salespersons name and automatically look up their email address. I can make the lookup function work if I tell the formula which column the salesperson is located in, but am lost how to write the formula to locate the salesperson based upon the column no. that contains their company info.

It's not pretty, but this works.

=IF(F1"",HLOOKUP(F1,ACCOUNTS!A1:Z100,ORDER!G9, FALSE),"")

F1 - contains the account no. entered on the order sheet.

Accounts!A1:Z100 - range containing columns of account info.

ORDER!G9 - This is the row number value I obtained by using a match function to lookup the Salespersons name entered in cell F8, then I add 1 to render the value of the row that contains the email address located directly below the salespersons name.

=(MATCH(F8,ACCOUNTS!B1:B100,FALSE)+1)

Herein lies my issue, the salesperson name will not always be in Column B

I can write another Match to obtain the column no, but then I am using another cell to hold a calculated value for my lookup statement.

ACCOUNTSABCD1CODACCT1ACCT2ACCT323 COMPANY 1COMPANY 2COMPANY 34ADDRESS ADDRESS ADDRESS 5   CITY STATE ZIPCITY STATE ZIPCITY STATE ZIP6TELEPHONETELEPHONETELEPHONE78910TOM JONESJANE DOEMARY SMITH11tjones@company1.comjdoe@company2.commsmith@company3.com12SALESPERSON 2SALESPERSON 213salesperson2@company1.comsalesperson2@company2.com14SALESPERSON 315salesperson3@company1.com
Excel 2010

In words, Find Mary Smiths email address, she works at the company with the account number in cell F1 on the order sheet.

View 6 Replies View Related

Excel 2010 :: Average Function With A Variable

Jan 4, 2014

I am working with Excel 2010 and I have a problem that I can't seem to figure out. I am trying to find the first nonzero cell and begin an average function from that cell and down through 29 rows (a total of 30 rows).

Sub average()

FinalRow = Cells(Rows.Count, 3).End(xlUp).Row
For I = 3 To FinalRow
If Cells(I,3).Value 0 Then
'I use the above to identify the first nonzero cell
ActiveCell.FormulaR1C1 = "average(........)

This is where I am stuck, I don't know how to code the first nonzero (which can change with different data) cell and average rows below it.

View 7 Replies View Related

Excel 2010 :: Lookup For Match Function

Jul 17, 2014

I think I need some version of a lookup for match function. Here is essentially what I need: I have a column of data points. I need to output every position at which the data point changes to a different value (going from top to bottom). See below for an example.

Here is some sample data (with column headers)

Position
Data Pt

1
-7.5

2
-8.0

[Code] .......

Here is the result I need (I want to put a consistent formula into the second column below that returns the results shown in the second column below):

split 1 position
2

split 2 position
4

[Code] ........

Is what I am describing possible? Because I am inserting this into a template, I ideally need to do this outside of any sort of VBA work.

View 2 Replies View Related

Passing Word Variable To Excel In Office 2010

Apr 2, 2013

I'm trying to pass a variable from Word to Excel. Basically I have a Word document with a plain text content control in it. I'll have users populate this field. I know how to create a reference to that content contol in Word VBA that'll tell me what's in that content control (eg.

Code:
MyWordVar = ActiveDocument.ContentControls(1).Range.Text
).

What I can't figure out is how to pass the value of "MyWordVar" to a variable in Excel.

View 4 Replies View Related

Excel 2010 :: Use Variable Instead Of Range In Worksheet Formula

Dec 3, 2013

Code:
=SUMPRODUCT(--(_NamedRng1=NamedRng2),$B$49:$F$49)

I am using the above formula in my code with two Named Ranges

Code:
Set Rng3 = Range("_NamedRng1").Offset(1, 0)

=SUMPRODUCT(--(_NamedRng1=NamedRng2),rng3)
[/CODE]

I want to set the range $B$49:$F$49 in my code and I have tried the above, but it does not work.

I want to allow for the fact my end user may insert rows so do not want to use $B$49:$F$49

View 2 Replies View Related

Excel 2010 :: Macro To Change Excel Formatted Table Name Dependent On Number Of Loops

Jul 9, 2012

Recorded macro. The hope is to insert a excel formatted table a set number of times. I have found a loop code that references a Cell A1 and repeats that amount of times. So if A1= 10. There should be 10 tables inserted. However on the second time there is a fault with the table name. I need the name to change each time the loop is run. ie Table1, Table2, Table3 etc up until the loop stops (A1 contents).

I am using excel for windows 2010. The macro that i have so far is below.

Sub LoopTest()
Dim n
Dim V
Range("A1").Select
V = ActiveCell.Value

[Code] ........

View 1 Replies View Related

Excel 2010 :: Lookup Single Closest Match?

Apr 17, 2013

I am using Excel 2010 and need to create something like a histogram that will have large regions of null values. In other words, my histogram might look like this...

x
x
x x
x x x
__x_________x___________x______
100 250 420

[My laboriously drawn histogram does not display correctly. It is supposed to have 3 x's above 100, 2 x's above 250, and 5 x's above 420]

I have data in the form of:
Value Prob.
100 30%
250 20%
420 50%

I have Excel giving me a histogram that looks like this...
x
x
x x
xxx
xxx
[This histogram also didn't turn out. Again, it is supposed to be 3 vertical x's, 2 vertical x's, and 5 vertical x's]

What I have tried to do is to create a list based on the lowest and highest numbers (e.g., 100 and 420) and split that into equal increments. I then intended to use a VLOOKUP or something to pull back the probability associated with the number in my list nearest the data value I have.

I couldn't get VLOOKUP to work correctly, so was happy when I found the following thread on Ozgrid. [URL]

However, in each of the solutions listed in that thread, I get faulty results. I am attaching a file that shows the errors and what I am trying to get : Example.xlsx

Perhaps there is some easier way to do what I am attempting to do. This architecture makes sense to me, but sometimes the perfect solution doesn't make sense until I see it...

View 5 Replies View Related

Excel 2010 :: Keystroke To Select Variable Name From Dropdown List?

Jan 23, 2013

I've defined a number of variables in my spreadsheet. When I want to use one I type "=variablename" and 'm presented with a list menu.jpg

In the above example, I have several variables starting with the word "Harvard" (a town, not the university :-)

How do I select one of these without having to double click with the mouse.

Up and down arrows allow me to highlight an item but I cannot find a keystroke that allows me to select the highlighted item. Instead I have to lift my hand off the keyboard and use the mouse.

Excel 2010

View 2 Replies View Related

Excel 2010 :: How To Match Worksheet Names Against Global Variable

Jun 10, 2013

I would like to ask the user if when the name the worksheet the same as an already existing spreadsheet tabe if they would like to overwrite it or unload the user form.

I am not sure of two things:

1. how to find the already existing tab?

2. Once I find out how do I programatically delete it, so the code can continue

The code below works with the exception of the last section (trying to achieve the questions stated above).

I am using Excel 2010.

Code:
Private Sub CommandButton1_Click()
If TextBox1.Value = blank Then 'Need name for processing
MsgBox ("Name must not be blank.")
Exit Sub
End If
If Len(TextBox1.Value) > 12 Then

[code]....

View 5 Replies View Related

Excel 2010 :: Search Variable Folder And Insert First JPEG

Jul 17, 2013

I am after tips on creating a macro in Excel 2010 where it will search a list of folder paths in a column on a spreadsheet and insert a jpeg within the sheet from that folder if it exists. If more than one jpeg exists i would like it to insert the first jpeg only. i have found tips where it will insert images if you know the filename, however I don't have this luxury as file naming routines vary from folder to folder the only constant is that the file is a .jpg format.

View 1 Replies View Related

Excel 2010 :: Lookup Formula With 2 Criteria To Build Schedule

May 6, 2014

I have a schedule that covers 3 shifts for the entire month. I have another sheet that is used for crew sign in that I want to auto populate with all the people on that shift for that day.

I need to be able to use 2 different cells on the sign in sheet for reference for the formula (one for the day, and the other for the shift. ex. Day 3rd, shift D). I'm wanting to use an array formula to accomplish this. I've uploaded a stripped schedule as this is for the military and can't have any data on it that pertains to what or who it's for.

BTW our current method is to due it manually for every shift for every day that wastes about 12-16 man hours every week. I have excel 2010 at work currently. Access is out of the question and Macro's/vba may or may not work as security is always being increased on our systems so either a single or multiple formula is the route I'm looking to do that will work the the next few years I hope.

View 6 Replies View Related

Excel 2010 :: ODBC Connection String With Multiple Variable (WHERE) Requirements

Dec 29, 2013

With VBA in Excel 2010 I am connecting to a SQL server by using ODBC.

This is a two part process:

Part one:I have created a connection string that gets me the following data: A, B, C, D, E from sheet tpoPurchOrder Where B is equal to "1" And Where C is equal to a changing field under Sheets("Macros").Range("B2")

Here is part one

Code:

Sub Part1()
Sheets("Open PO by Vendor").Select
Sheets("Open PO by Vendor").Cells.Clear
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=Connection;Description=Description;UID=USER;PWD=PASSWORD;APP=Microsoft Office 2010;WSID=Workstation;DATABASE=Database" _
, Destination:=Range("$A$1")).QueryTable

[code]...

Part one works perfectly.

Part two is where I have problems.Part two is a little different because I have the same connection, but what changes is the table that I am looking at "tpoPOLine" instead of "tpoPurchOrder" and the where is now going to have a variable number of commands.

I want part two to be depend on part one. Under part one I want the result from column E to be a where statement in part two.

As follows:

In particular: *E3 from Part 1*, etc.

Code:
"SELECT tpoPOLine.Status, tpoPOLine.POKey, tpoPOLine.ItemKey, tpoPOLine.POLineNo, tpoPOLine.UnitCost, tpoPOLine.ExtAmt" & Chr(13) & "" & Chr(10) & "FROM mas500_DII_app.dbo.tpoPOLine tpoPOLine" & Chr(13) & "" & Chr(10) & "WHERE (tpoPOLine.POKey=*E2 from Part 1*) OR (" _
, _
"tpoPOLine.POKey=*E3 from Part 1* ) OR (tpoPOLine.POKey=*E4 from Part 1*)" & Chr(13) & "" & Chr(10) & "ORDER BY tpoPOLine.POKey" _
)

Now my problem is that sometimes the E column from part one ends up being 1 row, sometimes it ends up being 50 rows. I would like the code to change accordingly.

1. Pull everything from the SQL server filter once in excel. This is not as efficient as the database has ~300,000 rows in the tpoPOline table and would take a lot longer then needed.

2. Create a nested if table and

Change

Code:
WHERE (tpoPOLine.POKey=*E2 from Part 1*) OR (tpoPOLine.POKey=*E3 from Part 1* ) OR (tpoPOLine.POKey=*E4 from Part 1*)"

to the value of that nested if table

The nested if table would be something like =if(isblank(E2),"E1",if(isblank(E3)... etc for ~50 rows. I know the syntax is incorrect but you get my point.

3. Rerun the query for each value in column E. That would require the connection to happen ~50 times which would not be that great, as well as I would have to copy and paste the data after each run as the tables cannot overlap.

What I am looking for is a way to run this only once, on one sheet, without writing a nested if table with 50 if's.

View 5 Replies View Related

Excel 2010 :: Reduce Many (and Variable) Spaces Between Text Strings With One Space Only

Jun 12, 2014

is there a way to reduce spaces between text stings to one space only when there are many spaces? To make it worse, the number of spaces between the text strings vary. I am using Excel 2010.

View 9 Replies View Related

Excel 2010 :: Price Lookup Based On Width X Drop Values

Mar 14, 2012

I'm trying to create a excel sheet which will automatically return a price based on a given width and drop value. Currently, I calculate the price manually by looking in a price book which has plenty of rows and columns and prices. I want to simplify this by simply entering the dimensions so it automatically calculates price based on the dimensions entered.

Below is the start of my worksheet. If I choose the exact sizes shown on the table, it will return a price, however if I choose a size that is not listed, I get an #N/A. Eg. If I choose 780 x 1500, it will return the price $179, but if I choose for instance 775 x 1490, it only returns #N/A, when I want it to still return the price $179.

My formula in K3 is
=IF(AND($I$3>0,$J$3>0),INDEX($A$3:$F$8,MATCh(J3,$A$3:$A$8,0),MATCH(I3,$A$3:$F$3,0)))

Sheet1ABCDEFGHIJK12ItemWidthHeightPrice
3mm6307809301080123019751821#N/A
49001581681791891992512001621741851952083615001661791912032167
18001701851972102228210017418920321623192400179193208224239102700
18520121823324911Excel 2010Worksheet FormulasCellFormulaK3=IF(AND($I$3>0,$J$3>0),INDEX($A$3:$F$8,MATCH(J3,$A$3:$A$8,0),MATCH(I3,$A$3:$F$3,0)))

View 4 Replies View Related

Double Lookup (lookup Variable Row And A Variable Column)

Mar 27, 2009

I have a file that I would like to lookup variable row and a variable column. I have tired vlookup and hlookup but these do not work because you have to specify a given column or row versus having that column or row be variable. Is there a way to do this.

Think of a set of times tables. I would like to input 8 and 9 and get 72 as an output. How could I go about doing this?

Or in the attached file, I would like to say A and 15 and get A15.

View 3 Replies View Related

Excel 2010 :: Pivot Table Reference Is Not Valid When Moving Data And Pivot Table Together?

Mar 19, 2013

On a worksheet, I created:

- a list of data
- a pivottable based on these data

When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.

This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.

View 3 Replies View Related

Lookup Values From A - Z Table In Excel?

Mar 12, 2013

I have the z-table imported into excel, How i can look-up values from the table to 2 d.p

For example, to lookup 0.49 would be the intersection of 0.40 down the rows and 0.09 across the columns, i.e a value of 0.6879

anoda example would be 1.26 which would be 1.20 down the rows intersecting with 0.06 across the columns and it gives a value of 0.8962

I have tried some index-match but hasnt worked out fine, i know excel's normsdist function can do this but i need to get it done this way

0
0.01
0.02
0.03
0.04
0.05
0.06
0.07
0.08
0.09

[code]....

View 2 Replies View Related

Excel 2010 :: Pivot Table Grouping

Apr 28, 2014

I had a workbook in Excel 2003 that i just moved to 2010. In the 2003 file I had 2 pivot tables, one each on a worksheet, looking at the same data, just grouped differently. One yearly, one quarterly.

Now in the 2010 workbook whenever I change the grouping on pivot table, the other one changes also. It's like they are linked together or something.

View 3 Replies View Related

Excel 2010 :: Find Value In Another Table And Return Value From Same Row?

Dec 14, 2012

Excel 2010. I have two very large tables of data:

Table 1 is a long list of unique items in no particular order, each of which belongs to a unique group of items, for example:

ItemsGroup ID
apple
pear
grape
fig
banana
guava
peach
mango

Table 2 lists all the Group ID numbers and in the same row as each Group ID number, shows the names of all the items belonging to that group, one item in each column. Different Groups have different numbers of items in them. For example:

Group IDItem1 Item2 Item3 etc.
214applepeargrape
33fig
7bananaguava
59peachmango

Is there a formula I can use in the second column of Table 1 to look for each item in Table 2 and tell me what group it is in? For example for "apple" or "pear" or "grape" it would look in Table 2 and return the Group ID number "214".

View 4 Replies View Related

Excel 2010 :: Copy Data From One Table To Another

Jun 9, 2013

I have two spread sheets that I 'converted' into tables. I need to combine them into a single sheet. When I attempt to copy and paste from one to the other the formatting and formulas do not carry 'down' as they do when I manually enter a new line or record.

Is there a way to copy multiple rows of data from one tabled sheet to another tabled sheet?

View 12 Replies View Related

Excel 2010 :: VBA - How To Convert A Range To A Table

Jan 27, 2014

I am relatively new to VBA and am trying to convert a range of data to a table in the same sheet. I receive the following message when I try to run the code as shown below:

"The worksheet for the table data must be the same sheet as the table being created." The code stops on the third line of the code.

Sheets("Data Forwards").Select
ActiveSheet.Range("$A$1:$U$1000").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$U$1000"), , xlYes).Name = _
"Table1"
ActiveSheet.Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium2"

I can see that the range is highlighted in the sheet before the code breaks.

View 3 Replies View Related

Excel 2010 :: VBA Selecting Only Certain Columns In A Table

Sep 10, 2012

I have a table in columns C:J, starting in row 7. I have a macro that selects and copies the whole table (after I filter the table) and pastes to another sheet. Recently I've realized that I can only copy/paste C:I or it will mess up my formulas during the process.

This is the code I have right now, which works beautifully for the whole table:

Code:
ActiveSheet.ListObjects("Table36").Range.AutoFilter Field:=6, Criteria1:= _
"=Alan", Operator:=xlOr, Criteria2:="="
Range(ActiveSheet.ListObjects(1)).Select
Selection.Copy

What I need is to know how to copy everything in the table except column J. I can't just copy a certain range cells because the number of rows is variable. There has to be a way to modify the ActiveSheet.ListObjects("Table36") right?

Using Excel 2010.

View 2 Replies View Related

Excel 2010 :: Filtering Pivot Table?

Sep 13, 2012

I have a pivot table in 2010- is there a way to filter the data using an external reference from the pivot table? I'd like to put the value in another cell and have the pivot update automatically when I type a new value in that cell.

View 2 Replies View Related

Excel 2010 :: Randomize Data In Table On F9 Only

Jan 10, 2013

I have an Excel table with 7 columns. I have used a formula to randomize the data in each column, and it works fine, accept that it resorts/re-randomizes the contents of the table every time data is entered anywhere in the workbook. I would like the data to be sorted/randomized only once, and then only again upon pressing F9, since I have other spreadsheet which reference the data in the table.

This is the formula I use to sort/randomize the data, and I am using Excel 2010 on a PC.

=VLOOKUP(SMALL($C$2:$C$20,B2),($C$2:D20),2,FALSE)

Column B = numbered 1 - 20
Column C = =RAND()
Column D = last of names
Column E = sorted/randomized list of names.

View 4 Replies View Related

Excel 2010 :: Pivot Table Sorting With VBA?

Apr 10, 2013

Using Excel 2010

I have a workbook with multiple sheets.Sheet1 is named "UPC" and is a giant database for my workbook containing 80,000+ rows and 12 columns.

Sheet2 is named "Scan" and uses a bunch of index-match formulas. Basically, you scan a barcode and it auto-populates across the row all the information it pulls with that particular UPC from the "UPC" sheet. One of my columns, "QTY Scanned", in the "UPC" sheet has a countif formula to keep track of how many times that item was scanned on the "Scan" sheet.

Sheet3 is named "PSlip" and has a big button on it that you press and multiple macros go into overdrive. First macro takes all the rows from "UPC" with a quantity greater than 0 in the "QTY Scanned" column and copies it to the "PSlip" sheet.

Second macro takes that data and makes a pivot table.This is all clothing, pants, shirts, jackets, etc...When my pivot table is created, my Column Label is Size. When the pivot table is created, it automatically sorts it in ascending order, so it goes from numbers to letters.

This is great for pants because the size is in reference to a waist size; 24,25,26,27, etc...

HOWEVER, shirts, jackets, and other tops are not in a numeric size: they are in a Text format. XS, S, M, L, etc...

Sometimes we only scan pants for an order, sometimes only tops, sometimes both.

Is there a way for the pivot table to recognize when tops are in included and automatically sort the sizes?

I still want the numeric values at the beginning, but once the text starts it automatically sorts in this order:

XXS, XS, S, M, L, XL, XXL, MT, LT, XLT, XXLT, 1X, 2X, 3X, 4X, 5X

View 2 Replies View Related

Excel 2010 :: Create Summary Table?

Nov 18, 2013

Code:
Date Jan-14..........Jun-14............Dec-14...........Jan-15..........Jun-15............Dec-15
Measure1
Measure2
Measure3

Code:
Date 2014 2015 2016
Measure1
Measure2
Measure3

I have two tables, examples above In the first table, Jan-14 is a dropdown value that updates all the values to the right by a month increment when a specific date is selected

I then have some code that populates the cells which have 2014,2015,2016 in them.

What I need to do next is populate the 2nd table with values based on the date ranges in the above table.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved