I would like to return the lowest non-zero number in the second column (B), based on the value in column A
in this example, 1 should come back
Example numbers
A B
22 0
33 0
22 1
22 2
33 2
22 3
I started out using:
=IF(AND($A$1:$A$6="22",$B$1:$B$6<>0),MIN($B$1:$B$6),"")
But it is not working for me.
I'm new to the DMIN function and I'm having a problem.
Cell A2: Date (rest of column is date format) (heading for the column)
Cell A2: Credit (rest of column is acctg. format) (heading for the column)
Cell A2: Debit (rest of column is acctg. format) (heading for the column)
Cell A2: Balance (rest of column is, equals balance field from previous row + credit - debit for current row, acctg. format) (heading for the column)
Column E: Notes (rest of column is generic text) (heading for the column)
Cell K1: Date (the text "Date")
Cell K2: > TODAY() (the text "> TODAY()") -- maybe this should be ="> TODAY()"?
Cell H2: =DMIN(A2:D1000,"Date",K1:K2)
I'm trying to get the minimum balance for the Balance column where the date is greater than today (lowest balance that I have in the future, which helps me see if I will be overdrafting).
I'm getting a value of 0 in H2. I have also tried =DMIN(A2:D1000,1,K1:K2) and get the same result.
I've got a database with 8 columns and many records. The task is to find the value in column 3 for the smallest number in column 7 with additional criterias for columns 4 & 5.
I've used the DMIN function for finding the smallest number, but I don't know how to show the corresponding value in column 3. I think I should only use DB functions. (I've checked and there is exactly one line with the set of criterias above, so there should be no errors.)
Is there any way I can specify to DGET that I want to get the minimum of a column but based on the other criterias? Or embed DB functions into one another?
I currenlty have a spreadsheet that works well but uses a lot of array formulas and, therefore, is running VERY slowly. The slow speed makes it impractical to work with, so I am trying to replace the array approach with the DMIN function. However, I am having trouble getting the DMIN function to find the minimum value in a column range according to a criteria in the same arrays did for rows.
Here is my problem:
I currently have arrays (composed of dates) in rows and I have converted this to non-array columns. I have pasted a sample of the new worksheet below.
Column D ["Original End"] has dates that are static. Column F ["New Start"] has dates that can change according to user inputs. I want Column G to find the date in a Column D that is greater than the date in Column F in the same row.
In the table below, you can see that I was able to get this to work for cell G3; however, when I pull the formula down, it stops working. Here is the formula I have in cell G3: =DMIN($B$2:$E$15,"End",$I$2:J3).
Conceptually, I want to write the following function in cell G4: =DMIN($B$2:$E$15,"End",">"&F4), where the criteria is 'greater than date in F4' - but this obviously doesn't work.
I'm using the formula: =IF(G28>VLOOKUP(D28,MileageLookup,2,FALSE),G28-VLOOKUP(D28,MileageLookup,2,FALSE),"0") in my "Overage" column[H28] to look up weekly mileage limits for employees and calculate by how much(if any) the "Actual Mileage"[G28] exceeds this limit. If there is no overage for the employee that week, I've asked it to return a 0 so that the next column can use this result to calculate the "Overage Charge"[I28] which based on a rate in the MileageLookup table. Both formulae are working properly.
The problem comes when I try to use the DAVERAGE and DMIN functions on the "Overage" column. Excel doesn't seem to recognize that the 0's are actually legitimate zero values and not just some sort of place holder even though it calculates the "overage charge" properly and recognizes the 0's.
I am trying to use the function DGET() to re-organize the information on the "DbCalc" sheet in the attached workbook. This formula can be found in C2:C5 in "purple. I am referencing the worksheet "Exp Rpt" as a database and match info based on two criteria. I can do this using an array formula like this ={SUM(IF((A1='Exp Rpt'!B$9:B$10000)*(A2='Exp Rpt'!E$9:E$10000),'Exp Rpt'!H$9:H$10000,0))} , however with the amount of data that I need to reference this array is soaking up all my memory. I would usually turn to access to do this, but the person that will be utilizing this tool only knows excel.
View 11 Replies View RelatedI have made a database to record drawing numbers.
In column A is the drawing number and in Column B is the revision number.
I am trying to write a macro that will alert the user when they are trying to enter a drawing that contains BOTH the same drawing number AND the same revision number as one already in the database.
Since drawing numbers will be reused and only revision numbers changed when a revision is entered, I need the function to check both column A and Column B then display a msgbox if there is a match.
I am currently trying to create a database of products for my company. For each product I would like to include an image associated with it. I then want to have on another sheet a place where the user will click an error and be able to cycle through the products. As tehy cycle the associated image will pop up.
What I need to understand is after importing the image into excel, how do I associate that image to a cell so I can reference it in another sheet of the database. I am not concerned with how large the database will get, my pictures are quite small.
I'm trying to lookup a database named database and return a time in column A, based on criteria in cells a1 and b1 on another sheet, A1 would contain a number and B1 would contain a day from mon-fri
eg of Database
A B C D E F G
10:00 5000 Mon Tues
11:00 5000 Wed Thur Fri
and so on
if a1= 5000 & b1=Wed
how can I return 11:00
I have tried index and match =index(a:a,match(a1&b1,b:b&e:e,0))
I've used a countifs, but I'm having trouble doing a sum in a similar way. I have 2 databases and I will try to explain below. I need the to sum the values of database 1 if the second database is >= 20. So the value I would expect on this example would be 900. I would also like to be able to highlight which ones are elliminated. Such as coloring the text red.
Database 1
Eric 100
Jenny 200
Gina 300
Doug 400
Database 2
Eric 18
Jenny 20
Gina 34
Doug 55
I have a database function in which I cannot use an "OR" operator. I can add "+" on to another (Ex. Sum(a1:b1)+sum(b2:c3)). The function itself is 80-90 characters long. So after I add a few together the cell runs out of capacity. I have a analysis that simply does not lend itself to listing out individual functions and then summing them.
View 3 Replies View RelatedI use Microsoft office 2008 for MAC i currently have 4 xls witch contains clients
The one has 11.206 the other 11.971 and the other 4267 people
THe 4th and last list has 3462 and i need to check witch of the people in 3462 are not contained in the other 3 xls
I tried to copy - paste some info in order to be replaced by excel but nothing
I've been browsing through postings for a while now trying to figure out a solution to my excel formula problem. While there is a lot of useful info, I have not found anything that will get me where I need to be. I am hoping that someone here will be able to me out. My project is this.
-A workbook with about 20 sheets.
-Sheet one will be a master list of serial numbers with blank fields for dates, account numbers etc.
-Sheets 2-20 will be used to assign those serial numbers to individuals.
-On each individual's sheet there are fields for dates, account numbers etc. next to the serial number.
-I need the master sheet to look through the individual sheets and insert the date, account numbers etc. into the master sheet.
I can get the IF function to work with one sheet but cant figure out how to do this across multiple.
I want to set up a database which contains customer comments and phone numbers, what I want to do is every 30 days or so, have the cells highlighted so that they remind me to call them.
View 4 Replies View Relateduses CSV files for importing product data, problem is both have it formatted differently. What i ideally need is a master file that i can change whenever a price needs changing or product adding, this feeds down into 2 csv files that are formatted for the relevent softwares and then i can use a macro to upload these automaticly at end of day. ive tried using just excel but had no luck, i tried using access and custom reports but keeping getting errors as page size is 35 fields long and doesnt want to export as a csv,
Using an existing database with nonfixed boundaries I am creating a new one with uniform boundaries.
The simple spreadsheet I will use in this comment isn't what the database actually looks like but represents what I am trying to achieve.
StartFinishAssigned #
0151
15252
25403
40552
55602
60804
80953
951002
Here I have the old data, organised into sections that have the same assigned number. (Note: the assigned number may be the same as the previous cell). I wish to change it into this structure:
StartFinish#
020
2040
4060
6080
80100
It will be organised by the start and finish instead of by the assigned number. Instead I would like the number to reflect the lowest assigned number that occupies a part of that area.Is it possible to create a forumla which would achieve this?
I have spreadsheets supplied by clubs to add data to a database. I am not a spreadsheet user,
Today I have a spreadsheet with the columns A to M repeated in the width of the spreadsheet.
How do I reduce that to one display?
From the data on my database I want a button to export certain columns (B,C,D):
1. that on the (column J) have number smaller than (cell A1)
2. and create PDF's based on a column that has 3 options (AA, BB, CC)
So all the data from database that make 1. true to be exported on 3 PDFs where the first PDF has all the AA data the second all the BB data etc..
Is this possible?
I have a big database that has different name in column A(something like Y1, Y2, Y3, Y4, Y5, Y6, Y7, Y8...). What i'm trying to do is to group them! In group A i have Y1,Y7, in group B i have Y2,Y3, in group C i have Y4, Y6 and etc! I think the solution is something like index...but i never worked with that in VBA! So each name of group A should copy on a new sheet, the next rows should come group B and etc! Is there a way to do that in VBA?
View 11 Replies View RelatedI have the following ....
View 14 Replies View RelatedAdd Project to 'database' I recorded my marco.
View 3 Replies View RelatedI'm generally trying to figure out in my head most of the pluses and minuses for using Excel Worksheets as a database. In My case it means adding data from a UserForm to Excel cells and upon these data to analyze the info from the worksheets to other worksheets like charts, tables etc. Here's my view:
Pluses:
1. VBA and Excel integrate well as a whole, easy to use, well know interface
2. There're many available options to manipulate with the database once it's arranged properly in the excel worksheets
3. Don't need to give extra money software like Microsoft SQL etc.
Minuses:
1. Slower operating because it takes some more memory and processing time to reference the data in the cells. Here I mean operations between the UserForm(s) and the Worksheet(s)
2. You can have DB not bigger than 256*65000
3. XLS files are generally much bigger as a size -> slower work as a whole
4. Maybe (i'm not sure here) there're less tools and options than a real SQL software
What options I have:
1. Use some SQL DB which is free
2. Use Text Files to write and read from
I am trying to find functions that allow me to categorise game attendance by team and day. EG: Out of the list, I would like to summarise the min, average and max attendance for the Broncos on Fri, Sat, Sun and Mon games (and for each team)....
View 9 Replies View Relatedin summarizing my data set. specifically, I have names of organizations listed in once column and in one of the others I have status of organization (open, closed), number of transactions and volume of transaction by months for each organization. In a summary sheet I need a formula that would look up for a specific organization in the long data base and match it with its status and count the number of "opened"(or "closed") organizations matching that specific name as well as sum the total volume of transactions for that specific organization. If I stated my request unclearly i can explain it better or attach a excel sheet.
View 9 Replies View RelatedI am working on a database for a client. The database has a few worksheets with an overview (contents) worksheet. What I am trying to do is create a hyperlink from the contents page to the corresponding worksheet. I can do this no problem...my problem is that if I add more data to the worksheet the hyperlink is lost. I am linking the sheets via cells so if a cell is moved down to add a new cell the hyperlink is no longer valid. Is there a rule I can create to keep the hyperlink and not have to adjust it every time new data is added.
View 9 Replies View RelatedI am trying to set up a database to collect information on students behavior. We need to collect the frequency of 3 things-time of day, behavior, and correction. Can I do this in Excel? How? Is there a way to write a correlation that will count the occurrence of words?
View 9 Replies View RelatedHas anyone ever tried to make a database out of Excel? I know that Access would be a better choice, but i have never done anything in Access and have no clue where to start. i know Excel, but i'm worrying that i will end up having too much data and my program will be slow.
View 9 Replies View RelatedI am trying to use an excel vba program to get data from a database and then use this data in my program. But i have no clue about where to start.
View 9 Replies View RelatedI am very new with Excel and VBA, what i need to do is:
develop an initial log in to open an excel workbook...
and then open the workbook if username and password
1. ask for user name (field should be 50 characters)
2. Ask for a password (password alphanumeric with special characters field 25 characters)
3.Validate user to a salesman data base
if log in attempt fails after 3 attempts, purge buffer, clear log in screen and lock keyboard for 60 seconds)
I have done the whole excel application all i need is this thing to pop up when double click in my direct access to it..
Also save document with the invoice number that in in a cell
and How can I make a cell auto increase the invoice number every time open workbook?
I was wondering if there was any code that would 'lookup' a value in a MS Access database in the same way that a Vlookup formula looks up values in tables in Excel.
Even better, if there was a fuction already written to do this.