Excel 2003 :: VBA - Adding Named Template Worksheets Based On Single Cell Value
Apr 24, 2012
I'm trying to make a macro in Excel 2003 to create x amount of named worksheets that are a copy of a different worksheet.
1. I have a main worksheet that will have a number manually entered into a cell (lets say A1);
2. I have a 'template' worksheet;
3. I'll assign the macro to a button on the main worksheet
If I enter '10' into cell A1 of the main worksheet, I'd like to click the button and have Excel create 10 copies of the template worksheet. These new worksheets should all share the same name with a number after them (ex: banana 1, banana 2..... banana 10).
View 2 Replies
ADVERTISEMENT
Jan 17, 2012
I some code that reduces a long list of dates from one source of data (in its own worksheet) down to a unique list of dates (on another worksheet). I need to create a new worksheet for each date in the range (named for the date), and then allocate each line item for that date in the source data into its repsective worksheet.
I'm using Excel 2003.
View 3 Replies
View Related
Jan 4, 2013
I have two worksheets (Sheet1 and Sheet2). Based on meeting a condition on Sheet1 AND Sheet2, I'd like to use macros to automatically hide rows on Sheet2.
Sheet1 sample:
Type
Selection
[Code]...
The conditions are: If on Sheet1, a Type has the Selection "Include", then any rows on Sheet2 which have that same Type are shown (ie. not hidden).
If on Sheet1, a Type has the Selection "Exclude",then any rows on Sheet2 which have that same Type are hidden.
So in the example tables above, if ONLY Type A = Include, then on Sheet2, the rows for Sample URL1 and Sample URL2 are shown, and the others are hidden.
Also in the example tables above, if Type A = Include AND Type B = Include (and all other Types are Exclude), then on Sheet2, the rows for Sample URL1, Sample URL2 AND Sample URL3 are shown, and the others are hidden.
I'm fine with the concept of If... And... on the same worksheet, it's trying to get it to look at two separate worksheets I'm struggling with!!
View 3 Replies
View Related
Jan 14, 2013
I have a workbook with two worksheets, Sheet1 and Sheet 2. Based on the data entered in the 2nd column on Sheet1, I'd like a macro which hides rows for Events which do not meet the criteria entered on Sheet1.
So in the example below, on Sheet2, I only want to see rows where the Country=Scotland, AND the Location=Glasgow, AND the Start Date is greater than (or equal) the Start Date on Sheet1.
Sample of Sheet1
Country
Scotland
Location
Glasgow
Start Date (dd/mm/yyyy)
30/04/2013
[code]....
So in this scenario, only the FIRST row meets the 3 criteria (Scotland, Glasgow, date after 30th April), thus all the other rows should be hidden.
View 6 Replies
View Related
Feb 25, 2013
I keep record of my blood sugar in excel 2003 that I send to my doctor weekly.
What I would like to do is, instead of typing the readings and mmol/L at the end in every cell, is to just type in the reading (example 5.5) then tab of the cell and have excel populate the cell with mmol/L automatically after the reading.
Can it be done?
View 2 Replies
View Related
May 30, 2007
Been trying to work this one out and have ended up banging my head against the wall.
Basically I have a spreadsheet, in the first column is the person's name, in the secon which team they are in and following this a cell for each day of the month with their availability.
The team name is a selection from a list, the availability is 1-6 (different levels of availability)
I'm trying to do subtotals at the bottom for each separate team, so the very last row each of the cells for each is added up:
Name Team 1st 2nd 3rd 4th 5th 6th 7th etc.
Mr X ___1st___1______2__1
Mr Y ___2nd_______2________5__5__5
Mr Z ___1st___1____________3__3__3
Total 1st: 2 2 1 3 3 3
Total 2nd: 2 5 5 5
So basically the formula in the last row goes along the lines of:
Add each cell in the column IF the team name = 1st
View 9 Replies
View Related
Aug 4, 2006
I would like one column (column A) to be an "indentifier" column. Column B is the customer name, column C is the customer city and state, column D is the customer phone number, and column E is the customer email.
By calling column A an "identifier" column, what I mean is that I would like to be able to go down the list and put an "x" next to each customer I would like to include in that particular workbook. I would then like to be able to run a macro which would take each of the customers with an "x" in the column A, create a copy of the template comfirmation call sheet, and fill in the identifying information from columns B through E into the copied template. Each worksheet would also need to be renamed the same as the customer name. It would need to perform this same procedure for each row that has an "x," making sure that all worksheets (confirmation call sheets) are grouped into one new workbook.
Sheet 1 is called "main"
Sheet 1 contains 5 columns
A = Blank
B = Customer Name
C = Customer City and State
D = Customer Phone
E = Customer email
Sheet 2 is called "template"
"x"'s are placed into "main" sheet, column A to identify which customer will be affected by macro...................
View 2 Replies
View Related
Jun 20, 2006
Here is what i want to do. # I have a workbook say 'Template', which has two columns: ColA -> Names , ColB -> Value, his acts as a template, i.e a budget. # Another workbook 'sample' which has similar ColA(may contain additional rows) and corresponding value in ColB.
Here is what i need::
I need to copy the contents of ColA and ColB of 'sample' wholse ColB valules are greater than ColB values of 'Template' for corresponding ColA values in both, into a new workbook. SO basically, template acts as a budget check for the sample workbook, and if any value in ColB goes above the budget in template, it will show up in the new workbook. I have attached both the workbooks. Basically the red highlighted cells in Sample are the ones i want in a new workbook.
View 7 Replies
View Related
Mar 22, 2012
I have a spreadsheet, but it came from another file using the detach sheet method. It has therefore taken with it, all the named ranges that are now superfluous.
It seems a bit long-winded to delete these named ranges one by one manually. Would there be an easy to understand script that would delete them all, so I can start with a "clean sheet".
I'm using Excel 2003.
View 5 Replies
View Related
Sep 26, 2011
I am wondering if it possible to automate the copying of data from particular cells, based on a value in a different cell, into a different format.
So to go from this simplified table:
AB1NameLevel2Arthur2a3Briony3c4Catherine3b5David3a6Edward2a7Felicity3c8George3c
to something like this:
FGHI12a3c3b3a2ArthurBrionyCatherineDavid3EdwardFelicity
4
George
At the moment I do it all manually, and it takes forever. I am sure there must be a simple way of doing it. I am using Excel 2003, but could work in a newer version if required.
View 9 Replies
View Related
Jan 31, 2014
In a large range of cells, how I can remove the single quote at the beginning of the contents of each cell? (without single quote they would be formulas). It didn't work using CTRL + L and trying to replace the single quote by blank applying this to the whole range. I am currently using Excel 2003.
Example:
cell a1 contents: '= IF (I17 = "----------", "----------", E17-C17)
cell a2 contents: '= IF (I18 = "----------", "----------", E18-C18)
cell a3 contents: '= IF (I19 = "----------", "----------", E19-C19)
....
etc., etc., etc.
View 2 Replies
View Related
Sep 9, 2013
I have this code working fine in excel 2007 but I need to get it working in excel 2003.
=0.02*(_xlfn.SUMIFS($D$2:$D$98,$C$2:$C$98,">="&I5,$C$2:$C$98,"<="&I4))
I have attached a test document to show what I'd like to do. It is basically adding values if the column next to it is in a certain range.
counting columns in Excel 2003.xls
View 3 Replies
View Related
Jun 7, 2014
I am using Excel 2003
I have used =IF(I6=J6,1,0) but I want a 0 value if the two cells are blank. How do add this to the formula?
View 7 Replies
View Related
Jan 22, 2013
I am wondering if it is possible to use a formula or IF statement within a macro to add a single row at a time if a cell shows as False? For example, if the value in column AD is False, I want it to add a single row under that row that has False. However, if the value in column AD is True, I don't want it to add a row and skip to the next record. I need it for approximately 500 records.
View 2 Replies
View Related
Mar 20, 2014
I have a company with upto 5 products, and I have created a dropdown box where you select your product.
When I select product 1 i would need info on product 1 , when i select product 2 I need info on product 1 and 2 and continue. so for product 5 -i need info on product 1-5.
I tried to add all info on excel and tried to delete it one by one by deleting rows in excel for products but it does not work.
View 4 Replies
View Related
Jul 2, 2012
I have a range of cells each containing a name. Based on a number that has to be entered manually I want excel to return the names concatenated in one cell. So for example:
Number of variable entered: 5
q9001
q9002
q9003
q9004
q9005
q9006
etc.
Should give me: "q9001 q9002 q9003 q9004 q9005"
I have been trying to work with formulas using IF and CONCAT functions. But so far I haven't figured out how to have excel return me the correct amount of variables for each separate number that can be entered seeing the number of variables entered can vary from 1 up to 50.
(Using Excel 2010)
View 7 Replies
View Related
Feb 6, 2013
I have a 97-2003 spreadsheet with approximately 22,000 lines of seperate companies. I need to know how to insert a blank line between the company names. I have been using the "Right Click - Insert" method but soon realized there must be 2000 different companies. The Company Name column is A.
View 7 Replies
View Related
Apr 3, 2014
I'm stuck using Excel 2003 to auto-populate a cell.
I have a range of dates in five consecutive columns called:
Phase 1, Phase 2, Phase 3, Phase 4 and Phase 5
I enter the date that 'Phase 1' starts under the first header. Once Phase 2 starts I enter a date under 'Phase 2', and so on to Phase 5.
Each phase is consecutive to the next so will always be filled in from 1 to 5.
I want to create an additional column called 'Status' that shows the Column Title of the last phase with a date in it. For example, if Phase 1 to 3 had dates but 4 & 5 were blank, "Phase 3" would be displayed in the 'Status' column.
I've tried nesting some ISBLANK functions without any luck.
View 4 Replies
View Related
Nov 21, 2011
I would like to merge several sheets into one sheet, all from within the same workbook. All sheets have the same data layout. I use xl 2003.
How can I merge them all into a single worksheet?
View 6 Replies
View Related
Nov 11, 2012
I am running Excel 2003 on a Win7 system.
Here is my situation:
Each of my data sets spans roughly 75 columns by 250 rows at present, but this could expand. The first 7 rows contain metadata. Columns 2-25 or so contain the raw data, from which everything to the right is calculated. The data sets have most columns in common, but not necessarily all.
In order to tease out the most meaningful information from my data, I frequently sort all or part of it based on varying criteria. When I find a useful sorting criterion, I create a new column with a header that describes the criterion and populate it with a formula that returns a 1 if the condition of interest is met for that row, or a 0 if it is not. For example, if I am doing this in column AA, I might enter
=--(AND($AX8>$AA$4,$Y8>0))
and copy it down to the end of the data. The resulting vector of 1s and 0s quickly re-identify data that meets that criterion even after subsequent resorting. It also makes locating data that meets multiple sorting criteria extremely simple. Essentially, I create a truth table.
Cell $AA$4 in the above example contains a "comparator" value I might wish to change at some point, which would change the subset of data the condition selects for.
Here's the first hard part:
For each data set, I need the ability to generate meaningful plots that includes separate series based on the criteria I have described. However, I also need to retain the ability to resort the data or change the comparator value without disrupting these plots. In other words, the plots must NOT change when the order of the data is changed, but MUST change to display the appropriate data when the comparator changes.
Here's the 2nd hard part:
Once I have this working for one data set, I need to be able to port it to other data sets (which are contained in other workbooks), so that I can compare equivalent plots from each. I also need to minimize the number of manual steps involved in doing so, to avoid human errors and excessive time consumption.
The only other possible complication I can think of at the moment is that, to this point, I have been inserting blank rows to isolate subsets that I do not wish to perform further sorting on from each other.
Right now I am angling toward VBA code that loops through the entire data set to generate base dynamic ranges using the column header row (row 1) as the names, and the entire column of data for the rangeloops through the truth table columns to generate "branch" row ranges for each of the sorting conditions,loops through the entire data set one more time to create "branch" ranges for each of the base ranges.
I could generate some code to accomplish a one-off solution for a given configuration of a single data set (provided there is not a list length limit in a chart series that I'd be violating)...but without a dynamic named range, I don't know how to get to something that would update appropriately. So in essence, I am still stuck at the dynamic range part of this.
View 1 Replies
View Related
Jun 5, 2014
Basically the first worksheet (equipment list) is set out for parts used for each individual unit which can be printed out for each unit.
The second worksheet is an equipment list, where each part number and quantity required is displayed which can be sent to suppliers for ordering.
At the moment I
-> copy the equipment list to a new worksheet
-> do an advanced filter for "Unique records only"
-> =SUMIF('Equipment List'!B:B,'Parts List'!A16,'Equipment List'!D:D)
which is much faster than the way we used to do it.
The problem is, when I add a part to the equipment list that is new, I need to go through the process again.
Is there a way to automate so any parts I add to the equipment list, if it is the first time the part number has been entered, it will copy to the parts list worksheet and update the qty column or if it is an existing part number it just updates the qty column?
The equipment list may potentially have up to 100 different drives, but using mainly the same equipment.
I created this sample on an old desktop using excel 2003 but I use 2010 on my laptop.
View 3 Replies
View Related
Apr 10, 2013
MS Excel 2010, WinsXP
how to highlight an entire record based on the value of a single cell?
I would like to highlight all records grey where cells in a column = "closed".
View 4 Replies
View Related
Jan 9, 2012
Using Excel from Office 2007, and WinXP Pro OS
I have a spreadsheet which contains 2 drop down pick lists in Field1 and Field2.
Code:
FIELD1 | FIELD2 | FIELD3
--------------------------------------------
In Cell A2, I have this set in Data > Data Validation:
Allow: List
Source: =CatList
If the user selects a value from the CatList range, then the drop-down options in Field2 are updated accordingly.
In Cell B2, I have this set in Data > Data Validation:
Allow: List
Source: =INDIRECT(A2)
The Ranges are as follows:
Code:
Cat1 | Cat2 | Cat3
---------------------------------------------
Cheese | Hat | Square
Trees | Cat | Circle
Bees | Sat | Triangle
Knees | Mat | Rectangle
Apologies | Bat | Octagon
I'd really like to be able to add a message into FIELD3, which is based on the value in Cat2
e.g. using pseudocode, something like:
If Field2 value = "Apologies" then put this message in Field3: "This requires approval from A"
elseif Field2 value = "Triangle" then put this message in Field3: "This requires approval from XYZ"
elseif Feidl2 value = "Bat" then put this message in Field3: "This does not require approval"
Basically this is for an access form, and I need to be able to add a feedback message to field3 based on what's in Field2.
I thought about using the INDIRECT option to add messages but I can't see how I'd do it. Maybe VB would do it, but I don't know it well enough.
View 3 Replies
View Related
Aug 16, 2013
I have an excel document with multiple excel sheets(sheet1, sheet2...etc), now every sheet contains a cell "total".
Now I want to copy the row containing "total" from all the sheets into another sheet called "report".
View 9 Replies
View Related
Jul 27, 2014
I have a number of separate worksheets in one spreadsheet all based on the same list (eg customers); the customer's name is the first column and hence the 'key' in each worksheet; when I insert or delete a line in the main worksheet the formulae in the first (customer name) column are amended in the other worksheets BUT I ideally need more than that; when I insert a new customer in the first (main worksheet) I need a new line with that customer name inserting into the corresponding place in the other worksheets; and when I move a line (eg delete a customer and move them to the bottom of the main worksheet list) I need the corresponding lines in the other worksheets moving as well. I guess what I really need is a drill-down function; a main customer list and sub-lists all linking back to the main lists like you would get in database tables.
View 5 Replies
View Related
Nov 28, 2012
Link: Create a workbook from every worksheet in your workbook
It works great, but is it possible to modify this to export each worksheet as an html file instead of an Excel workbook?
I'm using Excel 2003.
View 2 Replies
View Related
Mar 20, 2009
I have a workbook with a named range myrange, cells a2:a1001. I have counter as a named range, mycount cell z2. I want to get the value of myrange at cell address mycount and put that value elsewhere using vba.
View 2 Replies
View Related
Nov 6, 2008
I have a large amounts of named ranges each of about 100 rows 20 column's each in its own sheet and I need to be able to refer to a singe cell in that range.
I want to do this so that I can use an offset to get another cell value relative to the cell in the named range.
F9 contains a hyperlink to the named range and is the same as the named range but with spaces.
A couple of things that I have tried are below:
=OFFSET(INDIRECT(SUBSTITUTE($F9," ","_")),1,0)
=OFFSET(VLOOKUP("bob",INDIRECT(SUBSTITUTE($F9," ","_")),14,FALSE),1,0)
Neither of these work, I am asuming it is because I dont have a specific cell reference named for the offset?
View 9 Replies
View Related
Aug 16, 2014
Intend creating a calculator that shows me how much a certain hotel room costs at different times (seasons) of the year, for example I have a One Bedroom Budget Apartment (list of room types is extensive), I also have three seasons, low, S Hols and Xmas, so I have a sheet created and inserted drop down boxes for the RoomType, Season, and Number of days, I now have to insert a formula that looks for the three variables and inserts the cost (I already have this info on a worksheet)
TotalCost
RoomTypes
Season
Days
[Code].....
Also I am using Excel 2003 so no combo box option (that I can find anyway)
View 1 Replies
View Related
Oct 23, 2013
(EXCEL 2003)I have 6 worksheets for 6 separate ad reps that will be populated with customer info. To make my life simple I just want column A from each of the 6 worksheets to show up on a master sheet in column A. But what also needs to happen is if we add and delete customers that also needs to happen on the master. I understand the "=" and then pointing to a cell in another sheet and hitting enter. I got that to work but I just have soooo many cells to work with that would just be way too time consuming.
View 2 Replies
View Related
