I've been working on this project for my work and having a little trouble getting it to work. What I want to do is have E3:E32 populate the last 30 times the cycle (J3) was run on furnace(J1) (These are drop down lists). I originally had it run the last 30 dates, but since some cycles don't run every day the graph was missing dates. I want it to be able to just fill in proper HRC/HRB readings from the sheet "DATA" which can be found in column "U" on that sheet according to which furnace is selected along with the cycle.
I want to create a sort of complex logging system for my work.
As of right now i use a simple temple. Column A has the job number, column B has drawing numbers. Each cell in column B has multiple drawings. So for example cell B2 might contain 101, 102, 103, 104 and so on or something like 101...104. Depending on the situation. The ... means drawings 101 thru 104. What i want to do is have a second sheet that has Column A be the job number but column B has just one drawing #, so an example would be . A1 = 10939 and B1 = 105, 107, 109..111. Sheet 2 would then have A1 = 10939, B1 = 105, A2 = 10939, B2 = 107, A3 = 10939, B3 = 109, A4 = 10939, B4 = 110, and so on. Is something like this possible.
I have used the search function but can't seem to find a suitable solution.
I'm trying to figure the best way to extract 12 continuous months worth of data from a table and plot on a graph, each month with a separate plot on the graph (i.e. not the summed total but rather, a line graph with 12 points). The data is based on two, user-generated inputs, that are produced from a dropdown box.
I can create the Data Validation rules, use a Match formula to produce the reference for an Index formula and i have created a bunch of Named Ranges to work with and can easily generate the FIRST value to plot.
E.g. INDEX(DATA,ROW,COLUMN). After the first value, I'm stuck on how to generate the rest.
I have attached my problem for reference : Excel Problem.xlsx‎
I must graph a series of data points in an X-Y scatter point chart multiple times, then fit every type of trendline to that a seperate graph. Using VBA, I must then extract the R Sqaured value from each trendline, and find the best trendline suitable for the job.
How to extract the R Sqaured value from the chart though.
I have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.
In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.
Please see the attached sheet, Im trying to get it to populate the first page with the column data in the datasheet1 page by using the dropdown menu in N1. This is for when people ask questions about the data, I can quickly and clearly show them the differences from one month to another
I am trying to link one table 1 with another table 2, and these are in different tabs.
In first table (1) I have to do some calculations, and in the second table (2) I have contacts (name, fax and contact name).
So I want from first table once I write the name of Trade (which I did with data validation to pick only the trades that are in table 2 from A1 to A50) to populate table 1 - columns B with Contact Name and Column C with Fax # and all these data should come from table 2.
I tried with Vlookup but the problem is that I can fill only column B and I need to fill column B and C in the same time?
Is this possible in Excel or the only way is to go to MS Access?
Ok so I have made a self populating Data Table to show me which employee is on what shift that updates daily from our roster.
This works and it's brilliant, however I want to steam line my finished product further, but haven't been able to find an easy solution.
I have several employees per position and my data table currently self populates so that every employee has their own line. I want to populate it so that all employees with the same position all apear in 1 row across the columns under their current shift.
So this is what my end result currently looks like
Table 1POSITION SHIFT 1 SHIFT 2 SHIFT 3
POSITION 1 EMPLOYEE 1
POSITION 1 EMPLOYEE 5
[Code] ......
This is how I want it to look.
Table 2POSITION SHIFT 1 SHIFT 2 SHIFT 3
POSITION 1 EMPLOYEE 1 EMPLOYEE 5 EMPLOYEE 8
[Code] ......
We have upto 250 employees and upto 50 positions so you can understand why I need to stream line the table.
I can not change the roster as it is our orginal data as requires to be set up as it is. When adding a new employee into the roster I must enter them into there designated crew. "inserting a row midway down the original data".
If I use the CONCATENATE formula and add a new employee into the roster "half way down the original data" then it throws out what the CONCATENATE formula is looking up. It looks up the same cell but the names have all moved down by a cell therefore the populated information is wrong.
I am hoping to make my end result look like Table 2 even if it means adding another spread sheet in somewhere between the roster and the end product.
Realisticly I want a Pivot table that produces names not a tally of employees per shift.
I need to run multiple queries and insert their results into Excel cells. Each query will return only one result (a number, or a null). What is the best way to accomplish this?
Below is an example of the code I'm currently using. It does return data to the cell specified, but I'm guessing there's an easier way to populate multiple cells with the results of multiple queries.
In lack of database experience, I am using a hidden Excel sheet for data and several other sheets with referenced data. I am populating the data sheet from an inputform and inserting new references on save. This works pretty good, but when the data has to be updated, I've currently used the before doubleclick event and hereby used the activecell for reference, but now the data is on a different sheet!
I am trying to populate a text box in a user form when initializing the form. I have reviewed many posts in this forum regarding this problem, but have been unable to resolve. My code looks like this:
VB: Private Sub frmFeed_Initialize() ActiveWorkbook.Sheets("Log").Activate Range("A1").Select
I am trying to populate a large data table with data sourced from multiple tabs.
Each of the tabs is, for the most part uniform.
They have column labels of "Invoice Number", "Schedule Dates," and "Amount". Their cell references are A6, B6, and C6, respectively.
Is there any way to congregate this data into one massive data table? It doesn't matter the order of the data table. I will be using sort/sumifs/pivot tables to analyze the data.
I cannot copy and paste as there is too much data that changes on a daily basis.
I am trying to auto-populate a table with data looked up from another sheet. The functions I have used are: Data Validation, vlookup, ISerror and if functions.
Cell B4 in sheet2 uses Data Validation to pick data from sheet1 (in this case "tax") and auto populate the table with the information- Job type, Name, employee type and Job title. If i change from Tax to HR, the table should get auto-populated with the correct information.
I have setup vlookups but I am unable to get it working. I am a novice at using vlookups (I learn it just 2 days ago) and am struggling to get it work. I am not even sure if vlookup is the right tool to get this job done.
I have a unique task i am trying to achieve (when i say unique i mean i am out of my depth). I am trying to take specific data from a couple of different sheets to populate other sheets whether it be copying the text of fill cell (color)
I need to have totals from individual worksheets automatically enter into cells in a master spreadsheet.
What I am doing is keeping track of donations collected from individual departments - each on their own worksheet. I would like to have the totals of each page automatically enter & update onto a master worksheet that would show the totals from each dept and then give me a total of all those.
I am not that well versed in Excel. I have been able to set up the individual worksheets and the master.....but can not figure out how to accomplish what I want with the Master tally sheet.
On the individual worksheets I also want to set it up to give me the average donation per person. So if I total the # of donors and the total $$$ amount...what formula do I use to get the average?
I want to populate a Data Validation based on values in another cell on another worksheet--but, I want to populate it with the values stored in the cell right next to the cell. http://i32.photobucket.com/albums/d3...n/untitled.jpg. would be an example....
What I want my Data Validation List to do is to look at column B and wherever it sees a certain color--for example, red, I want it to put the corresponding value in column A in the list.
I need help to create a formula that would grab data from one sheet and populate another sheet based on the employee that is selected from a drop down list.
Attached is a draft of what I am looking to do.
Basically this would allow us to enter employee variances from several employees on one sheet and get a detailed break down of their history on another sheet.
A user has an excel document and there are 5 specific cells they need to populate. The data is in one of our SQL databases. Is it possible to create a new copy of that excel doc with those fields populated for each record? There are around 2000 records they don't want to manually populate each one.
I have a workbook that contains data on a different sheet that I would like to populate on a different sheet by selecting a value from a drop down list. The format of the data is identical just different numbers. But are broken out into different projects... I am incredibly new at this and just barely learned how to make a drop down list.
I am attempting to populate multiple rows in one column with data from another table. I need to get the correct street address using multiple values, i.e. first name, last name and city, as some of the names double up.
Is there a way to do this? I have pasted below an example of what I need done as reference.
I'm trying to populate a table based on two different criteria in a another table. On is based on a date and the other the number of a unit. I'm trying to get the data in sheets Week 1 and Week 2 into the format in sheet Example. Is there a way to create a series of functions, filters, etc that I could use to create a macro to do multiple sheets or at least a whole sheet at once? I've been trying to think through it with my limited knowledge of filters and functions
Here is the file with an example and I'm using Excel 2007: Example.xls
Column M of Sheet 1 contains either "y" or "n". I'd like Sheet 2 to populate with data in cells A-D but only for rows where column H = "y". I can figure out the condition easily enough: =IF('Sheet 1'!M2="Y",'Sheet1'!A2,"")
Repeat for each column to display. But I'd like to suppress the blank rows for both display purposes and the possibility of using in a Word mail merge.
I'm working on a workbook with several names for every month and I'd like to populate the names from the different worksheets to have a list in another worksheet to make a summary avoiding duplicates and making sure every name is captured.
I have attached a sample sheet....populate_2013.xlsx
I have built a data entry form (Input tab) and have code populating a table (Table tab). Currently both are in the same book, but I would like to house them in separately.
What is the proper VBA syntax for this? Will both books need to be open in order to populate the table?
The 'real' data is hosted on sharepoint and is getting darn big. The result is problems opening the doc and looong save times. My thought was to have the secretaries doing data entry open a single sheet (Input Form) which will then populate the data in another sheet. I'm dreaming, aren't I...
I know Access would solve everything, but for cost reasons I am stuck with Excel.
I have a big database of customers, each one has a unique reference number. They are spread accross a bunch of different tabs (21 in total to be exact)
One issue I have had is staff entering a customer whos already in the database, causing a duplicate entry. I dont need excel to tell us where the entry is, just to give some kind of indicator it already exists. I thought I may be able to use data validation/conditionality to turn the cell fill Red when it already exists in the data base.
The reason I think that method would be best, is that the sheets that data is entered on are seperate that the master sheet then pulls the data through from. Its the master sheet that would need to indicate a duplicate has been entered, as thats the only sheet where the entire database can be viewed.
For my project , I am using Excel as a frontend and Access as a backend. Now, I want to write the code in workbook_Open event that will copy data in Column L,M,N,O from Access table. The query should select data from Access table where Date=Todays Date. All the Queue Names and corresponding Queue Numbers are stored in Sheet2 of the attached workbook. In Access table , there are three fields named as Type,Type1,Type2 which make up Queue numbers.
In Sheet1 , There are Queue Names and we have to store Total Batches,Totl Envelopes,total Documents and total Pages for the corresponding Queue Names in Sheet1. SO for this we need to check the Queue Numbers of the Queue names in SHeet2 but in table Queue Numbers are equal to Type & Type1 & Type2.
I have a complex report with thousands of lines I am trying to clean up. In order to do this, I need VBA that recognizes specific text data starting after row 9. When it sees this, it will need to select and delete the 2 rows above it, the row it is in, and the 6 rows below it. 9 rows in total, some of which are blank. The text will repeat every 47-50 rows, but I cannot say exactly when it will repeat again. When it is repeated, I need the 9 rows deleted. At this time, there is only 1 column in the report because I need to split out the data in the cell into adjoining cells. Since I have not solved that yet, I need to just delete the entire row.
The text is written with spaces between each letter and a double space between words. It reads, "B I L L I N G M A S T E R I N V O I C E S E T U P L I S T I N G"
In excel, I have created a table containing macro pull-down menus and text boxes.
The question I have is this:
In the properties table for my pull-down menu, I have the “ListFillRange” filled with the correct range of cells containing the data that I wanted stored in it.
The problem/solution I am having/wanting is to have not just one selection present, but many (to show up in the text box directly below it) since there will be some data that have many sub-data that need to be visible for the person who will be testing whatever…
All in all, I would like to know if there is a code that I can write in the Visual Basic window or the macro “view code” window that will allow the text box below to be filled with the information that is clicked on from its corresponding pull-down menu. Is this possible?
10/05/0808:30:00 AMValid Card EntryLamDoor 1 10/05/0808:31:00 AMDoor Leave Open Door 1 10/05/0808:32:00 AMDoor Closed Door 1 10/05/0808:41:00 AMValid Card Exit Yap Door 1 10/05/0808:46:00 AMValid Card Exit Lam Door 2 10/05/0809:14:00 AMValid Card EntryLam Door 2 10/05/0810:18:00 AMValid Card Exit Lam Door 2 10/05/0810:19:00 AMValid Card Entry Yap Door 1 10/05/0810:40:00 AMValid Card Exit Yap Door 1 10/05/0810:42:00 AMValid Card EntryLamDoor 2 11/05/0808:30:00 AMValid Card EntryLamDoor 2 11/05/0808:20:00 AMValid Card EntryYapDoor 1 11/05/0810:20:00 AMValid Card Exit Lam Door 1 11/05/0811:40:00 AMValid Card Exit Yap Door 1
the above is the data i need to work with (dates are in dd/mm/yy). i need to find a way (a macro, preferably) to extract the date and time corresponding to one person, and paste it into a new sheet, e.g. at sheet 2 (renamed to "lam") should be
10/05/0808:30:00 AM 11/05/0808:30:00 AM 10/05/0808:46:00 AM 11/05/0810:20:00 AM 10/05/0809:14:00 AM 10/05/0810:18:00 AM 10/05/0810:42:00 AM
