Combining Multiple Templates Into One
Aug 17, 2009
A member of my team has created numerous templates in Excel 2007 for various customers. Each template has some columns that are identical for each (Cust Name, for example), and some columns are unique to each customer. I want to combine all of these individual templates into one Excel workbook and be able to click (select from a dropdown list) the customer name, and have only those columns that pertain to that customer appear. Select another customer name, different columns appear, and so on. Is this possible to do in Excel 2007? [This is my first post, so please forgive any offenses if my question is too long.]
View 9 Replies
ADVERTISEMENT
Jan 6, 2009
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.
View 9 Replies
View Related
Mar 22, 2007
Is there a way to automatically save a template as an xls file using the macro below?
ThisWorkbook.Activate
Sheets(" SCHEDULE").Select
ChDir "C:Documents and SettingsChrisDesktopRainforestMASTER LABOR"
ActiveWorkbook.SaveAs Filename:=Sheets("Schedule"). Range("v77").Value
I am trying to make a code that was written for the master schedule available when the workbook is renamed?
View 3 Replies
View Related
Dec 27, 2013
I do forecasting for my company. We have a forecast every quarter. Any good templates (simple or complex) for presenting different quarters (or time periods) and the actual results for management?
View 5 Replies
View Related
Aug 17, 2007
I'm trying to achieve: -
I want to create a spreadsheet which will include a column with email addresses in it.
Here's my problem.......
I want users to be able to click on an email address (like a hyperlink) and by doing this open their mail client (in this case Microsoft Outlook) and send an email. I would like the mail client to load a specific template just by clicking on the link from Excel. I'm not sure if this is possible, but there doesn't seem to be many things you can't do on Excel.
View 14 Replies
View Related
Aug 19, 2007
where I might find some?
View 3 Replies
View Related
May 26, 2014
Is it possible to combine the following three formulas into one formula?
=IF(AND(H5>59,(I5<5),(J5<15)),("Close Out"),(""))
=IF(AND(H5>59,(I5<15),(J5<5)),("Close Out"),(""))
=IF(AND(H5>55,(I5<10),(J5<3)),("Close Out"),(""))
View 2 Replies
View Related
Nov 11, 2009
I have attached a sample spreadsheet for your review. I think I need to combine multiple IF statements into one single statement. See column I:
There is currently a formula in Column I that looks at Column H and computes a ROUND formula based on this information. Unfortunately, this formula only works if Column J says "MB". If Column J says "Minutes" then I need Column I to compute with this formula: =IF(H2<30,0.5,ROUNDUP(H2/60,1))
How can I combine this with the current formula to get what I am looking for? Here is the current formula (for MB): =IF(H2<0.125,0.125,ROUND(H2,4))
View 2 Replies
View Related
Dec 28, 2012
I have a list of data elements listed row by row with headers
last 4 of SSNFull Nameschool_codeterm_beginterm_endSchool Rep NameEmail
1111 Jim smith 00104600082012 082016 school reptestemail@hotmail.com
2222 Jane Doe 00104600082012 082016 school reptestemail@hotmail.com
3333 justin justin00104600082012 082014 school reptestemail@hotmail.com
4444 Joe Joe 00104600082012 082016 school reptestemail@hotmail.com
5555 Jake Jake 00104600082012 082016 school reptestemail@hotmail.com
What I need to do is to get all 5 rows into one row and duplicate the headers so row 1 has all fire rows of information one column after the next comlumn. If I can't duplicate the headers easily and can just aggregate the data into one row in cell by cell that fine.
Sample document attached for review
View 8 Replies
View Related
Oct 24, 2013
I need to combine multiple sheets in to one sheet. Each sheet has a column with unique identifiers but other columns have different data. The model is attached.
View 5 Replies
View Related
Mar 12, 2014
I have a userform that has the same type information on 3 different entities (name, address, phone, etc). The same research is required on each one i.e. you need to look up the zip code for each). Therefore I have a bank of buttons on each page of the multipage form. Each button does the same thing depending on which page you are on. The following is my code to copy some information and open another application depending on which page you are on and which type entity.
My question is how can I combine these into one so my module is not 3' long.
View 1 Replies
View Related
Oct 23, 2008
I was wondering if you could help me combine a load of Macros (Theirs about 20, but I've only put two here)The code I have is this...
View 10 Replies
View Related
Mar 9, 2009
I have multiple WB's all with the same format in a single folder. I need a button to copy all the text from each WB into a single Master WB that has the same format. There are 3 sheets in each client WB corresponding to three sheets in the Master WB. Each client WB has a number of rows (or none) on each sheet and when they are copied to the Master they need to paste consecutively and into the corresponding Master sheet.
Something like this:
Copy rows from [WB1].Sheets 1, 2 and 3 (starting at a:4, columns A-Q). Then paste to corresponding [MasterWB].Sheets 1, 2 and 3 (at a:4, columns A-Q) then repeat with Next Book.
I hope thats clear enough. The Client WB's are all named "stats [name].xls" with 1 hidden sheet (to populate lists) and 3 sheets named "POC", "ISS" and "ECS" repectively. The Master WB is named Stats.xls with the same sheet names as the Client WB's.
The following code was written for me by a helpful member of this forum but it only copies the first sheet of each Client WB. When i tried to duplicate and modify it to copy the second and third sheets I could not get it to copy from the second/third sheets and it meant 3 buttons/3 steps/3 times the confusion.
Sub Report()
a = 1:
st:
If Sheets(5).Cells(a, 1) = "" Then GoTo endd
Path = Sheets(5).Cells(a, 1).Text
If Dir(Path) = "" Then
w = MsgBox(Path + " Is Not A Valid Path / File", , "REPORT")
a = a + 1: GoTo st
End If
If there is anyone who could help me with this I would very much appreciate it. I am only a basic user of Excel and VBA is still new to me. Adding modules and understanding basic commands is as much as I know at the moment.
View 9 Replies
View Related
Jun 22, 2009
Not really sure what I want to accomplish is called but here's a description:
I've got a file with 2 columns
Column A Column B
C53445002558-001--104
C53445540.00
C53455002558-003--105
C53455540.00
C53465540.00
C53475002558-003--105
C53475760.00
C53485002552-002
C53485002558-003--105
C53487294.00
C53495002558-003--105
C53495540.00
What I want is the following:
C53445002558-001--1045540.00 ....
View 9 Replies
View Related
May 18, 2006
I have two spreadsheets of 27,000+ rows each.
Each one consists of user data for each time they logged onto our systems. The problem is that there is one row for each login. There are anywhere from 1 to 20 (or so) rows per user. I need to turn that into a spreadsheet with one row for each user.
In theory, the problem shouldn't be that difficult. Each user has a unique user ID, and the only math that's required in the merge is a simple adding of numbers from three columns to give a single total for each user.
So far, the only workaround I've been able to come up with is using the subtotal function based on the unique user ID which will at least isolate each user and total up their usage statistics. But it still leaves me with the problem of deleting the now uneccessary extra rows by hand, and then pastin the data back into a new sheet. Using vlookup, I think.
View 9 Replies
View Related
Jul 19, 2006
I have an excel file that contains 93 tabs, all with the
same type of information, and I need to take the information from each
tab and combine it so that it has all the information from all 93 tabs
in one.....without having to copy and paste each tab,
View 12 Replies
View Related
Apr 9, 2013
I have a HUGE spreadsheet (1,000,000 rows) that contains a series of reports. The first row of the report contains all of the report identification numbers. The next 20-50 rows contains the report details, all in column A. Then the report ends with a cell in column A containing "[report_end]" and then on the next row the next report begins.
I would like to compress the report details all into a single cell in column A so that there is only one row for each report, sort of like this:
AA | BB | CC | DD
xyz
123
[end_report]
AA | BB | CC | DD
xyz
123
[end_report]
becomes:
AA xyz 123 [end_report] | BB | CC | DD
AA xyz 123 [end_report] | BB | CC | DD
View 5 Replies
View Related
Jun 29, 2014
I need to combine data from multiple tabs into one tab. I can have up to 5 tabs with data that starts in column B. The number of rows will be different each time.
View 1 Replies
View Related
Jul 26, 2007
would anyone be able to write the code that will combine the data from all rows from all worksheets within a workbook. i've struggled with this one....
here is the deal:
all worksheets have the same columns and column headings but differnet amount of row counts. the width of the sheets is to Column "M" or "13" and there are no blank columns.
There is no need to have the columns headings repeat within the compiled worksheet.
the amount of worksheets will vary depending on when i run it so it will not be able to use specific naming conventions.
View 9 Replies
View Related
Mar 11, 2008
I have multiple sheets within a workbook, where the sheetnames will always be changing.
Inside of these worksheets there is data that will be different, the starting cell of the range is allways the same and the number of columns is constant. The number of rows changes. I need a code that will go to each worksheet, define and copy the range on that sheet and paste it onto a summary sheet, in order.
I would like to collect all the data and put it onto one sheet.
View 9 Replies
View Related
Apr 15, 2008
I receive a monthly report containing a list of people, and how much is being paid for certain services. The company that sends me this list is preparing to adjust their rates and it will be retroactive back a few months.
The way they plan on doing it is by means of taking a credit back several months, then "re-paying" the correct rate. The main data will include the month for which the payment (or credit) is being made, the person's unique identifier, as well as the amount.
Here's a sample of what it would look like:
Name, ID, Month, Amount
John Doe, 123, 04012008, 25.00
John Doe, 123, 03012008, -20.00
John Doe, 123, 03012008, 25.00
John Doe, 123, 02012008, -20.00
John Doe, 123, 02012008, 25.00
So basically the above shows they paid $25.00 (correct rate) for April 08, then they took back $20.00 the prior two months (the old rate) and paid the correct rate right afterwards.
In what I need to do, this is going to be a lot of work. Is there a way to programatically merge the amounts given the member's unique ID as the "key field" as well as the same month? So it might look like the following:
Name, ID, Month, Amount
John Doe, 123, 04012008, 25.00
John Doe, 123, 03012008, 5.00
John Doe, 123, 02012008, 5.00
Just giving the sum of the amounts for a the given people in the same month?
I'm pretty good with VBA but this one is stumping me.
View 9 Replies
View Related
Sep 2, 2010
I would like to have a workbook with class lists for 21 classrooms.
Then I would like to use this each month to generate a workbook that has one sheet per class with the teacher name and class name as an overall header. The row stubs would be the student names. The 2 column headers would be the weekdays (Mon through Fri) and the day of the month as a number.
It seems that this could very well be an application that already exists. It seems like the kind of thing a business would create and put on the web as a free download as a good will thing.
Does such an application already exist? If not, is there a small example of populating a workbook with information in another workbook?
View 8 Replies
View Related
Apr 10, 2014
I am currently working on a performance document. I am working on the principle of having a master template which people can then access, Save As, and use to monitor performance around KPI's.
The issue I am having is around the name of the document changing when it is saved as, as all records have to retained. The macro I am struggling with is designed to unlock the workbook & worksheet, copy the worksheet specified into a new workbook and then return to the workbook the macro is held within and lock it back up. However, when the name changes it just locks the new workbook rather that the version I am asking it to.
ActiveWorkbook.Unprotect Password:="KPIreview"
ActiveSheet.Unprotect Password:="KPIreview"
Sheets("EID Graphs").Select
Sheets("EID Graphs").Copy
[Code] ....
Is there anyway I can change the “Kent – Monthly Activity & Performance Review – Version 17 – Master.xlsm” statement within the macro to reflect the change in name of the document?
View 2 Replies
View Related
Dec 11, 2009
I have quite a few Excel templates that need to be modified by changing/adding information or sheets to them. Instead of working through the lot manually, it would be good if I could do the same through code. With my little knowledge I can do this for workbooks. Need to typical code snippets or suggestions? e.g. to open a template file, save it with the original name, etc. The usual Dim srcWB as workbook, Set srcWB= ABC.xls and Workbooks(ABC.xls). open do not seem to work with template files (ABC.xlt)
View 5 Replies
View Related
Nov 24, 2009
I have a sheet with over 1000 rows showing me every employee's benefit choices. It has multiple rows for each employee - one that indicates their health option, one that indicates their dental option and so on.
I'd like to combine them so I have one row that has columns for health, dental, etc...and then their options would be listed all in the same row. I've attached the original with the solution I'd like pasted below. I'm sure this isn't difficult but I'm having trouble figuring it out.
View 2 Replies
View Related
Sep 7, 2013
I'm trying to figure out whether or not something is possible in power pivot. I'd like to load two tables into powerpivot and then set up formulas that create calculated values across the two tables.
Here's a simplified description of what the tables look like. [URL] .........
The pivot table should join the two tables on Month and Category and allow me to create calculated values across the cost category, like this. Note it's filtered to a single cost category right now: [URL] .......
In this view it's filtered to all cost categories but i'd be looking to still maintains the right count of members (i.e. it doesn't inflate the numbers from duplicate records as you might get if you were to join the two tables in a sql database): [URL] ....
The actual tables I would use are quite a bit larger (too large for excel without pp), and have quite a few more of each type of category.
Would something like this be possible to do in powerpivot without having to do some data prep work in another app to join the two tables together?
View 1 Replies
View Related
Sep 28, 2013
I have a time tracker that provides hours that employees clock in and out. It provides me with an excel document with all the employees time every two weeks as needed, however it does not total the hours from all previous spread sheets so that I can have a YTD (year to date) total of the employees hours worked and I need to be able to have this. Currently I am going through each and every spreadsheet and totalling them up. I have over 40 employees and this has become very time consuming to say the least.
View 1 Replies
View Related
Jun 16, 2014
I am trying to create a macro to gather a data range from multiple files placed a folder and combine them into a single worksheet which can be easily totaled. I've used some similar code I did for another project to gather the data. It starts by listing the file name and then the data set (About 40 cells) below the file title. However, the data sets from each spreadsheet are filling themselves into a single column one after the other, whereas I would like to have them populate one worksheet's data in each column.
[Code]......
SummaryWorkbook.xls
View 1 Replies
View Related
Mar 3, 2014
[URL]...I was trying to use this thread's solution macro on my sheet that is only consolidating columns A, B, and C with a couple thousand lines and I've had it running for over an hour now. There hasn't been any errors but is there any particular reason that might be causing it to take longer than it should aside from having to go through a few thousand rows?
Basically, I only want 1 Version of data in Column B and any other rows that has a match in column B, i want to consolidate unique data from Columns A and C.
A B C
AB 123 AAC
BA 131 AAC
AB 222 CAA
Res:
A B C
AB 123, 222 AAC, CAA
BA 131 AAC
View 2 Replies
View Related
Jun 9, 2009
This isn't absolutely necessary, but being able to do this easily would cut down on a lot of tedious jobs for me. Let's say I have a set of data like this (I tried to space it out as best I could to give the idea):
View 3 Replies
View Related
