DGet Does Not Return A Value When It Should...
May 20, 2009At least as far as I can tell.
So let us say that I have a table that looks like: ....
At least as far as I can tell.
So let us say that I have a table that looks like: ....
SOURCE DATA This is prepared day wise. Each day, dayswise sheet is prepared and therefore 31 sheets are there for the month of May.
DATE
MACHINE NAME
Shift
Run Hrs.
[Code]....
I've received a data report where the brand is in an incorrect format, but can be calculated from data in the list.
I have Brand in the format CRY, but it should be in the format CRYS or CRYW (brand + tag for winter or summer). There are other brands that behave in the same way, e.g. THO should become THOS or THOW, PLA should become PLAS or PLAW, FIN should become FINS or FINW.
I've created a new column using IF statements in conjunction with the departure date to work out whether it was a summer or winter booking, and as a result column C is now populated with either Summer13, Winter13/14 or Summer14.
I don't particularly care if it's a summer 13 or 14 booking, they get reported on together. So I now have the brand alongside the season (summer or winter). I built a database to use with the DGET formula:
Brand
Season
Result
CRY
Summer13
CRYS
[Code] ........
And I used DGET to work out what the result should be in column F:
=DGET($N$4:$P$16,"Result",B1:C2)
This works beautifully for Cell F2, but F3 can't seperate the headers (B2 and C2) from the data (B3 and C3) without tripping over B2 and C2.
I've noticed error 1004 is very broad and switches its name depending on what you're working with. But I have no idea what it means.
This is the line w/ probs:
Sheeti.Cells(y, 2).Value = WorksheetFunction.DGet(InventoryRange, 3, CriteriaRange)
I looked at protection and Macro security and checked my definitions (that work in other contexts).
Does DGET need to have criteria and database in the same worksheet? My project is working between two workbooks.
I'm trying to use DGET to pull coordinate pairs from a list and match them up to a label based on certain criteria. I can't seem to get it to work. I'm also thinking that the way I have it set up, the coordinate pairs might not stay together and that has to be the case. My criteria is multiple cell based too and I don't think DGET can handle that.
For example, In order for a coordinate pair to be labeled "B" the x coordinate must satisfy a condition and the y coordinate must satisfy a different condition. Can I use DGET with the AND function?
Excel offers many ways to use a key to lookup a value (VLookup, Index/Match, DGet, and the rest). What's the fastest way to perform a lookup of a small table of, say, 30 rows of key-value pairs? Theoretically, it would be most efficient to use a branch table (also known as a jump table). See the wikipedia article for branch tables: http://en.wikipedia.org/wiki/Branch_table. Does Excel/VBA have a way to create a branch table for such lookups?
View 9 Replies View RelatedI am trying to create a data entry sheet to enter quotes on. When a quote is received, I click on my "Add quote" button and a userform appears. Data is entered into the userform (frmEntryForm) and returned back to the next available row.
I also need to be able to:
Edit a row by double-clicking it. When a row is double-clicked, data from that row is passed back to the userform, edited and returned back to the same row (to prevent duplicates).Validate that all fields are complete within the userform where relevant (i.e. if the work is not complete or in progress then the "Invoice Number" and "Actual Cost" fields are disabled and blanked to prevent entry (I think this is almost sorted judging by my tests)
I have attached my sheet : 2014 Gatwick Quote Log (Macro Enabled).xlsm
How to return address of the column or cell I selected from Application.Inputbox, not just return the value?
[Code] ....
Is there a way with the following formula to tell it that if value return is = to value of cell above then find return next value?
View 6 Replies View RelatedI am trying to find the largest value, second largest, and third largest value for a column of numbers then return a content value for the adjacent column that contains the name for which each. The problem I am running into is when the second highest has the same value as the highest and also when the third value is zero (n=meaning there are only non-zero numbers for the column). I am using these formulas currently whic work when the numbers are not the same and I have atleast 3 non-zero numbers but I need to modify in order to account for the problem stated above...
View 8 Replies View RelatedHi, I desperately need help with a pivot-table and can't find anyone else who's ever had this problem...I have a very large healthcare data table which for simplicity I will describe as follows. In essence the first two columns respectively are (A) location and (B) month. Let's say the third column (C) is the number of cases of the disease kidpox treated in each health centre during that month. The fourth column (D) is the total number of cases of everything treated during that month in each clinic. The fifth column (E) is the proportion of cases of this disease over all consultations for each health centre and month, and the formula in E2 is .....
View 9 Replies View RelatedI am using the averageIf function and I need the cell to show 0 when then results are not positive. How to resolve my calculation
=AverageIf(a1:a10,">0",a1:a10) displays #DIV/0!
I want it to show 0 only
I'm working with an ancient greek lexicon, and I'm trying to get all the parsing to line up.
I have a table with
A
B
C
D
E
1
ἀκούω
I hear
present
first person
singular
[Code] ........
I want column F to display "present" if the row contains the word "present", "future" if it contains the word "future" etc
My problem is rather similar to [URL] .........., but I'm trying to search the whole row rather than a single cell.
{=(--if(isnumber(search("tfr",c:c)),"tfr","")+(--if(isnumber(search("payment",c:c)),"eft","")","")+(--if(isnumber(search("american",c:c)),"amex","")))))}
I am trying to look up column C and if it contains tfr then return tfr or if it contains payment then return EFT etc etc.
I have also tried using this one as I can use more than 7 criterias. Only problem is that return EFT for all ect where does not contain TFR or payment brings back EFT
=IF( SUM(--ISNUMBER(SEARCH({"Tfr","PAYMENT","AnotherTerm","And Another"},C:C ))),"TRF","EFT")
I need to have a cell value not return - ( of any number) as it effects additional cells calculations the current formula used is =G4/50-SUM(I4:V4) I basically only need it to count down to 0 reguardless of the entries in l4:v4
View 2 Replies View RelatedI have two criteria that need to be met when running a macro to record a timestamp. First - the timestamp needs to be recorded in the first blank cell in the correct column, based on a selection made in a userform. Second - the next timestamp taken needs to be recorded in the same row as the previous timestamp. The times are being recorded in columns D, F, H, and J. So as an example.....
I record the time in column D, but I skip column F and record the next time in column H. That's fine - the timestamps are in the same row (say row 8). Now I record the time again in column D, one row down (row 9).. But this time I don't skip column F.. the time doesn't get recorded in row 9; it records in row 8.
Sub MacroD()
Dim LR As Long
LR = Range("D" & Rows.Count).End(xlUp).Row + 1
ActiveSheet.Unprotect
Range("D18:D" & LR).Value = Now
UserForm1.ListBox1.Text = "Time"
UserForm1.ListBox1.SetFocus
' UserForm1.Show
End Sub
Sub MacroF()
Dim LR As Long
LR = Range("F" & Rows.Count).End(xlUp).Row + 1
ActiveSheet.Unprotect
Range("F18:F" & LR).Value = Now
UserForm1.ListBox1.Text = "Time"
UserForm1.ListBox1.SetFocus
' UserForm1.Show
End Sub
I have a template that aids in forcasting revenues weekly. Dates (each day of the year) runs down Column "A". Accross the top are the dates for each week (52 dates).
Jan 1, 09 Jan 8, 09 Jan 15, 09 Jan 22, 09
Jan 1, 09
Jan 2, 09
Jan 3, 09
Jan 4, 09
Jan 5, 09
etc, etc, etc
As each week passes, new revenues are fcsted for the balance the year. These number are copy and pasted to the corresponding date in column "A" under the corresponding Row (week of forecast). As time goes on, the data postings starts to resemble a set of stairs. What I want to accomplish is, using a formula in one column I want to pull the last forecasted revenue for each date in column "A"
So I have say 1-4 columns that may have an A to describe a student as absent. In the 5th column I'd like it to add all the A's and return a value of 1-4 or if possible... a result that says 4A etc. The column might have another letter in it so i only need to add it up if it has an A in it.
To visualize, i just want to add up the students that have not attended a class.
Sheet 1 column A is comprised of several items. Column B is comprised of their prices. If a value from column A is entered into D1, I want the corresponding price to appear in A1 on Sheet 2.
A B
apples $1.00
pears $1.50
banana $0.60
grapes $1.20
If "grapes" is typed into D1, I want $1.20 to appear on Sheet2!A1.
I have a spreadsheet that is imported from Quickbooks Pro 2012, and it comes over with the following date format:
01/27/2014
I need a formula that will read that and return the value of 25th. To explain, any invoice that is created between the 1st and the 10th of any given month should return the value 1st, any invoice created between the 11th and the 20th should return 15th, and any value returned between the 21st and 31st should return 25th.
Where I'm at:
I'm assuming I'm going to want to use an IF function. Since the digits will always return in the same spot (the 4th/5th spot), I know I want the formula to return the value based on that specific value. I have the following formula that provides similar information/functionality, I'm just not sure how to edit it to return those values.
//=IF(LEFT(D14,1)="I","INV",IF(MID(D14,3,1)="-","CC","ACH"))
I have this formula that does something similar to what I want to accomplish, I'm just not 100% how to edit it to find the value that I want, and then return the value that I want. I guess the biggest issue is that I only understand about 85% of what this formula is trying to accomplish.
For mod formula (1,4) it returns 1. Why is that? I thought it's supposed to show the remainder. In this case there would be no remainder.
View 4 Replies View RelatedI think this may be impossible but what I am hoping to do is in B3 Return the date that is 3 working days before the date in B2. Working days being Monday to Friday in this case. so if the date in B2 is the 16/04/2014 3 days before this is the 13/04/2014 which is a Saturday so i would want the formula to return the 12/04/2014 which is the Friday. Is this possible?
View 2 Replies View RelatedHow do I write a VB Script to take the user back to where they came from.
Example:
User is on Sheet 1 and clicks a macro that takes him to Sheet 10.
I need a macro that is on Sheet 10 that takes the user back to Sheet 1.
BUT if the User is on Sheet 5 and clicks on a macro that take him to Sheet 10
I need that same macro on Sheet 10 to take him back to Sheet 5.
I have a VLOOKUP formula and if it looks and does not find a match it will either put a "0" or a N/A. Is there something I can add to it to leave the cell blank if it does not find a match?
View 9 Replies View RelatedI have this vlookup formula and as you can see when I run it works good. The only problem is that I would like to have formula that it something is not found I don't get #N/A , but rather a 0 or blank.
=VLOOKUP(A6,GL!A$1:E$76,5,FALSE)
LOOKUPDIVDPTCC SALES 46120
010775010775 49,028,244.58
010200010200 3,373,145.76
010700010700 113,667.00
010300010300 14,382.54
011000011000 (1,518,076.05)
010600010600 589.98
011900011900 6,280.31
010900010900#N/A
011100011100 313.08
010750010750 570.90
Rather than explain, it's best to look at my simple example attached. Why am I getting #N/A in cell E6 when I want it to return "one"?
View 3 Replies View RelatedIn C1 I have =OR(B10="MOODY",B10="AIB")
PROBLEM:
B10 (see below) is a Vlookup and it is preventing C1 to return the name of
the agency: AIB or MOODY - Instead I get TRUE or FALSE, No use to me.
Hours Agency
70.00 AIB
80.00 MOODY
40.00
40.00
40.00
40.00
40.00
40.00
70.00 AIB (B10)
I want to return the percent of change in the last two values from a column of values, that gets a daily update. I can use the Index,Match 9.99.....E+307 to find the last value in the column but I am unsble to get the next to last value displayed.
View 3 Replies View RelatedClolumn D will always have a positive value, Column N sometimes will be zero.
using the formula =SUM(N6-D6) returns a negative value if 'N' is zero. I would like to return a blank cell or '0' if 'N' is zero.
Column A lists types of employees (managers, engineers, sales reps, etc.), each of which earns a seperate hourly rate. Column C lists hours worked, and Column D lists the total pay (Cx*Bx).
So column B is missing. Is there a way to generate the hourly rates in column B for each employee type in collumn A, i.e. if it's an Engineer, return $29, if it's a Technician, return $18, without an infinite set of True/Fasle conditions.