I have a spreadsheet with over 70 worksheets. The majority of these are concerned with Work standards (National Occupational Standards), each of these standards are further broken down into performance criteria.
What I would like to happen is for a person to be able to enter a "Yes" or "No" response in a box to a specific question on an assessment report or a witness testimony worksheet. If a Yes is entered I would like the spreadsheet to automatically enter a reference i.e AR1 or WT2 (stated by the inputter on that worksheet) in each of the other seperate worksheets that cover this perfromance criteria with this reference.
Because many of the worksheets contain similar performance criteria, i.e "treating people fairly and with respect". The answer of "Yes" on the assessment report worksheet may require a reference inputting on anything up to 60 different worksheets. Is this possible or am I dreaming of the impossible?
I really should have asked this question before I wrote out all the seperate worksheets.
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")
I am trying to set up a "diet log" for my nephew to track his eating habits for a school project. I am trying to make the "total" section of the attached spread sheet auto calculating depending on the information placed in the "breakfast / lunch / dinner / snack" catagories. I have two worksheets in the file. The first being the preferred method of a single cell and seperating the items using only a ",". The second sheet has a little bit different lay out which I don't think will work as nicely.
Honestly, I am not sure if this will even be possible, but I figured I would try here as a final resort. Currently there are just some sample foods in there as I will let him fill in the actual workbook for his use.
I have a spreadsheet with a medium-sized data set, and I need to look up values from the large table, and thin the table down to only the relevant values.
To arrive at each unique value, I will have to match 3 input cells to the first 3 cells of the data table, then copy values from across that specific line to the appropriate cells of my smaller table. I really don't need to sum or do any math in this step, its just a copy from the master table to the smaller table, the math will come later.
I think I'm attaching the file (remember-first post) so hopefully it will help with what I'm trying to accomplish. Hopefully this isn't a big deal, but I've been pulling my hair out on this all day and I just can't find a solution anywhere that really matches up to this.
I am trying to create a vba using only one macro button (excel). I would like for them to enter their names and have their own password that will then insert their signature(pic.jpg) in a specific cell.
I created a certificate in excel and I don't want 12 (number of people) buttons visible. Id rather have a long code. Im not sure if using If and Then and ElseIf is the way to go.
I am looking to create an excel search with two input and three output values on Sheet 1 that will link to a table on Sheet 2. To help illustrate:
Sheet 1:
The two white cells indicate the two input values, and the three blue cells indicate the three desired output values.
Sheet 2:
I want to enter the two input values (Length and Width), press the "Search" button, and have the corresponding output values (DWG#, Item #, and Tooling #) from Sheet 2 appear in the three output cells on Sheet 1.
I am looking to take the data off of a "detail" sheet and put it to a summary page. I want the summary page to find the capital and expense from "details" sheet by the month on the "details" sheet. Then for every month add all the expenses and capital and put as 2 values per month, Capital and expense, on the summary page. I am not really sure where to begin but have added my excel workbook that I have started.
I'm working on a fairly complex spreadsheet where I want to pull conditional formatting into an entire row. I have a column in tab1 where I have a bunch of beer styles listed. On tab2, I have a 2 columns: a complete list of styles with a 'yes' or 'no' in the next column based on certain criteria. What I'd like to do is have the column in tab1 highlight if that style in tab2 has a 'no' in the column next to it.
I have several formulas that I use over and over, so I'm creating VBA functions that I can call from a worksheet.
But...when I have multiple inputs going into a function, is there a way to remind the user which input comes next in the same way that Excel does with built-in functions?
For example, when you type in =PMT( Excel brings up the helper text so you know that the order of inputs is (rate, nper, pv, etc....
Is there a way to bring up that same helper text in my own function? So if I have up, down, right, left as inputs and give the spreadsheet to someone else, they can see it's up, down, right left and not right, left, up, down .
1. I need a template that allows a user to select a location from a dropdown menu (Cell B1 of Example Data tab). 2. Titles in cells C1:V1 and B2:B8 will stay the same. 3. Data inputs within range C2:V8 would change for each location. The idea is based off the location selection from the dropdown menu, different data is shown within the range and available for updates. 4. The chart (see Example Chart) would sum all location inputs in range C2:V8
I want to create a macro to take multiple inputs from sheet 2, apply them simultaneously to the inputs on sheet 1, take sheet 1's output and list next to the inputs on sheet 2. I want to apply this to a spreadsheet with complicated calculations. Example spreadsheet attached. Related Macros I have will do a similar thing but only take one input at a time. Code as follows.
VB: Sub x() Dim r As Range With Sheet2 For Each r In .Range("A2", .Range("A2").End(xlDown)) Sheet1.Range("A2") = r r.Offset(, 2).Resize(, 3).Value = Sheet1.Range("C2:E2").Value Next r End With End Sub
I have a spreadsheet of courses required to reach a certification. On this spreadsheet I have listed the number of hours required for each course in one column, and how many hours I have accrued in an adjoining column. Not all the hours will occur at once, so I tend to bound from cell to cell adding hours in small amounts. What I am trying to do is create a macro that will allow me to add to the existing number of hours to the newly accrued hours, without typing over what is already there.
For example…Class 1 requires five hours total, and I have two hours accrued. If I accrue two more hours (for a total of four hours) I want to update cell E2 without going in to this cell manually and changing this number. I would like to enter the additional two hours in a text box or similar function, and have that function update E2. To add to the level of difficulty, there are four levels of class. This means not only do I need to be able to select which class hours need updated, but which level of class. I have attached the spreadsheet I am working with to try to make things a little clearer.
I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells.
I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook.
I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.
I have an Excel Spreadsheet (2007) that contains over 500,000 records that shows Electric meter usage per month over a 24 month period per meter. What I want to be able to do is to select a meter row per number and copy is to it's own worksheet. At the end - I want to be able to have a seperate worksheet per electric meter number - that I can create a graph. If I go through all 9000 meters and copy and paste into a different worksheet - it will take me weeks to do manually. How can I do this automatically?
I need a macro that would consolidate all data in multiple worksheets of multiple workbooks in one Master file.
All the workbooks will be in one particular folder. The macro should search for data in all the workbooks and consolidate it in one master excel workbook.
I am currently using both excel 2007 and excel 2010. This macro would really reduce manual work as currently consolidating data from 45 to 50 sheets takes an ample amount of time...
I have attached a test workbook excel 2010 (ignore ref# errors, I've cut the workbook down for uploading purposes) What I would like to do is have a 'Button' on my 'information Sheet' which when clicked would clear certain cells. I have searched the forum but can't find a solution, everyone seems to want to delete rows or columns but I just want to clear certain cells. The workbook will have 11 sheets each named 'caravan 1' through to 'caravan 11' The uploaded test workbook only only has 3 sheets.
On 'caravan 1' (which is slightly different to the other 10) I want to clear the content of cells
On all other 'Caravan sheets' I want to clear the contents of cells
D4 & D5 E4, E5, E22, E23, E41 & E42
It would be icing on the cake if it could give a warning such as " are you sure you want to clear these cells" but that isn't really necessary. The worksheets will be password protected, but the cells mentioned above will not be. If it proves too difficult to clear all the cells on all the sheets with one click, then perhaps a simpler solution might be to have a button on each sheet instead
I'm making a Excel list for trading cards (MTG) and I've divided it into different worksheets in order to sort it by the color of the cards (not really important I guess).
The issue I have is that I want it to copy certain columns (in this case C3:C1000, F3:F1000, G3:1000 ) from almost all of the worksheets into a new worksheet where it should paste them into column A, B and C.
In two of the worksheets it's also different (C3:C1000 , G3:G:1000, H3:H1000).
I've seen many where you copy one range from multiple worksheets, and that would've been okay I guess if not those two worksheets were different. I'd prefer not to change them, and I'd also prefer if I could've gotten just the information I needed also. Is this possible to do?
I'm trying to email multiple worksheets from a list to multiple recipients in a list, but in one file per person. In a sheet called "Email list", for example, I have a list of 50 worksheet names (e.g. one for each cost centre) in column A (with a heading in cell A1, if that's OK, so the list starts in A2) and in columns B-F up to 5 recipients for each worksheet (e.g. 3 recipients might be 2 supervisors and their manager).
Also, each manager and supervisor might also receive several sheets.
Although columns B-F are presently free-format (they can be anyone's email address, regardless of whether they're a supervisor or manager), it might be easier to split these columns so B,C,D are supervisors emails and E,F are managers, if that makes it any easier creating the collated file of worksheets..?
Unfortunately the recipients might change occasionally so they can not be hard keyed into the macro itself (which would be much easier), but need to be in a table so it's easier for the user to update and maintain - in the past, I've managed to hard key the requirement into a macro which I've maintained (by recording and editing the macro), but I don't know the VB to figure this new request.
Ideally, I am trying to create a macro that can collate the various worksheets each person will receive and send the selected sheets in one file to that recipient, rather than multiple files within one email and/or multiple emails.
I have an excel workbook with 8 worksheets. Each worksheet has vertical columns (approx 250 columns per sheet) of numeric data. Is there a function or macro that will combine all of this data into one vertical column without having to individually cut and paste each one into the new column?
I would like to append multiple workbooks with multiple worksheets in a separate workbook. For eg. I have workbook "A" with sheets 1,2,3 and workbook "B" with sheets 4,5,6. Now I want to append "A" and "B" to create workbook "C" with sheets 1,2,3,4,5,6.
I have a folder - U:30000 - that contains a number of identically layed out workbooks. What I'm trying to do is, in each, change a number of cell values. Where dominicb's solution to the previous problem didn't work for me, however, is that I need to change cells in multiple worksheets. The cells are specifically:
On worksheet 'Construction': C3 (which is a date), D3 (which is a text value); on worksheet 'FF&E': D3 (the same text value as D3 on 'Construction'. Unfortunately, whoever initially set up the workbook didn't have the foresight to link it!)
I'm trying to combine data from several worksheets (one sheet per workbook) into a single, consolidated master worksheet for reporting purposes (filters and pivot tables). We do not need to keep formulas for the master worksheet, only values and formats. Individual worksheets are used by different users to capture case data in a Human Services field. Column headings are identical, but rows contain data on individual cases. I'm trying to find a relatively easy way to combine multiple worksheets into a single master. After I establish the worksheets and technique, it will be operated by extremely basic users so I've been reluctant to use extensive macros.
Because of complex reporting needs, the exact combination of worksheets being combined for reporting may vary. For example, one time I may combine Tom, Dick and Harry, another time Tom, Dick and Bob, and yet another time Tom, Dick, Bob and Harry. Obviously, one method is to cut and paste the rows into a single worksheet. Are there more elegant solutions that could easily be handled by very basic users? Worksheets are stored in a single folder along with a separate worksheet used for validation rules (as you can guess, this would ideally be a database application but for various economic and political reasons we are using Excel). One possibility, if straightforward, is to use Access to consolidate data then export it back to Excel for analysis. I've scoured the various threads but have not found a situation mirroring mine. The number of rows for each worksheet is generally less than one hundred, but there will be a few exceeding several hundred. Total numer of rows of the resultant master worksheet will not exceed 10,000.
I am trying to consolidate information from multiple (atleast two or more) worksheets (in the same workbook) into a single sheet.
The worksheets have identical column headings. There are 4 columns in each worksheet. The first three columns are text strings (and some cell in these columns may be blank). The fourth column is an integer. The number of rows in each worksheet is most likely to vary. Within a worksheet the rows are unique. However, some rows may be duplicated across worksheets.
To reiterate, here's what I am trying to do.
Step 1. Create a summary sheet.
Step 2. When there is a unique row (identified by the first three cells in that row) across worksheets then I have that row as is in my summary sheet.
Step 3. When there are duplicate rows (identified by the first three cells in that row) across worksheets, I want to add up the cell in the fourth column associated with those row in my summary sheet
I have tried union (works only within a sheet), consolidate (works only with a single column). I don't need the sophistication of a pivot table but simply an aggregation of data
I am trying to do this using macros (within an add-in)
I have enclosed a sample workbook with the expected output. I created this workbook by hand in an attempt to clarify my problem statement.
Month Input, Column Input and Network Input are changeable based on user input, I am trying to get the desired output based on selection from A20, B20, C20 into cells G20, G21, G22 from row number 2 and corresponding values in H20, H21, H22 from the corresponding row based on the selection.
I'm trying to compose a date based on inputs from a userform. The user selects the day, month and year in combo boxes, and I want to place this data on a single cell in the format DD/MM/YYYY. How can I do this?
