I am trying to create a 'Database' of Project Managers where by a skill can be entered into a cell and the VLookup will lookup the Project Manager that has this skill. There is the posibility that there will be more than one Project Manager with the same skill, formula so that the Lookup can return multiple values
the formula i am currently using is; ...
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 am creating a database for Customers and Invoices.
1.I want to select a customer from a drop down list. I only have a few customers.
2.I want to save Invoices to review or make changes before mailing.
Per sample Invoice template.
I am seeking assistance in creating a 'database' type function within EXCEL workbook. I need to create a record of all job applications within our area and only a handful of staff have access let alone know how to use access. All are able to use spreadsheet/workbooks. Would like to be able to have entry page (i can do this fine) but I would like a search funtion that will allow staff to be able to track the progress of applications and add comments etc
View 3 Replies View RelatedI am trying to create a cross-reference database in excel. I am not sure if I am using the correct terminology, so here is what I need to do:
I have three columns that contain text cells, each with different numbers of rows. Now imagine that cell A1 is related to cell B3 and also to cell C5. There are numerous different combinations similar to this. Is there a way to show this relationship between cells in excel. If so, how?
My situation is as follows:
1. I have one sheet with all the information about my customers, such as customer name, address, mobile number and email. I have also added customer reference no...... this could act like a primary key in databases...... This is SHEET 1.
2. my other sheet, lets call it SHEET 2.... is basically a template of my quotation where at the top I need to enter the customer details such as customer name, address, mobile number and email.. Sometimes I have returning customers and I need to enter their details again and again.... it is time consuming.
Initially I would like to enter all my customers detail into SHEET 1.
When I have to create a quotation in SHEET 2, I would like to enter a customer reference number and it should bring all the data from SHEET 1 about that customer and place it into SHEET 2 (quotation template)...
I already transfer my data from database (excel.xlsm) to an old worksheet (excel.xlsx) with a table by using VBA. Now I want to make this old worksheet become a new file with new file name like yyyy/mm/dd/where. Is that possible?
View 4 Replies View RelatedI am trying to create simple leave data base using Excel. Is it easy or complicated?
View 2 Replies View RelatedI've been looking around to find a solution for my problem and as a last resort I have decided to make a post, and I will get straight to the point. I have 3 xl Files of relevance:
1 - is my "database" which consists of multiple sheets with different information in each. Each sheet is correlated with each other by one common ID. 2 - is my "template" which is 2 sheets, which functions as a report. The template serves as the Report which will be printed. The data from each row from the database can fill the spaces in the template.
3 - "the tool" xl file is where i have my macros and the mapping for the which columns from the database belong to which cells in the template.
Basically what I'm trying to get to work is: User opens Tool clicks "create Reports" and the tool should then open the database, pick the range of rows from the database put the data into the relevant cell in the template save the "template" with the info on it, close it and do it again with each row of data from the database. So if I pick rows 4 to 34 in my tool, it should create 30 xls files from the template and fill in the data from each row into each newly generated "report".
I have a template for information that needs uploaded into a database via CSV. This database is then access via a HTML front end. As part of this there is a 'folder structure' with in the front end. This is displayed in a database table (See attached excel file for example data structure)
Currently I have this feeding into a drop down list which is somewhat cumbersome so want to make it more efficient and easier to use. I had toyed with the idea of adding more columns into the excel template and split the string up to populate however this isnt exactly professional looking.
My current thinking is, is there a way of putting this data into a listbox which is easy to navigate? Ideally in a folder tree navigation structure, I know this is possible for a windows folder structure
Folder Structure.xlsx
I am trying to create a database that contains data taken from several work books that i have saved in one folder on my hard drive. These workbooks contain quite a lot ** data, but i only want to extract the data i want to instead ** having to extract it all? these works books are used by several other people as well, so i want it to be able to update as data changes.
I was thinking about using data links, but that only seems to extract all the data instead ** just what i want?
I am trying to create a shared database that tracks training for about 100 people. I've built a workable sheet, but the only way I have to share it over e-mail (there is no office NAS set up, and we're on a military base so we can't establish wifi).
Is there a way to set up a workbook that can be updated on one computer and instantly reflect that information in the cloud?
My goal is to have each team responsible for updating their own form and then have that information update my master tracker.
Its really hard to explain. See the attached.. Basically I have kind of a database of Letters and each letter has several codes associated with it, although each letter is different (a few are the same but that's irrelevant), e.g. letter E means you need to use codes 3.1, 3.2, 3.3, 3.4, 3.5, 3.6, 3.9, and 3.10. That information is stored elsewhere. I simply want a user to be able to put that letter into this box and next to it these codes will populate.
View 4 Replies View RelatedI have an assignment for work where I am required to create a button within an excel sheet that will submit any text in a cell which in this case would be "A9" to a database to a certain field. I must create this using visual basic.
View 1 Replies View RelatedI am currently pulling information from a database into Excel. In column A is a list of dates that vary depending on how many days I decide to pull. On any given day there are options to which car will be driven and it is random and there are attributes associated with that drive such as below. If the car was not driven the data pull inputs "No Data" or a "0". On any given day up to three cars can be driven but it is still possible that only one will be driven. This is shown in the second table below. I'd like to be able to create an automated summary table such as the first table below. The summary table should be organized by date and show what cars were driven and their attributes. I know this seems simple but in reality I have six different cars and up to 10 attributes for each car and will be pulling months at a time. The only thought I have had is to do a for loop that checks every individual cell but I am not sure how to implement that.
Car
Miles
MPG
Car
Miles
MPG
Car
Miles
MPG
[Code] .....
I need to have a end user form that pulls information from a database and simplifies work for our substitue coordinator. Please be advised I have been working on this and can not get the desired results. I need a form that displays the current date and 5 columns of data the Data needs to be in seperate cells for example cell A1 titled TEACHERS and then the sub coordinator could start typing the name of the teacher in a2 and a predefined database of teachers name, class they teach, etc. MATH, ENGLISH and planning period would be pooled and automaticly fill the cell.
I also would need a second cell say cell B to be call Substitutes and b2- b100 to pull data from a database of substitute names and display their information.
In column A of a database I've calculated workdays for all year (excluding weekends and public holidays).
Every day, I update the numerical data of the database, with the facts of previous day.
In another sheet I've made some monthly, quarterly etc., reports for that data.
What I want, is to have:
1) a report every day, with the sum of specific data (say that in column D), for a period starting the first workday of current month and ending the previous workday of today.
2) the total workdays days for that period.
I want to accomplish something like this but slightly different:
[url]
I have the same issue but a bit more complex. In my case I have an additional amount of columns (let's say dimensions). So for the example given (sales per month) I would add two columns for 4 different regions and 5 machine types (just examples).
The output (that would serve as pivot tabel input) would then have to be a 4 x 5 x 12 x 4 = 960 row database table.
I have tried to accomplish it by customizing the given code but that provedto be beyond my powers ;o)
and some 3rd party software [url]and [url] but non of them is doing what I'm looking for.
The code I'm looking for ideally would count the number of columns and unique records per column and construct the database table from that. So I am looking for flexibility in the number of dimensions also...
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))
The following all is set to occur in Excel VBA (i.e. why it's not in Access forum)
I know of a way to create a database using a new ADOX catalog, BUT as there is a dedicated "CREATE DATABASE db_Name" SQL statement., I was wondering how you can execute a SQL statement (as you would to create a table) when there is no file to which you can connect yet.
I have searched for some manner of execute statement in ADOX, but all I can find is Create in the ADOX library.
I tried to connect to the prospective save folder location, but the error prompt indicates that the location is in use or it cannot be accessed.
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 would like to create a spreadsheet with 3 columns: employee ID, last name, first name. Then on the 4th column I would like to enter the employee ID and have the last name and first name auto populate into the 5th and 6th columns. How do I do this?
View 14 Replies View Relatedi've got a bunch of vlookup statements in my worksheet and i now need to create if statements based on them. however, the if statements do not recognize the vlookuped values. how can i get the if statements to work based on what the cell SHOWS and not what the formula is? i realize i can copy and paste values but i would like to retain this worksheet as a template.
I am attempting to create a macro that will carry out vlookup for each row in a data set
For simplicity testing this on a sheet where A1:A10 contains data 1 to 10, and B1:B10 contains a to j, and putting the result of the vlookup into column E.
The code I have written is:
[Code] .......
When I attempt to run this I receive an error message that says, "Unable to get the VLookup property of the WorksheetFunction class."
What do I need to change?
I'm using VLOOKUP to create a Cross Reference tool. CR is my "anchor" page that I'm attempting to tie the remaining worksheets WIT,TEC,COP back to the CR worksheet. I'm able to get VLOOKUP to work on the CR worksheet in columns H & I but unable to get the VLOOKUP to work in column J. The VLOOKUP function is entered but it does not return a value that I know exists in worksheet COP.
View 11 Replies View RelatedI am trying to create a scoreboard or leaderboard for my job. I am using VLookup to pull information from another worksheet and that working fine. And when I get that information I am ranking it. Here is my problem... Some of the data cells that are being pulled using VLookup are blank totally blank so when I try to rank it, it say #Value!. Basically I want that blank cell to be a zero instead so that I can rank it 0 being the lowest score 3in the ranking.
View 3 Replies View Related I am trying to create and calculate player statistics using Vlookup in Excel. This is what I am trying to do:
BCD
5PlayerGoalsAssists
6Jordan Eberle00=VLOOKUP(TEXT(B6,"@"),B13:G14,4) used in C6
7James Neal 00=VLOOKUP(B7,B13:G14,4) used in C7
cell format is "general" in B6 thru B14
Lookup TableCol ECol FCol G
Player NameTeamGPGAPts
13James NealPITT4325
14Jordan EberleEDM1404
Imported Data from Yahoo Sports and have sorted in ascending order a-b-c-d
(pasted as text and than changed to General in Excel formatting)
I have tried using 2 different vlookup formulas structures, but no success. I have tried using numeric reference (i.e using numeric 1 in for both in the lookup table and the original cell) and that works.
I am trying to create a formula which looks in 1 cell to determine 'IF' it states either "BUTT CUT" or "DIE CUT" and then does a VLOOKUP in a table array for each which ever column relates to asnwer to 'IF statement.
This is as far as I have got but even this won't work:
=IF(I15="BUTT CUT",=VLOOKUP(J15,V13:Z22,2,FALSE))
I need to create a macro that can create a dynamic copy/paste loop. So far what I have is horribly inefficient. Each row in colmn A(minus the header) has a unique number in it. For each unique number, I need to paste it based on the number of column headers in row 1(minus column A). So, if there are 20 column headers, I need to copy cell A2 and paste it 19 times in another sheet. Then, I need to move to the next number in column A and do the same thing. Here's what I have:
[Code] .........
You can see that this is not dynamic. If I add another row to my table and rerun the macro, it will not catch it. I've attached a sample file to show you the big picture of what I'm trying to do. The data that I have is in Sheet1, and I'm trying to get it into the format in Sheet3. Rows/columns will be periodically added to the table in Sheet1, so the macro needs to be dynamic to catch that. The data in Sheet3 will always remain, and the macro will add the updated data below the old data in Sheet3.
FC_Macro_Sample.xlsm