I need to create a Spreadsheet where a certain Value is added to the from of the data I enter into the cell. For example: All of my MAC Addresses start with 00:80:64. I want to be able to just type in the last three entries into the cell and have Excell automatically add the value 00:80:64 So I want to be able to add a specified value to ebvery entry in a collumn.
View 1 Replies
I want to pull data from my excel file (using VBA) into Ms Word. I created a template in Word and wrote a macro to do this, it worked, however, anytime a new row is inserted or deleted in my excel spreadsheet my macro produces wrong results in my Ms Word template because the cell position has shifted, thereby producing the wrong result. How do i make it that my result remain the same when new row is added to my spreadsheet.
Example:
Excel row1: vicky 528
row2: sam 532
row3: john 092
row4: Own 211 word template: 092
This is what happen next:
Excel (New row added) Macro: ThisDocument.ScreenedPatients.Caption = wb.Sheets("CSAs").Cells(16, 1)
row1: pat 542
row2: vicky 528
row3: sam 532
row4: john 092
row5: Own 211 resulting Word template(running macro): word template: 532
But I want John to automatically go on the Word template without going into macro to change it all the time when a new row is added.
I have a workbook to track clients served. The first sheet has all of the data entered into it. The name, age, demographics, services. The second sheet is a template of the individual client's pages, summarizing their information and services received. I want to code the sheet so that once a new client is entered into the table on sheet 1, the template will automatically be copied to the end, renamed, and the basic data for the new client will be pasted into the new sheet.
I've been working on this project and done a ton of googling. And also am very new to VBA. I have found a solution that works:
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
Sheets(2).Copy After:=Sheets(Sheets.Count)
[Code]....
But I really don't want to do 50 "else" clauses for each line where there could be a new client. So I was thinking, if i could find a way to do a relative reference, such that it selects the cells to copy relative to the one that was changed within the range of that column, then I would only need one "if" statement. Is that possible? Is there some way to change the "B3" in target_address to "any cell in column B" and then range "A3" a few lines down would somehow be "1 cell left of the one changed" and the range "A3:K3" to copy would be "1 left:9 right" of the one changed. I know this may be impossible or just not how VBA language works, but I thought it'd be a lot easier than 50 repititions of if..then...else....
The attached is a race keeper's scorecard.
Tab 1 lists all participants and race times.
Tab 2 generates a printout to post on a wall.
The spreadsheet works great, BUT, I forgot I needed to add a condition of DNS (Did not start) and DNF (Did not finish).
Once this is added, of course, it breaks everything done so far (damage being done in column M on tab 1 and all of tab 2).
My ideal state is that a DNS or DNF can appear in column L on Tab 1. . . and these participants fall to the bottom of the list generated on Tab 2.
I know there are miracle workers out there who can make this happen! Again, hoping for no code, and no manual manipulation if possible (end user is not Excel savvy). Must be compatible with Excel 97-2003.
[Excel 2003] I have 2 spreadsheets: one to summarize data from a 2nd detail spreadsheet. I'm analyzing work order information for a service operation.
I'm using dynamic name ranges, as follows, for the detail:
WO_Num =OFFSET('WO Tracking Log'!$A6,0,0,COUNT('WO Tracking Log'!$A:$A),1)
Other detail data is defined as these examples show:
GM_X =OFFSET(WO_Num,0,8)
OpenDate =OFFSET(WO_Num,0,1)
All detail data begins in Row 6 in the detail spreadsheet.
In the Summary spreadsheet, it appears to make a difference where my calcs are located in order for my COUNTIF's to work correctly. As long as I keep my summary calc (to total the number of work orders in the detail) in Row 2 of the Summary, it works fine...but if EITHER I move my calc down a row OR if my detail drops down a row because a row was added above (where I have just header info), my summary totals change?!?! I don't understand.
Here are two examples of the calcs I'm using in the summary:
=COUNTIF(WO_Num,">0")
=COUNTIF(GM_X,"X")
Can someone tell me what is going on? What I'm doing wrong?
I am trying to create a chart which updates every time a new date (and its data) is added to my list below:
Date
Clicks
Impressions
Click Through Rate
01/06/2012
3
93
3.23
02/06/2012
4
100
4
03/06/2012
2
102
1.96
The chart plots the number of impressions and the Click through rate (on a secondary axis) against the date.
I think I need to use the offset property but am not totally sure how to do this
Is there a way to automatically create list of the Tab names, and have it automatically update when new tabs are added?
View 2 Replies View RelatedIs there a VBA code that will enable me to create a button and assign a certain macro to it everytime I insert a new worksheet?
how to insert a new worksheet with VBA, what I want is that when I insert that worksheet, there is already a button there with a specific macro(already made) assigned to it.
I recently added a formula to a spreadsheet with som complicated formula. It worked ok and I saved the sheet. Now it takes 5 minutes to open the sheet and when I try to do anything,like delete the inserted column the program locks up giving a no responding message. I can do without this column if I have to.
View 4 Replies View RelatedI have the following spreadsheet:
A-------------B
File No.-----PRODUCT TYPE
123456-----NDT
123456-----NDT
123456-----NDT
123456---------
123456---------
789110---------
789110---------
789110---------
What I need is if there is an NDT in Column B and 123456 in Column A then I need to eliminate all rows which contain 123456.
End result
A
789110
789110
789110
I've got a master spreadsheet grabbing data from 12-13 different tabs/spreadsheets.
One of those has a table, and I was wondering:
Is it possible for the master spreadsheet to automatically add a new line every time someone adds a new line and creates a new entry in a different spreadsheet?
In other words,
If there is a table in the master spreadsheet, and someone adds a new line, is there a formula which allows the master spreadsheet to add a new line in order to autopopulate?
I'd like to create a button that makes the "Find and Replace" popup come up when pressed. How to do this? (Ctrl + F is the shortcut for it)
View 3 Replies View RelatedI'm trying to create a spreadsheet in which the cell will tell me how many hours, more or less, i have worked a week.
Below is what I’m trying to create - but I'm having trouble.
- if the total = 35 (in cell A1) then display -2 (in A3),
- if the total = 36 (in cell A1) then display -1 (in A3),
- if the total = 37 (in cell A1) then display 0 (in A3),
- if the total = 38 (in cell A1) then display 1 (in A3),
- if the total = 39 (in cell A1) then display 2 (in A3), etc (and so on)
How do I create VBA macro to create CSV files out of Excel spread sheet for each tab? Right now I am manually opening the spreadsheet and saving each tab as CSV file. But it is taking lot of time if we have lot of tabs.
View 14 Replies View RelatedI have a small loan business funded by multiple investors. A spreadsheet that could calculate each investors monthly share of cash flow. Can a party familiar with Excel create this custom sheet with my input. How would your charges be determined.
View 2 Replies View RelatedI was wondering how to create buttons such that when the user clicks on the button they are automatically taken to a different part of the workbook.
I'd like to do this because my spreadsheet is now large enough such that navigating just using the sheet tabs at the bottom is not ideal.
I can create a hyperlink, but I think a button just looks nicer.
I am trying to find a code that will allow me to generate multiple sheets depending on what is selected in one specific column. I have found a code that is perfect for this however I can't seem to set the range. For example instead of copying all of column headings I only need to copy up to column AN2.
View 1 Replies View RelatedHas ever created a spreadsheet that used Monte Carlo Simulation?
I've been able to create one that works using the random number generator in conjunction with discrete probability distributions - that's fairly easy, just assign a probability to each discrete value in the distribution and then decide whether or not to select it using a randomly generated number.
However, I want to perform a Monte Carlo simulation that uses a continuous (not discrete) probability distribution, but how I would use the random number generator to then select a value from the distribution based on its probability of occurance.
i have a spreadsheet which i have attached, currently users have to filter each column to get the end result
I would instead, like to create a screen where by they answer questions, for example. a drop down box where they choose flow rate, ie. 0-5l/min 6-10 l/min and so on for each option and then they end up with the list of choices which meet their criteria
I have an Excel spreadsheet containing a large amount of formulas applicable to 2009. I want to create a duplicate workbook for 2010. I want to maintain all cell formatting, ie A1=sum(whatever) without retyping it all. When I attempt to use copy-and-paste (or paste special) from 2009 to 2010, the cell values maintain a reference to the 2009 workbook. For example on 2010 workbook, A1=[2009]sum(whatever). Therefore the values from 2009 print in cells. I only want to maintain cell formula formatting, not a reference back to 2009 workbook.
View 5 Replies View RelatedI am trying to create a spreadsheet where when certain conditions are met, a certain score is given. For example, if the condition is >= 300, the score is 15. If the condition is >= 290, the score is 14. All the way down to a score of zero.
I tried...
=if(A2>= 300, "15", if(A2>= 290, "14", if(A2>= 280, "13"))) etc.
I would like to combine 2 tabs into one spreadsheet. I was not able to do so, because it exceeds the max rows allowed in excel. I have installed powerpivots but not sure how to combine data using powerpivots.
View 3 Replies View RelatedWhat i'm looking to do is create a spreadsheet where the first page allows me to input the number of teams in the group.The teams cannot play others in the group except the ones they are paired with. eg. "Enter number of team pairs in set" (in this case 4)
Team 1a vs Team 1b
Team 2a vs Team 2b
Team 3a vs Team 3b
Team 4a vs Team 4b
After number of teams in set is established click a start button on the first tab of the spreadsheet.
this auto generates a new tab with all the possible win/loss combinations of 4 team pairs.
New Picture.jpg
if the number of teams on Tab 1 is changed (say to 6) and the start button is clicked, another new tab is generated with all possible win loss combinations of a 6 team set.
remember 1a vs 1b can only be win or lose and the win lose combinations are for the set of 4 matches ( or more matches if specified on the first tab and clicked).
if you put 7 in for the number of team pairs then it would be win loss combos for 7 pairs of teams.
I would simply like to know how to create a basic ranked leaderboard through EXCEL or Google Spreadsheet, which i will later on implement on a community forum. I would like 2 entries for the leaderboard column titles, being:
Column A: PLAYERS
Column B: POINTS
Then under A i will be typing all players manually, and similarly for B with points.
I am trying to create a workload spread sheet for work I need to have the same looking spread sheet 365 times but also need each sheet to be dated, EG (tue,01/04/2014 through to tue,31/03/2015) I can create 365 tabs that have the same spread sheet on and I can create dates but not do both at the same time, it isn't fun doing copy and paste 365 time.
View 1 Replies View RelatedI am trying to create a macro to insert monthly P&L figures into a spreadsheet.
I am needing the macro to insert a column to the left of a column titled 'movement' each month and copy&paste my monthly figures in.
I have recorded the below macro but my inserted column is always moving into column E and pushing the prior month to the right, where I need it to always be situated to the immediate left of the movement column, e.g. if the macro is inserting September P&L data I need it to be situated between August data (currently in column E and movement data currently in row F and so on.
Column C D E FItem
July
August
Movement
Fees
26,500
28,000
2,000
Assets Ongoing
150,000
160,000
10,000
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("P&L Current Month").Select
Range("D11:D127").Select
[Code] ...
I am trying to create a spreadsheet to calculate the macro nutrient ratio of foods based on carbohydrates, proteins, and fats and how many units/servings I eat of each. I've attached the file I've started. It's fairly straightforward and simple, thus far.
I would like to be able to associate a food with its various nutritional data in its row (fat, carbs, sugar, fiber, sat. fats, protein.. etc.). With all of my food items entered, I would like to be able to choose from a list, have it prompt me for how many units I've eaten then plug it into my formula to calculate totals and ratios.. The final flow should go soemthing like this...
1. Select a food from a list (each food on the list has its associated characteristics)..
2. Select how much ( each food will have an associated serving size i.e. 1/2 cup, 1oz..) ---- (4) oz VS. entering each unit in A1
3. Make these totals of each characteristic feed into my final chart with which to track and graph.
I have a main spreadsheet that I am consistenty adding information to. The columns are : Company name, Contact, Territory, and Status. The main spreadsheet is titled "Main". I want 5 additional spreadsheets in the same workbook that are automatically pulling information from the "Main" spreadsheet, and populating the appropriate spreadsheet . For example...I have 500 entries of different companies in "Main". All of these companies are either categorized as "North, South, East, West, Offshore" in the Territory column. So, I would like my workbook to have 6 tabs...one "Main, North, South, East, West, Offshore." As of right now, I am Sorting the column, then copy and pasting into correct spreadsheet manually.
View 14 Replies View RelatedI am trying to create a spreadsheet that will automatically increase the work order number by 1. The cell will always be in the same place. The idea is that when the file is opened it populates the number. After being closed and reopened the number will be 1 higher than the previous.
The page will reused by various people to create and print work requests for my mechanics, I want it to assign the W/O automatically.
create a macro that will find and calculate based on the details of 4 columns on the same spreadsheet.
Basically, here is what I need to do:
1. Need to calculate how many QTY IN (Column F) and PALLETS IN (Column I) of a specific PART# (Column A) and LOT CODE (Column B) For example if enter a PART # and LOT CODE, it will calculate how many QTY IN and PALLET IN of that PART # and LOT CODE.
2. Need to do the same for QTY OUT (Column G) and PALLETS OUT (Column J) also.
3. Output should contain the PALLET # , QTY IN and PALLETS IN based on the PART # and LOT CODE search. I just want to simplify the search function in this spreadsheet because it is very cumbersome if you need to find how many items left for that PART # and LOT CODE.
Attached is the spreadsheet..
