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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
