on the "all data" sheet, there are two rows that have a Y for file missing - these are to be marked, so that they may be focused on finding their file
currently, i am doing as such and then filtering by that column to print out a list, and would like a simplified solution (populate sheet "missing") with just those rows who have missing files - so that another user may easily view such data without having to fondle around with the master list
I have a long row of data with words/numbers in each cell. Whenever I see a cell starting with W, I want to add up the value 2 cells to the right of that cell starting with W.
For example, if these following 9 cells in the same row read W1, 90, 100, M2, 90, 100, W2, 90, 40, whenever the cell starts with W, I want to sum up the value 2 cells to the right of that cell starting with W. In this case, I am adding up the values 2 cells to the right of W1 and W2, which is 100 + 40 = 140. How can I do that?
I'm trying to make a spreadsheet that looks up values for a number of individuals and if they meet certain criteria they are collected in a box.
It is for a class of children's test results. I would like to be able to write the results each child got across the page (names at side, headings across top). If a child gets, say, level 2a in his test, his name appears in a cell elsewhere. I can do this with an 'IF' statement... Thing is I want to collect all the children who got that level in the other cell. The cell might end up with 10 or 12 names in it as a result of their scores being looked up. I would have cells for all of the levels, so the children can be automatically grouped together by attainment.
I have pivot tables with one row field and two or three data fields. I want to be able to quickly hide rows that do not have entries in *all* data fields.
I thought of auto- filtering my data source, but don't think that would be practical as I want to have several tables powering off of the same source data (with many columns), and also the data source is auto-filled with API... Is there a way to traverse the rowfields with vba and hide them if there isn't an entry for every data field? Or a better way than both of those?
I think that this is probably rather simple, but the answer is eluding me, I cannot find anything that helps online either. I have a set of data that looks similar to the sample below, however it is quite a bit longer. I would like the 'Effect' as the X axis, with the 'Score' as Y. This basically shows three columns, which is fine. However I would also like to use the 'ID' as the label against each plot point and I'd like each plot point to look the same.
The problem I am having is that to get the labels to show, I am having to add each series manually, which is rather time-consuming. Is there a correct (faster) way of doing this as I think I'm missing something obvious!
I am currently working on a spreadsheet that records data from 11 different locations, with each location having it's own page within the workbook.
All of the pages are identical in terms of how the data is stored, the only difference is literally the name of each page (i.e. the location).
What I would like is to have a master page that then pulls through the relevant data, dependent on which location I choose from a drop down list on this page. As each individual page is identical in terms of lay-out, one formula will work for each page, with the only obvious exception is the page reference embedded in the formula. I know how to do a standard lookup for various figures, but I can't figure out a formula that would work with 11 different options... Is there any way to accomplish this?
I am searching for a formula or simple macro to select text data from column named "SM NAME" to corresponding worksheet. I need this to populate as many worksheets as there are SM Names. ( there are usually 10 SMs)
For Example in the Master Data there are Several SM names listed. I need to extract the "ID" and "Agent" columns in the Master and populate into the workshhet with the Approriate name tab. The Master list changes regularly.
I use excel and would like to know how to copy a large volume of address data but at the same time filtering out irrelevant data placed under each other in a row, in this case, air compressors air conditioning web address etc ( see below for example). I need the first 5 lines only. The rows of unwanted data are irregular i.e some have 10 lines, others 5 , and others 2 or one line which makes using a formula difficult as there is no consistency. The data eventually need to be placed horizontally in columns to be compared to other address lists. To make matters worse, the text data has been merged and wrapped.
I have a spread with five tabs. Each tab is a person's job responsibilties for numerous properties. Each person works with a property called Orange Avenue. I want to create a tab for JUST Orange Avenue items but want it to automatically pull any and all info from each tab where that person's Ornage Avenue duties are.
I'm trying to convert this code so it transfers the data without the input box being needed.
Dim response As Long Dim strLastRow As String Dim rngC As Range Dim strToFind As Variant, FirstAddress As String Dim wSht As Worksheet Dim rngtest As String Application.ScreenUpdating = False
Set wSht = Worksheets("Transfer Sheet") strToFind = Application.InputBox("Enter Your Initials") If strToFind = False Or strToFind = "" Then Exit Sub
With ActiveSheet.Range("H2:H5000") Set rngC = .Find(what:=strToFind, LookAt:=xlWhole) If Not rngC Is Nothing Then FirstAddress = rngC.Address
Do strLastRow = Worksheets("Transfer Sheet").Range("A" & Rows.Count).End(xlUp).Row + 1 rngC.EntireRow.Copy wSht.Cells(strLastRow, 1) Set rngC = .FindNext(rngC) Loop While Not rngC Is Nothing And rngC.Address <> FirstAddress
I have to workbooks and I want to populate one of them with data from the other. The receiving document has the fields Account and SubAccount which I need to match up with the account and subaccounts in the other workbook.
However, in the other workbook, the line looks like "1000 Wages 000001" (which is account, name, subaccount). I can't find out how to have my receiving document scanning the providing document and when it finds an account and subaccount (in the above string) that matches the account and subaccount in the receiving document, to bring over the data.
From the attached spreadsheet I need to transfer all the rows where the issue status in column T is either ongoing or open. Now I know how to transfer them over if row 1,2,3 etc was going into row 1,2,3 etc into the new spreadsheet but as not all rows from this sheet is going over I need to now how to transfer data so in the new spreadsheet it just follows down rather than have spaces in it. i.e. if on this sheet I need to transfer data from rows 1, 5, 10, 15 on the new one they go into row 1,2,3,etc
I have a sheet with 1250 data. All are in Column "A" I want to print them out and therefore would like the data also in Column "B" and "D" in order to save printing paper and make it easier to search. Since the data is alphabetical sorted, doing it manually is a big job.
I have a new spreadsheet that I made to give us the break down of each invoice we create. what we want is to see each job that is done (by code), the amount we charge per each, the cost of goods per each, and the profit margin. In this spreadsheet I have two tabs. in tab one it has the form I made that has a place to input the data that will change with each invoice as well as a section that is broken down into columns. In these columns I have it listed this way. CA=Job code, CB=Job Description, CC=how many, CD=Charge ea., CE=rate total, CF=COGS ea., CG= COGS total, CH=profit
I have the formula done for all of the math the get the totals. My idea was to make a second tab. In tab 2 I have everything listed in columns that will go into tab one. It is broken down like this. CA=Job Code list, CB=Job Description list, CC= charge ea. CD= COGS ea.
What I want to be able to do is when I type in the job code in the first cell under the column A on tab one, it will bring over all of the info from tab 2 and put it in the right spot for that code. So if I typed in say TT001 in that cell it would bring over the description matching that code and put it in the cell under B, along with the charge in column D, and the COGS under column F. And I want it to be that way for each code I type in and it will be different each time I fill this out. How do I explain this part? Ok so I a new invoice made its # 22. I take this spread sheet and do what I want and get all the totals so I print it out. I then want to be able to clear all the info and or just type in a new set of codes that will be on invoice # 23 and then #24 etc.
So what I am saying is the codes will be different each time I fill it out. that's why I have them listed on a different tab and will just put them in tab one long enough to get the total and print it out. Is that clear as mud? I will stop here and wait and see what questions I get back.
I have a program I built and need to transfer data from a form, from workbook "C" to another workbook "W". The workbooks are open by different users . 90% of what I locate on the Web pertains to worksheets and what don't, does not work. The folders are stored in my Public Documents.
How to transfer data from one workbook that's open to another in a macro form. I included two sample data workbooks I have. Sample will have the macro inside it and will already be open for the other portion of the macro that will be running. I already have code that will open up the Sample2 file. I'm trying to take specific data from Sample and paste it into a summary sheet in Sample2. In Sample i will be cycling through roughly 30 sheets to find this data but I only included one because I just need to know the basics on how to make this work and then how to modify it.
I need to transfer a large amount data (~2,5 million lines of data) from one spreadsheet to another while making sure the destination file is kept in a certain format.
Spreadsheet "oxno" is the file that needs filling out with data provided to me in spreadsheet "ox" The value that is the matching criteria will be the ITS account number (Field B in "ox" and field A in "oxno") and the currency (Field B in "oxno" and field G in "ox")
So I want a macro to look in "oxno" and use field the values of field "a" & "b" and search for the them in "ox" in fields "B" & "G". Once it finds a match it should copy the value of field "I" in "ox" and paste into field "n" on "oxno".
The next data to be copied should be what goes into field "O" in "oxno" this data is derived from field "L" or "M" but only when its of numerical value with one non numerical field in it (usually a "/"). Last value that I need copied over is Field "T" on "oxno" this is derived from field "J" or "M".
I realise the potential conflict because two sets of queries will be looking to gather values from field "M" on "ox". That is why I am hoping when we look for value for field "O" in "oxno" we only look for numbers with one non numerical field in it (usually a "/") .
After finishing writing this up, I realise how wordy this has become appologies for that, but hopefully someone will be able to understand what I am looking to achieve and will be able to suggest a solution.
I have two worksheets one that has a weekly schedule and sheet2 (daily attendance) where i want to transfer One day at a time but i only need the Job role,employee name and shift for that day, then i need to do the same for each day but i don't want seven sheets. please find attached an example sheet.
I am trying to transpher data between two userform.
The Idear, To store information within a list box on a userform and select what list items "information" is to be copyied to another useform's Textbox.
To have the 1st userform with a Text Box and one Button. When the user click the button the userform containing the listbox " information" is displayed. On this form the user can select an item "Information" that they want to be transphered into the other useform's textbox. (any information that is going into the textbox must only be added to the textbox information as a new line)
I have 3 worksheets- Sheet 1 and Sheet 2 will have data from the customer that I need to transfer in Sheet 3 as a summary. So if 5 rows are filled in Sheet 1 and 6 rows in sheet 2, VBA code that can transfer data from sheet 1 and sheet 2 to sheet 3 all one after other (i.e. have 11 rows total). The current code formula i have just replaces data that was filled in from sheet 1 to sheet 2.