Microsoft Access VLOOKUP from an excel fiile?? PLEASE READ?
544272
File.. Get External Data.. Link Tables .. browse to the xls file    would set up a permanent link to the excel data and give the spreadsheet a kind of table name. Then it can be queried. You want to use several columns over time and you can use an 'Append query' to append data to a table; but experiment with a crosstab query. In that way, your Access table would always have 2 fields plus maybe a ColumnNumber field where you could have a default value of 1 the first week, 2 the next week, etc.. A crosstab query would present that data as several several columns (one for each ColumnNumber value). When your Access skills increase, you might be very glad that you used a small number of fields because it makes the querying of the data simpler to maintain, in my opinion.\nAs for VLOOKUP, you can research "query table joins" and see how a query in design view lets you choose the field which 2 tables have in common. One table is really the excel spreadsheet presented as if it is a table; the other table is the real access table.
631605
Try emailing me the file with a little more information.  I have been doing auto-filtering and formulas for many years and should be able to get you the answer you are looking for.
292988
Depends on the file that needs to be imported, and how you need to import it.  Enterprise manger comes with an import wizard that you can use to import information.  sometimes it's easier to just import the whole thing and delete what you don't need/want.\n\nanother way would be to make the file into a CVS and import that into access, then by using a linked table, export the table to SQL.
215288
There is another way to do this. Assuming your date is in cell A1;\n\nuse the formula =MOD("A1",7)\n\nThe result will come back as follows\n\n1 | Sunday\n2 | Monday\n3 | Tuesday\n4 | Wednesday\n5 | Thursday\n6 | Friday\n7 | Saturday\n\nIf you use it in conjunction with a VLOOKUP you can display the actual day.\n\nso in all \n\n=VLOOKUP(MOD("A1",7)  ,DayDataRange,2,FALSE)\n\n[I've named the lookup range DayDataRange]
750744
Why use VBA when you can use Excel formulas\nWe can do it in two ways\n1- The Macro solution\n2- The Formula solution\n\nI recommand you to check the second option since you are not very good in VBA.\nSo\n\nIf your Book1 cells are A1:F4, and your Book2 cells are A1:C4 in Sheet1, then paste this in cell C2 in book1 \n=VLOOKUP( $A2, [Book1.xls]Sheet1!$A$1:$C$4, 3, FALSE)\n\nthis function will search for the A2 value (Car1) in the first column of [Book1.xls]Sheet1!$A$1:$C$4 (Column A) and returns the 3rd value from that table of that row (Column C, cell C2).\n\nMail me here through Y! Answers if you are intersted in the macro part.\n\nEnjoy my profile, I am the VBAXLMan
459913
Excel has that limitation.\n\nLook into using MS Access instead.
967626
EXCELL - Spreadsheets
747603
Depending on where you are (the main form or the subform) and what you need to access, there are several different ways of doing this.  Check out the forum archives at:\n\nhttp://www.access-programmers.co.uk/forums/archive/index.php/t-77746.html\n\nto see how to do this.  I grabbed the text and saved it on my hard drive so I could get to it later; this cheat sheet really helps!\n\nPS: This is a MS-Access question, not a VB 6 question.  MS-Access uses "VBA" (vb for applications), VB6 has a compiler and it's own form editor.
780343
If you are in VBA in MS Access? Than you can cut the string and used the variable directly and then finish the string.\n\nstudTbl.Open "SELECT * FROM [studlst] where ([no]=" & i &")"
850443
ok I give some hints\nIf you want to refer to a database from Excel you need to add the ADO 2.8 objects Library. \nHow to proceed?\nFrom the Visual basic editor, click on tools (menu bar) and then reference. look for Microsoft Activex Data objects 2.8 library and tick it.\nNow you should have access to ADO objects.\nTo learn how to use these objects try the msdn microsoft website, full of advice and templates\n\nIf you want an example from me, give me a valid email address where i can send you an excel workbook and small access data base for it takes too long to explain
881205
VLOOKUP is used for lookup tables in which the compare values are placed in the table's first column. The general form is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), where lookup_value is the lookup value you want to send to the table, table_array is the cell reference of the table, col_index_num is the number of the column in the lookup table containing the value you want to retrieve, and range_lookup indicates whether the compare values constitute a range of values or an exact match.
332491
Try digdb excel add-in\n\nthis is the linke you need
880681
you can also go :\nTools-Analyze-Documenter\n\nPut this into a module and hit f5. Just a little VB to get you started, let me know if you have any questions.\n\nFunction DocumentTBL()\nDim tdef As TableDef\nDim fld As Field\nDim qry As QueryDef\nDim qryName As String\nDim IndexFields As String\nDim qdefPrimaryKeys\nDim strsql As String\nDoCmd.SetWarnings False\nOn Error Resume Next\nDoCmd.RunSQL "drop table tbltables"\nOn Error GoTo 0\nDoCmd.RunSQL "create table tbltables (ID  COUNTER CONSTRAINT PrimaryKey PRIMARY KEY,TableName text," & _\n                "FieldName text,FieldDataType text,PrimaryKey text,InQueries text,Notes text)"\nFor Each tdef In CurrentDb.TableDefs\n\n    If Left(tdef.Name, 4) = "msys" Then GoTo 1\n    If tdef.Indexes.Count > 0 Then\n        IndexFields = Replace(tdef.Indexes(0).Fields, "+", "")\n    Else\n        IndexFields = ""\n    End If\n    qryName = ""\n    For Each qry In CurrentDb.QueryDefs\n        If InStr(qry.Sql, tdef.Name) > 0 Then\n            qryName = qry.Name & ", " & qryName\n        End If\n    Next qry\n    For Each fld In tdef.Fields\n        DoCmd.RunSQL "insert into tbltables (tablename,FieldName,FieldDataType,InQueries,PrimaryKey) " & _\n                        "values (" & Chr(34) & tdef.Name & Chr(34) & "," & Chr(34) & fld.Name & _\n                        Chr(34) & ",'" & EvalControlType(fld.Type) & "'," & Chr(34) & qryName & Chr(34) & ",'" & IndexFields & "')"\n    Next fld\n1 Next tdef\nOn Error Resume Next\nDoCmd.DeleteObject acQuery, "qryPrimaryKeys"\nOn Error GoTo 0\nstrsql = "SELECT tbltables.TableName, tbltables.PrimaryKey FROM tbltables GROUP BY tbltables.TableName, tbltables.PrimaryKey "\nstrsql = strsql & "HAVING (((tbltables.TableName)<>'tbltables'));"\nSet qdefPrimaryKeys = CurrentDb.CreateQueryDef("qryPrimaryKeys", strsql)\nDoCmd.SetWarnings True\nEnd Function\n\nFunction EvalControlType(ctlType As Integer) As String\nSelect Case ctlType\n    Case acCheckBox\n        EvalControlType = "Check Box"\n    Case acComboBox\n        EvalControlType = "Combo Box"\n    Case acCommandButton\n        EvalControlType = "Button"\n    Case acImage\n        EvalControlType = "Image"\n    Case acLabel\n        EvalControlType = "Label"\n    Case acLine\n        EvalControlType = "Line"\n    Case acObjectFrame\n        EvalControlType = "Frame"\n    Case acOptionButton\n        EvalControlType = "Option Button"\n    Case acOptionGroup\n        EvalControlType = "Option Group"\n    Case acRectangle\n        EvalControlType = "Rectangle"\n    Case acSubform\n        EvalControlType = "SubForm"\n    Case acTabCtl\n        EvalControlType = "Tab Control"\n    Case acTextBox\n        EvalControlType = "Text Box"\n    Case acToggleButton\n        EvalControlType = "Toggle Button"\n    Case acListBox\n        EvalControlType = "List Box"\n    Case dbBigInt\n        EvalControlType = "Big Integer"\n    Case dbBinary\n        EvalControlType = "Binary"\n    Case dbBoolean\n        EvalControlType = "Boolean"\n    Case dbByte\n        EvalControlType = "Byte"\n    Case dbCurrency\n        EvalControlType = "Currency"\n    Case dbDate\n        EvalControlType = "Date / Time"\n    Case dbDecimal\n        EvalControlType = "Decimal"\n    Case dbDouble\n        EvalControlType = "Double"\n    Case dbFloat\n        EvalControlType = "Float"\n    Case dbGUID\n        EvalControlType = "Guid"\n    Case dbInteger\n        EvalControlType = "Integer"\n    Case dbLong\n        EvalControlType = "Long"\n
1044851
One question: when you type "2" in the field t4f3, what do you want to lookup in table 2? Fields? Records? All fields? All records? I believe you're not designing the right way your database. Please be more specific.\n\nMakiavel
1172805
I would use Data Validation and VLOOKUP in Excel to do this.\n\nType in the 2 lists (for convenience lets start in A1) then add a third column:\nSerial#1          Combo#1          =A1\nSerial#2          Combo#2          =A2\nSerial#3          Combo#3          =A3\n\nMake the lists as long as you need. We created the third column for later; it just copies the serial numbers over to simplify things for the VLOOKUP formula.\n\nNow that the data is input we will need to create a drop-down menu. Since the data is in columns A-C, click in E1 to select the cell and click Data | Validation… In the Allow box choose List and type the range for the combinations in the Source: field (in our example: A1:A3). Now, click on F1 and type this formula:\n=VLOOKUP(E1,A1:B3,2,FALSE)\nIn the parentheses you will have this data: \n-E1 is the criteria you will look up in the array.\n-A1:B3 is the array where the data resides (remember this is from the example, yours will be longer).\n-2 in our formula tells VLOOKUP to pull the data in the 2nd column that matches our criteria from the 1st column.\n-False tells VLOOKUP to look for an exact match.\n\nWe have now created the Serial Number lookup that returns the proper Combination. To lookup the Serial Number from the Combination, we just do the steps again.\n\nClick in E2, choose Data | Validation… Again, in the Allow field choose List, but this time we need the combinations in the drop-down (B1:B3); click OK. The formula for F2 is almost the same:\n=VLOOKUP(E2,B1:C3,2,FALSE)\nThe only differences is that the criteria and array are shifted; the criteria down one row and the array over one column.\n\nIf you wish to hide the rows that contain the array, just right-click on the column headings and choose hide.
186906
In Excel, I would use a lookup table.  However, if you're talking a programming language solution, one Visual Basic althernative would be "Select Case".\n\nmonthnumber = 3\n\nSelct Case(monthnumber)\nCase 1\n   monthName="January"\nCase 2\n   monthName ="February"\nCase 3\n   monthName="March"\n\netc.\n\nCase 12\n  monthName="December"\n\nEnd Select
876460
First of All, without the actual file, trouble shooting will be tough...\n\nAnyways,\nI had faced some calculation issues previously. It got solved when I created a fresh Pivot table in a new worksheet "afresh"... Though the Source data was same, the 2 PT's showed different summaries...\nTry it...\n\n\nTip / Info: \nAlso have a look at the Custom calculation of "Running Total", this can be useful for U as well...