This is almost embarrassing, but I have a simple VLOOKUP function that I can't seem to figure out. On Sheet2 cell A11 is the value I want to look up in Sheet1. Sheet2 B12 is the cell I am entering the VLOOKUP function. The value I want entered is in column 7 of Sheet1. I hope I have explained this enough. My formula entered is =VLOOKUP(A11,Sheet1!$A$9:$A$42,7,FALSE).
For whatever reason this is not working, and I am getting a #REF! error.
I've got a spreadsheet with several rows and the numbers within those rows are totalled up at the bottom. Is there a way where i can highlight the cell within the spreadsheet and it takes it off the accumulative total at the bottom? or something similar to this effect? I just don't want to have to remove figures from this spreadsheet and highlighting will be a good way of seeing what has been done
View 1 Replies View RelatedI have a question on data interpolation with Excel 2007. Normally, this wouldn't be too much of an issue for me, but for some reason I cannot figure it out.
I'm working on an Income Statement, which is designed for 5 years. I have values 1 and 5, which are given to me (B7: 3,500,000 and F7: 5,200,000). I'm trying to figure out the other 3 years of revenue assuming a growth trend. The values should fill the series B7:F7.
I'm trying to see how accurate people's work predictions are to actual work completed. So I have these formulas:
=IF(C15=0,"",(SUMIF(L$24:IO$24,"Est.",L15:IO15))) - total estimated days
=IF(C15=0,"",(IFERROR((SUMIF(L$13:IO$13,"Act.",L15:IO15)),""))) - total actual days
But the problem of course is that people estimate a load of work and only fill in the actual days as they go along, so the accuracy of comparing one to another is almost always misleading.
What I want to do is only count the values in the weeks Estimated if the Actual figure is also there (L24:IO24), which is always the cell directly on its right.
I learnt that " " & can combine the text and figure. However, when it comes to a negative numbers, i dont know how to maneouver it. Eg, Cell A1 is USD(50,000.00) and the comment in Cell B1 is Cost is USD(50,000.00). So the function in B1 = "Cost is "& what to put here?
View 3 Replies View RelatedI have done a search and I think this is possible but just can't convert any of the examples to work for me.
This is the calcuilation I would normally enter -
=VLOOKUP(A80,'[CF227.xls]Sheet1'!$A$9:$J$9,10,FALSE)
However I don't want the cell 10 rows across when a match is found I want the figure 10 rows across and then 2 rows down from that point. i.e. if the match is in row A9 I want to return the figure in J11.
I am trying to find some missing values compared to 6 base values. For instance, I have a sheet with some names translated to another language, I am trying to find the languages some names have not been translated too.
For example, if I have six languages, Arabic, Japanese, Russian, Chinese-Simplified, Chinese- Traditional, and Korean to compare too, I want to find any names that are not translated in certain languages.
Sample:
John Japanese
John Chinese - Simplified
John Korean
Martin Arabic
Martin Chinese - Simplified
Martin Russian
Ramon Arabic
Ramon Russian
Sam Arabic
Sam Chinese- Traditional
two formulas for one data set. The data is attached in the spreadsheet: "Product IDs". The data is a set of Master Product IDs (parent) and the Linked to them Products (children). I need to create a relationship between unique parents (Master Product IDs) and their children (Linked Products)
I need to create two formulas:
1. From the Data Set table, need to vlookup the unique value in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). So, the result will be as shown in Table 2.
2. From the Data Set table, need to vlookup the unique (de-duplicated) parent/children relationship in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). There are total 3 parent/children relationships in Table 1. So, the result will be as shown in Table 3.
i want to put a subject for a negative values, how should i do it?
For example:
lookup_value: -1000
table_array:
<0 - Poor
0-10000 - Good
> 10000 - excellent
is it possible to do this? ALternatively see the (new) attachment for clearer question.
I am trying to do a vlookup function of 2 values but the key lookup column is an approximation.
I have attached a sample data file. Essentially, on Sheet 1 Column M (labeled as EPS), I want to do a vlookup of both Column A and B, and copy the values in Sheet 2 Column K into Column M. The approximate value comes from Column B, the date, as Sheet 1 has all the dates from 2001 and 2008 while Sheet 2 only has 4 dates per year. On sheet 2, the first 2 dates are 10/30/2001 and 2/5/2002. I want the EPS value for 10/30/2001 to stand for all values between 10/30/2001 and 2/5/2002.
The Vlookup function with a True value for the last criterion can do this for a vlookup of 1 variable, but it doesn't work for 2.
I have tried searching for it but I must not be entering the correct criteria.
how to figure a person's age if all I have is that person's birthdate?
i'm doing a process and in column N, i would need to have the following combinations:
A1
A2
C1
C2
H1
I1
so basically, a formula that would figure out the combinations available!
can this be done using a formula?
******** ******************** ************************************************************************>Microsoft Excel - Cost Calculator---V5---Try.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutK39=
KLMN39A12 40B 41C12 42D 43E 44F 45G 46H11 47I11 48J Cost Calculator
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have a command button (code listed below) which is 1/2 working. I have a column of PID's (numbers) sorted in ascending order which includes multiple rows with the same PID. When the command is executed, I want to insert a new row after the last matched PID (this part of the code works fine),
or
Insert a new row for the non-existent PID but in the correct sorted location (this is what I can not seem to figure out how to get to work).
My data looks like this in the PID column.
PID (my header row in A1)
158
158
159
161
163
163
163
164
I can successfully insert another line 158, or 159, but can not determine where to insert a line for PID 160 (which is not in the list yet)..................
I have this great code, but I can;t figure out how to add more columns to it.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 2 Then
If Target.Value = "Q" Then
With Target
.Value = ""
End With
Else
With Target
With Selection.Font
.Name = "Wingdings 2"
.Size = 22
End With
.Value = "Q"
End With
End If
Cancel = True
End If
End Sub
I have the following which does what it says it does.
What I can't figure out is, If the used range Starting in row A5 is greater then or less then A50 not to apply the borders.
.Range("A5:M50").BorderAround Weight:=xlHairline
.Range("A5:M50").Borders(xlInsideVertical).Weight = xlHairline
.Range("A5:M50").Borders(xlInsideHorizontal).Weight = xlHairline
Here is what my table is im working with. The tite "todays date starts in Cell A1 and B1 is =Today().
I need to figure out how to do an if for the over 30,60,90 past due. Here the logic I need to figure out:
in the Over 30 column, the logic of the statement is as follows: If the difference between today's date and the date of the invoice is greater than 30 and equal to or less than 60, the amount goes into this column.(over 30 E3) To get the amount(the ammount in C3) into the Current column, the difference between the two dates must be less than 30. To get into the over 90 column, the difference between the two dates must be greater than 90. You can figure out the over sixty column. And this needs to be copied to the remaining colums
Today’s date: =TODAY() Shop Invoice Date Invoice Amount Current Over 30 Over 60 Over 90 Ludwig's Landromat =TODAY()-72 $432.78 Willie's Washing =TODAY()-107 $565.11 Wash It Here =TODAY()-80 $308.47 Spiffy Wash and Dry =TODAY()-58 $725.37 Water Soap & Hot Air =TODAY()-30 $427.95 Wash it & Dry it =TODAY()-90 $603.91 Acme Wash & Dry =TODAY()-60 $332.35 Totals
by using vlookup i can only get the first data for every time. how can i get this table?
View 14 Replies View Relatedlook at my Vlookup formula and tell me what I'm doing wrong? I have a drop down menu that contains a list that I want to look up its values (as shown on the 'A' columns), the look up values are in sheet 2.
View 3 Replies View RelatedI have a column in a sheet that has the same data more than once and I'm using a vlookup function to pull that information from the column. However, I don't want to repeat the same information more than once. Is there a way to show an item in vlookup only once.
Here's the scenario:
Column A
New York
New York
LA
Washington
New York
Washington
Washington
LA
New York
I want the outcome in my vlookup to be:
Column A
New York
LA
Washington
under column F, 01/05/1901, to display Jupiter in F6 and also populate Neptune in F9 and Saturn in F7, based on the date_aspect table.
I'll keep this short and just post the sheet.
I need a macro to calculate the order value i.e when i fill in a qty against any code a macro would execute and get the rate of that code from (rate file worksheet) and multiply that value with the fill in qty and display it and also, when i fill in a qty against another code the macro should perform the same procedure but in this case it would add the value to the last value and show the combined total value for the order,
View 9 Replies View Relatedthis is the code i have for lokking up two values(J2&K2) but i would like to add a third one L2
=VLOOKUP(J2,OFFSET(A$16,MATCH(K$2,A$17:A$782,0),1,COUNTIF(A$17:A$782,K$2),3),3,0)
I have a vlookup that searches through a named range to find a account number and returns the value of an account payment.
The lookup works fine until a two or more payments have been made to the same account. Then the lookup only return the first value in the range.
Is it possible for the lookup to SUM all the payments and return the total for that account number?
I have been building a tracker to track benefit payments for child benefits and DLA so I know where I stand and what has been paid.
The problem... its limited and basic an I have to do most things manually, even when I have cell formulas to do some of the legwork.
I stumbled across the term MATCH and SEARCH and I was curious on how to use them for my needs, I was trying to use the LOOKUP set of tools.
My problem...
Benefit rates are set between two date periods, all payment periods are 28 days apart (...but could change to every 7 days) which means that sometimes payments are due 3 weeks of one rate, 1 week of another, the reverse of that or 50:50 depending on the cycle.
A
B
C
[Code]....
The problem should be very evident and especially when I adjust the dates to match what payment dates I finally end up with.
The result of the look up needs to be calculated at the frequency in A14 to B14, A15 to B15 (for example) at the appropriate rate found in D:D when searched for based on the dates in A14 to B14 ... ... etc.
I tried using MATCH to get a match to a date falling between 30/12/2013 and 4/4/2014, I tried this...
=SEARCH(A15,MATCH(B15,A4:F8,1),A15)
which returned #N/A
I have two cells that each contain their own lists (listA=A1, listB=B1). I'm trying to figure out how to say:
If the selection from (listA= X AND listB=W )or the selection from listB=Y output 0, otherwise output data from C1.
Susie:
tootsie-pop 3
oreo box 2
suckers 5
Bob:
tootsie-pop 2
suckers 1
nerds 4
I need to put together a list that shows (in total) how much candy everyone got. Sure, I could concatenate Susie & Bob's (and everone elses) lists, sort, duplicate, put a sum field into the duplicate list and delete repeat names, like...
nerds 4
oreo box 2
suckers 5
suckers 1
tootsie-pop 3
tootsie-pop 2
duplicate, then the second looks like:
nerds 4
oreo box 2
suckers =sum(!Sheet1,B3:B4) [or whatever the proper format looks like)
tootsie-pop =sum(!Sheet1,B5:B6). But what if I have 15,000+ types of "candy"? It would be painful and tedious to put in all those sum fields.
If the user selects, annual bill on the calculation type box:
1. Then I need the spreadsheet to use the figure 365 in cell G14.
2. I also need it to times the figure put into G15 by four.
3. It also needs to divide the readings in cells D9,D10,D11 by how many days in cell D7 and them times by 365 and put the answer in E9.
If the user selects bill to date on the calculation type box:
1. I need to copy the figure in D7 to G14.
2. The number in G15 needs to be divded by 90 and times by how many days in cell D7.
I am still new to VBA. I am trying to use VBA to search given rows for percentages above 0.81%. When it finds cells with percentages 0.81 and above it will high light the cell with red.
Here is what I have done so far ...
I work on a sea going vessel and manage an Emergency Response document. My dilemma is automatically assigning a Muster location based upon data entered in a certain column. I'm having a problem figuring out this Nested IF Statement: Column J will either be blank (no entry), "Fire" or "HRC".
If Column J is blank, column A needs to read "STBD". If column J has either "Fire" or "HRC" column A should read "PORT". See attached Excel file for an example with only 3 or 4 rows.
In cell A1:A10 I have various words.
I am trying to convert them to a number assuming every letter = a number in sequence of the alphabet then is summed together. ie:
a=1, b=2, c=3, d=4 ............. z=26.
So the word Excel would equal 5+24+3+5+12 giving me the answer 49.