I'm using a spreadsheet to assimilate data from a number of different files. Now I need to vlookup on this compiled data, but the vlookup cannot find the value because it is not looking at the value of the data in the cell, but rather the formula. How do I get vlookup to search a column by the value displayed in that cell, rather than the reference to some other file?
View 9 Repliesi am trying to write some code to analyse a weightlifting movement that occurs 3 times. In particular there is a part of the macro where the user will input the start and end time of the movement using input boxes (to only select relevant data).
I want to then use a vlookup function to search for the start and end times in a range (1 column) in the time range and return the cell reference of these so i can select only these values and either create a graph or do more analysis. My code for this particular part so far looks like:
I have a sheet which uses a vlookup to find the data on a large sheet. normally to get the cell reference of the data i would use cell("address",......
However this appears not to work with a vlookup.
I have a worksheet that uses a lot of vlookups and I have to update the filenames and locations quite often. I would like to update a single cell rather than updating every single formula.
However I am getting the #VALUE error when trying this.
Simplified Example:
I have: =VLOOKUP(A3,[Table.xls]Sheet1!$A$1:$B$4,2,FALSE)
I would like to place [Table.xls]Sheet1!$A$1:$B$4 into a cell (D1) for example.
And have my vlookup function as =VLOOKUP(A3,$D$1,2,FALSE)
This way I only have to update D1 when I want to change the filename instead of a whole lot of functions.
I am currently having real problem using the formula below:
=INDEX(Data!C:C,MATCH(B7,Data!A:A))
What i am trying to do with this formula is return the cell address of an item that is in a different sheet.
The reference the for search is in Cell B7 which is a date, it then looks at a different sheet "Data" for this date which is in Column A. It then returns the Cell Reference of Column C of the same row.
Currently it is just returning the value of column C and not the cell reference.
I am trying to create a lookup formula where the cell value to match has a formula behind it.
For example: =INDEX(AR7:AR371,MATCH(G28,AE7:AE371,0))
The trouble I'm having is that the value in G28 is derived from a formula and the Index Match formula then gives a #N/A result. If I change the value in G28 (a date) manually, so directly enter a date, the Index Match formula works ok.
Is there any way of getting the Index Match formula to work, or Vlookup would also do although that at the moment that has the same problem with G28 having a formula behind it.
I am trying to use vlookup so that when each site name is selected the relevant comments for that site are displayed.
I have a formula that is working, but it displays "0" if there is nothing in the cell. How do i get it to display a blank cell if thre is nothing in the reference cell.
this is the formula that i am using:
=IF($C$4="", "", IF($C$4="No Match", "", (VLOOKUP($C$4,Comments!$A$2:$U$295, 5,0))))
The site name appears in C4, and is selected from another sheet in the workbook.
See attached a sample from a larger workbook I am working on. What i would like to do is in the Rec tab column G, keep the references from columns L & M as the Table Array and Column Index Number. I have =VLOOKUP(F:F,L:L,M:M,0), I would like to have =VLOOKUP(F:F,whatever tab reference is in column L as table array,whatever number is in column M as index number,0). I have included what I would like the data to look like in coulmn H.
View 2 Replies View RelatedSheet 1 contains:
Item Sheet 2
ABC =vlookup(A2,'Sheet 2'!$A:$B,2,false)
Sheet 2 contains:
Item Data
ABC 2
I'm trying to get the vlookup to return the value "2"
Right now, I'm manually entering the tab name in the vlookup function, even though it's contained in cell B1.
The tabs are contained in the same workbook if that matters. Since this workbook is growing rather quickly, this is a painful process and doesn't feel very scalable. Since I'm using a mac, I need to do this with functions vs. macros. Does anybody know how I can reference a cell for the name of a tab in the vlookup function?
I was thinking I could maybe somehow do this with the INDIRECT function but I'm stumped.
How can I do the following....if for example I have the following vlookup;
Code:
=vlookup(A12, 'sheet 2 $A$2:$ID50$, 3, false
How can I change the column index i.e. the 3, to reference to a cell.
Code:
=vlookup(A12, 'sheet 2 $A$2:$ID50$, H1, false
The above example doesn't work but I'm sure something can be done using TEXT or VALUE
In workbook 1, I have summary sheet with columns
A -> SNO
B -> Customer Name
C -> Product Name
D -> MODEL
E -> Quotation Rate
I have to retrieve MODEL & Quotation Rate .
and also having seperate sheet for each customers say X1 , Y1 , Z1 ( so other sheet names as X1, Y1 & Z1)
In X1 sheet , I do have following columns
A-> Product Name ,
b-> Model (as of now limited to 1 per product name)
c -> Rate
Now my request is as follows Based on the Column b value in Summary sheet - i have to goto respective sheet and do vlookup for the respective product name and retrieve model and fill it in Column D. I heard i can use INDIRECT function and Vlookup in this junction .
I have problem with a spreadsheet that I am trying to create.
I have a large sheet of data which is dumped in from another program. This contains our deliveries and orders etc.
Now for planning purposes, I would like to see how much of each item I have on order.
I can use VLOOKUP, but that will only give me the amount for the first order it encounters. But not the 3rd, 4th etc. I could use SUMIF but I need the dates as well. After doing some searching I think I have found a way of doing this: I can get the first easier enough:
=VLOOKUP(D$11,'purchase order'!$A$1:$K$6000,5,FALSE), this gives the first order than the another =VLOOKUP(D11,'purchase order'!$A$1:$K$6000,11,FALSE) for its date.
For the second column to check any other orders I thought I could find the cell referance for the first SEARCH: which is
A108: ="A"&MATCH(D11,'purchase order'!A:A,0).
Is there any way of using this Reference to start a new VLOOKUP. So the Lookup Range starts at this reference?? To make things harder it is on another sheet.
I am doing a vlookup on a cell range where the value I'm trying to lookup (a date) exists, but it's not an actual number in the cell...it's a reference to another cell with that value (somewhere completely different).
So, I'm trying to vlookup(date(1/1/2011),A1:A12,2,false) to get the B column value.
A B
1/1/2011 #
2/1/2011 #
...
12/1/2011 #
However, the A column is not the actual date. It is a reference to another cell somewhere completely different that has the actual date 1/1/2011.
When I do a vlookup trying to find 1/1/2011, it can't see it there unless I overwrite the reference in A1 (for instance) with the actual date.
Can I do a vlookup and keep my cell references?
Explanation of problem:
I have 2 files: data.xls and calculation.xls
In data.xls, I enter a value in cell A1
In calculation.xls, I go to cell B1 and enter a reference to cell A1 on file data.xls. The following formula is entered in cell B1 of calculation.xls: =[data.xls]Sheet1!$A$1
Ok so far so good. Now when I change the value in data.xls, the value is updated automatically in the calculation.xls cell. And this is done even if I first update the value in data.xls and then later open the calculation.xls file (it will ask me to update).
Now let's say I have calculation.xls closed and I am updating the data.xls file. I enter a new entry above cell A1 in data.xls. So now the contents of cell A1 are shifed down to cell A2.
When I re-open my calculation.xls file, it is still referencing cell A1 in data.xls. Is there any way for me to link it in a way so that calculation.xls would know that it now needs to reference cell A2 since that cell got shifted down?
If both files are open then it works fine. But the way this setup will work, I will not always have them both open.
Also, if I copy the contents of data.xls onto another sheet on calculation.xls and reference the cell in the same file, then of course it works. But I would like it to work the other way with the files separate.
I have multiple files in one folder: first.xlsx, second.xlsx, third.xlsx etc
In each of them the first cell is calle fist_indicator, second_indicator etc
In a new document i have a column A with all the names: fist, second, third
In a hidden second column i have ="="&"["&ZS(-1)&".xlsx]Tabelle1!"&"Z1S1" which gives the value of =[first.xlsx]Tabelle1!Z1S1 , =[second.xlsx]Tabelle1!Z1S1
So now when i try in column c to just say = R1C2 it gives a #NAME? error
Summarized, i have a manually filled in column A and with it i want to make another column that shows the first cell of each document (so it uses the fist colum + the other directions to show the first cell)
if i just add =[first.xlsx]Tabelle1!Z1S1 it works, but i want to be able to make this first.xlsx be changed according to the entry in column A.
I am trying to use VB to vlookup between to workbooks
1. Make active workbook WBK1
2. Make workbook being open WBK2
3. Copy and Paste between WBK1 and WBK2
4. Have a vlookup in WBK1 and bring in the values from WBK2
5. Close WKB2
6. Copy, Paste, and transpose values in wkb1 within wkb1
The script works fine until it reaches the vlookup step. I have used the vlookup by itself without the copy and paste code successfully but when I combine the two it provides me with the error 9. Subscript out of range.
I want to use a macro to open an existing file on my c drive where the reference to that file is contained in a cell.
E.g. In my sheet, the cell A1 contains the following text "C:Documents and SettingsTest". Therefore the file I want to open using a macro will be Test.xls.
However the file I wish to open (and therefore the reference text in cell A1) could change (and it could also change to be a word document) so it needs to be able to adapt when a new valid file (and document type) reference is entered into the cell.
Also, as an aside, when I want to change the file referred to in A1, the easiest way I've found is to find the file I want in windows explorer, go to properties and copy the file reference and then paste it into A1. However the reference (from the properties tab) seems to miss the ".xls" or ".doc" at the end (see above). Is this an issue? If yes, then I'm happy to manually type the .xls or .doc extension at the end.
Is there an easier way to do this?
I have the following (working) formula that pulls the largest value from an array in a closed workbook on a network share:
=LARGE('\pdfpcoclu2k302pd10fap2k01dist10AdminFISCAL SHAREDM806 ReportsFY 2008-2009Clarion countyJanuary 09[385-4009.xls]M-805'!$F$11:$G$41,1)
I want to be able to use a cell value within the open workbook to change the name of the referenced workbook (i.e. the 385-4009 portion of the formula) in the fromula
I have atempted to use the following:
=LARGE('\pdfpcoclu2k302pd10fap2k01dist10AdminFISCAL SHAREDM806 ReportsFY 2008-2009Clarion countyJanuary 09["'"p8"'.xls]M-805'!$F$11:$G$41,1)
but excel returns an error and highlights the following portion of the formula:
'\pdfpcoclu2k302pd10fap2k01dist10AdminFISCAL
I'm trying to open a workbook using a macro, then saving the workbook that's just been opened using a cell value as the title.
This is what I have so far ... to open up the workbook and then run a macro from there .. this works fine
Sub Test1()
'
' Test1 Macro
' Macro recorded 08/11/2009 by Tracy
'
Workbooks.Open ThisWorkbook.Path & "/MultiFuel Expense.xls"
Application.Run "'MultiFuel Expense.xls'!TestSave"
End Sub
This macro is the one I'm struggling with. I need to save a copy of the workbook I just opened (currently named MultiFuel Expense) in the same directory using the cell contents of G2 as it's name with _expense on the end.
Sub TestSave()
'
' TestSave Macro
' Macro recorded 08/11/2009 by Tracy
'
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "" & Range("G2") & "_expense"
End Sub
Trying to reference cells in a closed workbook for the first time, and am having problems. The formula in my cell is: ='H:Trial BalanceTESTING[12-07-2007.csv]12-07-2007'!$P2
At first it wouldn't work, and I always had a #REF! in that cell, but when I opened the csv file the correct value showed in the cell that has the formula. Is it not possible to have it work without me having to open the csv file as well?
I want to save my file as a pdf based on cell reference A4.
This is what i have
[Code] ....
i have a order form that is customer dependant. the cust name on the form is manually entered by the user within a msg box. i have specific cusotmer folders on a shared machines C drive where i would like to save these excel docs to.
i understand that the cust name must match the name of the file and there needs to be a way to save those that do not have specific folder matching what was entered as the customer name.
the overall goal is to have the file name saved as CustomerNameDeliveryDateInvoiceNumber.xls where CustName is from cell "M3", delivery date is "Q7" and InvoiceNumber is "Q1".
Sub OrderFormSave()
Dim strCustFileName As String
savefile = "\SrSharedDocsCSPSharedFILESCustomerOrderForms & strCustFileName & "
ActiveWorkbook.SaveCopyAs savefile
End Sub
I need to reference a cell to another cell in a different workbook (not opened), however the name of this workbook contains a variable in its name. Something like:
='C:[Week" & variable &".xls]R23'!$D$3
where variable is the number of the week. I have tried using a cell name instead of a variable: ='C:[Week=($B$3).xls]R23'!$D$3
and I get a dialog box to update values by selecting a file.
I'm trying to make a vba that would save the file with the end date that will reference to a certain cell that user selects it. Working Sheet is a name of a File, and Current Sheet is a name of the panel where user will select the date.
ActiveWorkbook.SaveAs Filename:= WorkingSheet("Control Sheet").Range("D3").Value & Format(Date, "yyyymm") & ".xlsm", FileFormat:=51
But here i seem to missing the location
And I tried this one:
ActiveWorkbook.SaveAs Filename:="X:Work2014WorkingSheet& Format(Date,"yyyymm"). & ".xlsm" _, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
but it keeps popping up as 'expected end of statement' for yyymm?
I'm trying to get Mac OS X Excel VBA to understand this code however the ChDir reference and the filepath seem to be causing problems. I would like to specify in the code where the file should be stored but I did try removing the filepath section altogether but to no avail.
Option Explicit
Sub SvMe()
'Saves filename as value of A1 plus the current date
Dim newFile As String, fName As String
' Don't use "/" in date, invalid syntax
fName = Range("A1").Value
'Change the date format to whatever you'd like, but make sure it's in quotes
newFile = fName & " " & Format$(Date, "mm-dd-yyyy")
' Change directory to suit your PC, including USER NAME
ChDir _
"C:Documents and Settings USER NAME Desktop"
ActiveWorkbook. SaveAs Filename:=newFile
End Sub
I have 2 files: lets say FILE1.xls and FILE2.xls. In the FILE1.xls i fill in a cell with FILE2.xls name. I want to do a vlookup in the file 1 and to take the name of the file2 from the cell i have filled. for example =VLOOKUP(A1,'[FILE2.xls]Sheet1!$A:$C,3,0) this is the simple vlookup. What i am asking is that i take the file name from the cell. I tried like this: =VLOOKUP(A1,'[=B5]Sheet1!$A:$C,3,0) B5 is the cell with the file name "FILE2.xls". It sais that the name is not correct.
The reason i want to do this is because each day the file name is changed and i want to put in the refference cell the file name and do the vlookup using the file name in that cell.
I am trying to automatically reference the contents of another file into my main file. Basically I have one schedule that I've automatically generated in AutoCAD, and another formatted template. The AutoCAD schedule is completely unformated.
I want to include a link to the AutoCAD scheule (a *.CSV file) on my main spreadsheet and have the autoCAD schedule populate my template. I've done some reading, primarily on here, and I think I need to use either the INDIRECT() or the INDEX() function.
I can get INDIRECT() to work, but it's fairly repetitive (not overly important, but still.....). More importantly, though, the schedule file has to be open, which I would like to avoid if possible. INDEX(), I just can't figure out how to work!
I have created a macro in excel 2010 which enable the file to save (extract) data into separate location and name. The vba code for macro is as follows: Question: How can I save this workbook with reference to the value containing in cell B2? (it is named temporary now - as defined in the code)
Sub aaa()
'
' aaa Macro
'
[Code].....
I have a list of job names in a column. All names contain hyperlinks to a corresponding file. I need to retrieve the value of "C2" in each hyperlinked file and place it next to the original job name.
View 6 Replies View Relatedgenerally vlookup function gives a value from the right side cell. is it is possible to display a cell from the left side. in the attached excel file i want the numbers against the cell which contains mom.....
View 2 Replies View Related