Using Formula With Cell Reference To Work In VLOOKUP Function
Jan 30, 2014
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 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 have a workbook with a Summary sheet and then a series of sheets for each month/year. Onthe summary sheet I use column A for the month/year as a date formatted "mmm yy" ie the same as the sheet names.
in columns B,C,D I want formulae that sums or averages a fixed range on each sheet but I would like the formula to take the sheet name from Columa A rather than hard coding it.
So that in the example =SUM('Dec 04'!$C$6:$C$47) I want to take the "Dec 04" from the text in cell A3
I'm coping and pasting data (html) into a worksheet. When I try to format some of the columns as accounting$, number, ect. They won't format. It looks as if there are spaces before and after the data. So I tried the trim and clean function but it doesn't work, so I guess the blank "spaces" are not really spaces.
It gets better...
When I do the same exact copy and paste (as html) on another persons computer, they are able to format just fine, and the "spaces" are removed automatically.
Why is this working on one computer but not the other? Is it a setting that my computer has enabled or disabled? If so, what is it?
I am having difficulty with a Vlookup in Excel 2003. Basically I have converted both fields to Text using the text function =TEXT(A2,"0") and have tried matching the values but just get an #N/A error and I have also converted both back to number format but still get the #N/A error....when I do a "=" operator function it returns a FALSE value but I cannot see why as both cell are just 4 or 5 digit number.
I built an Excel Macro to pull information from my schedule and compile it within another Worksheet. The Macro below searches for an instance of "Unavailable" and then populates my Data Worksheet with the WeekDays (ie: M-W-F or ALL Week), the Details (ie: Vacation or Training) and the Name of the employee. I struggled with a way to populate my employee name as "unavailable" isn't always offset by the same amount of characters to employee name (as it is with WeekDays and Details). Instead, I collected the row in which "Unavailable" was found.
I used the Vlookup function to populate the number of Absences and it worked swell.
C2 is populated with Weekdays (ie: ALL week or M-W-F) O contains a list of possible day combinations P contains the number of days
I hoped to use a similar function to populate my employee names. This didn't work well a 'tall.
I have the following formula in cell L51 of all sheets calculating the volume depending on the monthly index that is chosen from the drop down menu in a particular sheet. =If(MIndex=0, SUM(D33:L50),If(MIndex=1,SUM(D34:L50),If(MIndex=2,SUM(D35:L50), 0))). I am getting the following message and I do not understand what it is about.
Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference. Try one of the following
I am trying to do a "getpivotdata" formula on a cell in certain file that gets info from a pivot table on another file. However the name of the second file (the one where the pivot table is found on) might be different each time so I created a variable for the second file name but I canít figure out a way to use this variable in the get pivot data formula as I am not very experience with VBA.
Template = Application.InputBox("What is the name of the file ending with (.xls)") If Template = False Then cont1 = MsgBox("Please write the name correctly using (.xls)!", vbOKOnly) If cont1 = vbOK Then Exit Sub End If End If
I have a table of consolidated data that consists of the date at which certain payments occur and their amounts that other sheets need to reference to perform calculations. I am currently using the formula below to put the data under the correct date column. =INDEX(ImportedData!$B$10:$DB$10,MATCH(B3,ImportedData!$B$4:$DB$4,FALSE)). The problem that I have is that the INDEX range, currently B10:DB10, will need to change depending on the number of files that are consolidated ie with more files the row will increase.
I tried to answer a problem on here by giving this formula: = COUNTIF(A1:B6,"=""") but it doesn't work on my machine. Nor does =COUNTIF(A1:B6,"<>"""). In either case, the effect is as if all cells in the range are non-blank. But the result of =A1="" is "TRUE". Neither syntax is rejected by Excel; in fact, if you omit one of the quotes, the syntax is corrected to the form shown. So I am curious. Does this work normally, but some setting in my machine is stopping it? Or, if it never works, why is it not giving a syntax error? I am using Excel 2000.
How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.
________A________________B_____________C_________D 1 Task___________Trigger_____________Days_____Due Date 2 Design begins__Proj OK______________10____10-Jan 3 Specs written__Design begins (A2)____5____15-Jan (D2+C3) 4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)
If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?
Attached I have a document where I am wanting all of the individual sheet names on the Total page. Rather than having to change each formula to match the sheet name I believe there is a way to reference the sheet name column (AO) in the formula so you can drag it down to fill in the columns. I am basic with Excel and do not know how to add this indirect function into my current formulas.
i have two excel files which are data and master..both files will be use for salary calculating.. the vlookup formula will be use in master files for dragging the salary data from Data files..the formula as follow VLOOKUP(B4:B225,'D:Salary[Data.xls]AUG'!A$1:F$65536,6,FALSE))
the vlookup working fine to me.. but my problems is i want the data to be auto calculated when they have same value in two columns.. or if the name is similar/match in two columns (one for salary and another one for overtime), the salary should be auto calculated.
Data files will contain of these: a:Employee ID b:employee name c:Employee salary/Overtime
Master files will contain of these: a:Employee ID b:Emplyee Name c:Employee Nett Salary (that will be dragging from Data files)
is there any formula that i can used to combined with my vlookup formula?
i 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 am currently having real problem using the formula below:
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'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?
I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.
12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.
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.
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.