Searchable Database On Multiple Criteria
Aug 20, 2009
when it comes to using an excel sheet (which is already built and rather large) for functions which are more suited to database functions.
The excel contains audiotaped medical info in columns:
question
physician name
diagnosis/subject
timecode
label1 (checked box "on")
label2 (checked box "off")
comments
each of these are sectioned by audiotape with headers --
looking something like this --
TAPE 1
diagnosis/subject1
then under that, columns
question | physician name | timecode | label1 | label2
then
diagnosis/subject2
then under that columns
subject/question | physician name | timecode | label1 | label2
then
TAPE 2
with similar info
so each TAPE has numerous DIAGNOSES covered under which there are various questions asked and answered by individual doctors.
(see link to dummy file for visual)
[url]
I need to be able to search on a group of criteria simultaneously:
# physician’s name
# subject
# on-label
# off-label
and return the soundbite in a user-friendly way so that the entry-level desk people (with little or no excel expertise) can retrieve it.
YES, it was not set up to do this, but this is what i've got. so i'm hoping that before i go down too many dead-ends, someone can tell me which is the best way to do this.
Looks like VLOOKUP can only really search on one column and filters would require too much expertise for entry-level employees, but should i look into VBA or Macros. or do i need to import this into Access -- or learn SQL to interface with it on a user-friendly website?
View 9 Replies
ADVERTISEMENT
Sep 14, 2006
I am a DJ and I want to allow my prospective customers to view my music library, while preventing my competition from printing a copy, or posting my library as their own. I think I can accomplish this by having a Java (or similar) interface on a page of my site that allows the user to search songs by title or artist. However, I am lost as to how to make that happen.
A competitor has something pretty close to what I want, but cannot get enough info about how to do it myself when viewing his source code (http://www.houstonkaraoke.com/search-pro/prodb.htm). I would like something a little more asthetically pleasing. My DB is in Excel 2000 and I am running XP.
View 3 Replies
View Related
Jan 2, 2013
I would like to be able to create a reactive, searchable database. I have data that will be broken down into two divisions: Origin and Destination States. I would like to type in an Origin State, then a Destination State, and then have the document provide results for matching data. For example, if company 1 is in the origin state, but not destination state, its data will not be shown in the search result. However, if company 2 is listed in both the Origin State and Destination State, its data will be listed.
Addition - Attached is a small example of the worksheet that I would like to create. In the "SEARCH" tab, I would like to enter the Origin and Destination States. the search would yield all appropriate matches. In this example, if I entered Alabama as the Orign State, and Arizona as the destination, the search would provide the following match:
GreenLine
H&M Bay
Refrigerated Express
View 1 Replies
View Related
Apr 2, 2014
I built a searchable drop down list in excel which will create quick receipts from a huge list of products.
I entered my products in one column and their respective content and price in the columns next to it.
My goal is to create a searchable drop down list which will include all of my products so that when I search and select the product my customer ordered it will ultimately pull the content and price with it to form a receipt with multiple products.
The problem I ran into is that my searchable drop down list cannot be copied to the multiple rows below.
Cell B8 is active.
EFS receipt drop down.xlsx
View 2 Replies
View Related
Jan 4, 2013
I am building a database in excel where I have the number of days across the top (horizontal) and in Cell A2:A300 I list our product lines (that is repeated based on different divisions). Beside each product line in row b3:iv3 I have sales data for each working day.
In a new tab I want to formulate the total of ProductA on day 240.
A B C D E F G H
1 Region Product 235 236 237 238 239 240
2 RegionA ProductA 25 31 15 18 10 5
3 RegionB ProductA 21 13 10 5 28 10
4 RegionB ProductB 21 13 10 5 28 10
5 RegionC ProductB 21 13 10 5 28 10
6 RegionA ProductA 25 31 15 18 10 5
7 RegionA ProductC 25 31 15 18 10 5
In another Tab summary of Product Totals:
Day235 Day236 Day237
Product A 76 75 40
Product B 42 26 20
Product C 25 31 15
I tried many formulas for example:
=SUMIF('2012 Sales'!$C$3:$IS$113,$C1&$F$4)
View 9 Replies
View Related
Jul 18, 2014
i have database where are some data for several shops i need macro which will copy data for exactly that shop which i will choose in summary sheet, for better understanding i also attached excel file.
View 1 Replies
View Related
Jan 22, 2007
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 Related
May 29, 2009
I do have an excel workbook containing two sheets:
Sheet1 contains a list of 30 criteria, every one with its own check box (TRUE/FALSE).
Sheet2 contains a database of items, which columns contain the 30 criteria mentioned above. (So sheet1.criteria1 can be the only value of sheet2.column1)
I am trying to program a macro, which can be assigned to kind of a "submit"-button in the first sheet. Its purpose should be to open a new third sheet and listing all the items from sheet2, which contain values checked=TRUE in sheet1.
View 10 Replies
View Related
Oct 2, 2007
Cell A1 is HEAT_CODE
Cell A2 is W5H
In cell
=DMIN(Database,"Cost",A1:A2) return the proper value
I need something like the following so I can use fill down.
=DMIN(Database,"Cost","HEAT_CODE"=A2)
the above returns #VALUE! error.
View 6 Replies
View Related
Apr 16, 2012
What I am trying to do is extract all the data from a excel database based on criteria. When i specify the site and month I need to pull all the corresponding data and write it to a worksheet. Below is an example. I am not allowed to show all the data it is confidential.
Site Date Operator Month
Chicago 12/3/11 Daniel December
San Jose 4/8/12 Mike April
New York 4/8/12 John April
View 1 Replies
View Related
Feb 1, 2007
I have a database that has outgrown excel (over 77,000 rows now) so i now have it in Access. I want to replicate the following situation I used to have using lookups in excel with a query to the Access database: Paste a list of identifiers into an excel sheet. Values corresponding to the list are returned from the database in adjoining columns. The list can be from 10 to 300 cells long an returns data in 14 columns. I have had a go with the Import external data wizard which generates a query but have not been able to work out how to base the query on a list of cells in excel and have the query return values for each of the identifiers in the list.
View 2 Replies
View Related
Apr 14, 2014
I have database where I am searching for duplicates. The values I want to compare are not in the same column. For example:
A
B
C
D
E
[Code].....
I need to remove one of the 10 year old Yankee teams but not the 11 year old Yankee team.
View 2 Replies
View Related
Jan 22, 2009
I have 100 or so workbooks named A1234.xls, A1235.xls, A1236.xls etc.
I want to gather information from the same worksheet and same cell in each workbook.
I want this information in a separate workbook named Master.xls
In the Master spreadsheet I have the workbook names in column A e.g.
A1234
A1235
A1236 etc.
In column B, I want the information form each of the 100 workbooks. For example, from "sheet 1" Cell C2. This is the same place I want the information from in each workbook.
Is there an easy way of doing this with a formulae rather than a macro. For example, in the Master spreadsheet column B1 formula would read =[A1234.xls]Sheet1!$C$2 ... and then can you drag this changing the filename according to column A in the master spreadsheet?
If not can a macro be used? I have only started looking at macro's and my knowledge on them is very basic.
View 9 Replies
View Related
Aug 24, 2009
I have created a query in an Access database which returns multiple results. I need to be able to run this query within Excel, but only showing the total of the results in a single cell.
I also need to run it multiple times on the same sheet using different dates. The Access query asks for a date each time it's run
I have only found ways to retrieve the entire results of the query into Excel based on the settings within the query.
View 9 Replies
View Related
Jan 9, 2007
Is there a really good tutorial for laying out multiple database queries in excel? Or even just for automating reports between Excel and Access? Here is my situation: Monthly, Quarterly & Annually I report on performance standards such as: duration, best practices, cost-benefit analysis, etc. The information is pretty standard and is pulled from Excel Reports. Access is used to filter this information by appropriate month, quarter, year, and/or data type. In access I calculate the performance averages, max and min. Then this is in linked back to Excel to update the charts and summaries. My concern is how much time I spend rebuilding this system every month (about 4 business days)
View 3 Replies
View Related
Jan 23, 2014
I need the searchable list box to return data one column to the right of the "searched" column data.
This works mostly with Data Validation.
Named range "validationlist" with "=OFFSET(Choice!$AC$4,,,COUNTIF(Choice!$AC$4:$AC$14,"?*"))"
D9 has a drop down control. Enter the cell and type "br" and click the drop down arrow. Mouse to choose an item from the list box. If you chose "brown" , it will be entered in the cell. The returned item should be "banana" from the other column. How to do this?
Once working the control needs to be used across the row in more cells: E9, F9... all the way out to W9. And there has will be a Lookup to pull data from elsewhere above D9.
This worksheet ultimately needs to be uploaded to Skydrive and used as a collaborative worksheet as a 'web app'.
See attachment and formula output below.
[Code]............
searchableListBox-EF1.xlsx‎
View 2 Replies
View Related
Mar 31, 2004
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.
View 4 Replies
View Related
Nov 7, 2008
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))
View 9 Replies
View Related
Oct 21, 2006
I am trying to solve a problem. I am currently using this formula
= SUMPRODUCT(--(Sheet2!B2:Sheet2!B300="MARKETING"),--(Sheet2!D2:Sheet2!D300="200612"),Sheet2!E2:Sheet2!E300)
This formula works for me as it is but I would like to add more months to 200612. I want this to also be 200701 and 200702. In another cell there will be up to 10 months. Is there a way to do a Vlookup or something that will look up these months in another table, rather than keep typing them out in the formula?? Otherwise my formula will be very long.
So the info looks like this in excel
MARKETING 200612 -10
MARKETING 200701 -25
MARKETING 200708 -50
ECONOMICS 200709 -30
The info goes on and on. The two variables are the MARKETING column and the month column. My problem is that I would like a seperate table that can be the months. So 200612 and 200701 is one table, and 200708 and 200709 is another table. The table changes often so I dont want to mess with the formulas, rather a table.
View 5 Replies
View Related
Jun 3, 2013
I have a 5 major categories of products e.g. Ketchup, Hot sauce ,mayonnaise , pizza sauce etc.
Now each of these individual categories are further divided into number of of subcategories based upon the the sizes and brands.
I want to create a sheet (One sheet) where I have to record production of each product on daily basis.
Like for example if I am producing ketchup, I select ketchup from drop-down list,Now in next column I have to select production size from predefined sizes i.e subcategories. But it should also be from drop-down menu and it should only show me the subcategories of my selected product like ketchup .I think this has something to do with data validation but I am not sure how this model will work.
View 3 Replies
View Related
Oct 17, 2013
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
View 5 Replies
View Related
Mar 24, 2014
I've attached a sample workbook to show what I am trying to do. I would like the formula to say "if Sheet2!A:A is "MON" and if Sheet2!R:R matches Sheet3!A:A, and if Sheet2!I:I doesn't match any of the values from Sheet1L:L, then I'd like the sum of Sheet2!F:F. It seems pretty simple but I've tried a million different variations of SUMIFS, SUMIF, IF, AND, etc. and I can't figure it out.
View 1 Replies
View Related
May 18, 2014
Macro for AverageIFS, with multiple criteria in the same criteria range....
View 9 Replies
View Related
Jul 30, 2008
I have a spreadsheet which lists letters issued, the date issued and the potcode. I need a formula that counts, on a weekly basis, the number of letters issued to postcodes in Sutton and Bexley.
I have a count sheet set up on one worksheet and the list of postcodes applicable to each area are listed on another worksheet. I have been playing around with sumif, sumproduct etc, but these don't seem to work as I am pointing the formula to a list of postcodes and not an individual postcode. For example, the formula for one week needs to tell me, the number of letters issed to Sutton between 26/07/08 - 01/08/08. This is what I tried -
=SUMPRODUCT(('MU63 NC'!$F$2:$F$10>=COUNT!B$2)*('MU63 NC'!$F$2:$F$10<=COUNT!B$3)*('MU63 NC'!$D$2:$D$10=Sutton!$A$2:$A$50))
But I get #N/A - if I change the last refernce to a specific cell instead of a range it works, but this will make the process very lengthy as there are lots of postcodes!
View 5 Replies
View Related
Mar 18, 2013
I maintain the data flow at my work. We send and receive the data using excel files with specific formatting that I then upload to the database. Each time I send or receive the excel file I must log them, this is what my code question refers to.
I use RDBMerge to merge all the contents of the 100 plus excel files into one worksheet. The first part of the macro cleans up the merge data for use in the log (i have attached an example of the clean data and finished log).
The blue shaded area of the "Raw_Data" is what the clean data looks like, the yellow column is what current macro records for each record.
As you can see by the example the Raw_Data is only two files LL_LLL_BOB_ToLLLLL_20121228_01 & LL_LLL_BOB_ToLLLLL_20121230_01, each with more that one record.
The log code in column "H" Is based on this criteria:
First Letter of the Unique ID in column "E" - O, M, or L
Program Type in Column "F" - U or R
1. O-U = U
2. O-R = RU
3. M-U = U2
4. M-R = R2U
5. L-R = R
You will note that Columns G-R of the "Log Sheet" correspond to the "Record Type" found in Column "G" of the "Raw_Data" sheet.
This is the area where my skill at using scripting dictionaries fails.
The results for the log list each file only once, but the log code for each corresponding "Record Type" in columns G-R of the "Log Sheet" must contain each unique instance of the code. In other words
if LL_LLL_BOB_ToLLLLL_20121228_01 contains an O-U with an "A" Record Type and an M-R with an "A" Record Type; then, on the log sheet there needs to be the codes "U/R2U" in the cell intersection of the LL_LLL_BOB_ToLLLLL_20121228_01 record row and "A" column (which is column "G")
So, If the File contains one of each code for each Record Type the corresponding cell must house one of each code separated by a "/" without any spaces. This means the cell value could no code, or one code and all the variations in between to all five codes. Also, for ease of human reading the log codes should be concatenated in the 1-5 order that I listed them in (U/RU/U2/R2U/R)
Here is my code so far.
VB:
Option Explicit
Sub test()
Dim dic As Object, a, i As Long, rng As Range, e, w, n As Long
Set dic = CreateObject("Scripting.Dictionary")
[Code].....
View 1 Replies
View Related
Aug 5, 2012
I am using MS Office 2010. I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---
=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$R1"),2*(AND("'"&$H$1:$H$43&"'!$E1">"'"&$H$1:$H$43&"'!$F1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$S1"),2*(AND("'"&$H$1:$H$43&"'!$G1">"'"&$H$1:$H$43&"'!$H1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$T1"),2*(AND("'"&$H$1:$H$43&"'!$I1">"'"&$H$1:$H$43&"'!$J1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$U1"),2*(AND("'"&$H$1:$H$43&"'!$K1">"'"&$H$1:$H$43&"'!$L1"))))
but it returns a value of zero each time. Clearly there is an error in the formula.
Here is some background:
-- $H$1:$H$43 is a block of cells that has the names of the sheets in the workbook
-- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing.
In the entire workbook, I want to add 1 (counting function) only when:
R1=2 AND E1>F1 or
S1=2 AND G1>H1 or
T1=2 AND I1>J1
U1=2 and K1>L1
on each appropriate sheet in the workbook.
View 1 Replies
View Related
Aug 31, 2012
I have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:
TABLE 1:
Project
Benefit Type
Delivered or Enabled
Benefit
PJ1
Financial
Delivered
Saving of $4M over 24 months.
[code]....
I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.
TABLE 2:
Financial - Delivered
Financial - Enabled
Tech - Delivered
Tech - Enabled
Green - Delivered
Green - Enabled
[code]....
So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:
Continued maintenance of hardware.
Increased capacity.
View 1 Replies
View Related
Jun 29, 2014
I have a directory which contains many files, they are all names based on their locations. eg. Burwood-File1.xls,Burwood-File2.xls,Burwood-File3.xls etc
I have a master that which will contain the branch in the first column.
I have defined the directory location in a separate sheet as well as email template.
When I click on the Send Email button I want ti to attach the files that match the Branch name.
The Branch list will constantly grow.
View 2 Replies
View Related
Feb 24, 2014
Im struggling to find a formula to populate cells based on values to be checked in two fields (ICODE, COMPANY) as shown below.
In the example here, all records where ever the ICODE = 49, i need to populate the fields(columns) AD1,AD2,AD3 or AD4 with the product name of the same company where the ICODE <> 49. There could be between 1-7 products per company.
CURRENT SHEET
ICODE PRODUCT COMPANY AD1 AD2 AD3 AD4
89PENS ABC STATIONERY CO LLC [code]....
The attached example file may be a better alternative to view this problem.
View 2 Replies
View Related
Feb 1, 2012
Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)
e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated
Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.
e.g.
AAA blue round elongated
BBB orange
CCC smelly
View 3 Replies
View Related