Automatically Generate Relationship And Keycode
May 31, 2006
I have a excel file which contains Regions Cities and Suburbs in a single sheet. What I need is Regions, cities and suburbs in seperate sheets. Also each must have (except for Region) a unique ID (starting from 1) Name and then the ParentID.
Example:
RegionID Region - for region
CityID City RegionID - for City
SuburbID Suburb CityID - for suburbs.
I have attached a sample file for your consideration.
View 2 Replies
ADVERTISEMENT
Dec 4, 2006
I have build a form in Excel. I would like to make one field (ID) to automatically generate the next number.
View 9 Replies
View Related
Nov 10, 2009
A1, B1, C1, D1, E1, I1, J1 & D3 all linked to L1 thru S1 perspectively But when I copy the highted box and paste it, A4 automatically link to L4, as well as other fields. create a macro where excel automatically generate the link A1 = L1, A4 = L2, A7 = L3 and other fields perspectively all the way til the end?
View 3 Replies
View Related
Aug 3, 2007
In column A on sheet1 I have a list of unique numbers . On sheet2 I have a list of unique numbers that are available. I am currently using this formula,
=INDEX(Sheet2!A:A,MATCH(TRUE,INDEX(Sheet2!A:A"",0,1),0),1)
to look at sheet2 and tell me what number is next after I copy it to sheet1. My problem is how do I automatically generate the next number onto sheet1(by double clicking) and have that number removed from sheet2?
View 4 Replies
View Related
Apr 10, 2007
For non-blank cells in the range C11:C65536, I want to have a hyperlink automatically generated (in the same cell), using the cell value as a relative variable in the link itself. One intended use for this is to populate the range with stock symbols, and have it generate hyperlinks to the following address:
http://news.moneycentral.msn.com/tic...asp?Symbol=XXX
Where XXX is the stock symbol in the given cell.
View 2 Replies
View Related
Feb 20, 2014
I have a workbook in which users record the time spent on various activities. I have some code which will copy the worksheet template and create a worksheet for each user (in a defined list of names). Then each worksheet needs to be password protected so only the user can see it (I can do this individually by manually writing in each sheet name and setting a password but will have 80+ users for some groups and it will take ages...)
What I would like to be able to do is also automatically generate a password for each user (combining a word and automatic number e.g. "EMidsTeam123") it may be possible to insert this into the code I already have (which I have copied )...
VB:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim strPass As String
Dim lCount As Long
If Sh.CodeName <> "Sheet1" Then
'Set sLast variable to the last active sheet This is then used to return the user to the last sheet they were
[Code]....
View 6 Replies
View Related
Sep 10, 2013
I have a marketing calendar that I need to update just about daily with new promotional plans because the dates change very often and there are a lot of markets that we are keeping track of. Because the format of the excel is basically a bunch of merged cells to show the range of dates of a particular promotion, it takes a very long time to unmerge and remerge everything when one date changes.
I have an example of what the graph looks like below, as well as a grid of what I would like to use to generate this graph automatically using the start and end dates so thats all you need to change. If you changed the start dates, the market name, or the promo name, the chart would reflect the updates.
View 2 Replies
View Related
Apr 17, 2014
I currently have a large spreadsheet that multiple people fill out. Each person fills out all the information in a row. At the end of the row, I would like a button that says "Generate Form" so that when clicked, a new sheet automatically opens with a template form that I created and is already filled out with the information that was just inputted into the spreadsheet. Also, there are four different template forms that could generate. For example, there are forms A, B, C and D. If the user inputs "B" into the first column of the row, then when he goes to click "Generate Form", a new sheet is created with all the information filled out in Form B.
View 3 Replies
View Related
Oct 25, 2012
I am using the following piece of VBA code in Excel 2007 to automatically generate an email when a button is pushed:
Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
Dim r As Integer, x As Double
r = ActiveCell.Row
'Get the email address
Email = Cells(r, 3)
[Code] .....
The button to launch this code sits in cell AK7. What I would like to do is have the macro copy the email address present in cell E7 into the email address section of my created email. In addition I would like the reference provided in cell AJ7 to appear in the Message Subject part of the generated email in the format " RCS Reference contents of cell AJ7".
View 3 Replies
View Related
Apr 8, 2014
i want to generate sheet automatically from existing sheet applying auto filter on specific column. i attached the sample which 3000 rows actually i have more than one lac rows so i have to put filter on sub_div column and then copy and paste to another sheet and give the name of sheet like F21. i want to do automatically this provide vba code or function for this
View 3 Replies
View Related
Nov 26, 2009
I would like to get to a stage when I can type in a part number and it will bring up other part numbers that were ordered with that part number. Ideally the top ten rather than having a huge lists of hundreds of parts.
I.e The top selling part number in the list is 11511607, I can easily make a formula to bring back all of the order numbers this was ordered on but I don’t want the order numbers, I want the other part numbers on the order in which this part was ordered. I am trying to get to which parts are commonly ordered together and thus should be stored close together in the warehouse to speed up the picking processes.
View 3 Replies
View Related
Jun 8, 2009
Got two spreadsheets that should be identical, and in both the column widths ARE identical - when you look at the no. of Pixels. However, the other width is different. So what's the relationship between width and pixels? Is this difference to be expected?
View 5 Replies
View Related
Feb 20, 2013
How to copy paste data as a relationship table.
In attached workbook...when user will click on "Relate" button on "rawdata" sheet....then data should be visible in same form as showing in "relate" sheet...
Book2.xls
View 1 Replies
View Related
Nov 15, 2008
I want to bring in text files that result in a single column and then want to be able to align equal text in the column next to each other. I used to be able to do some moderate programming in FoxPro and used a transaction that was called set relationship to field+field, etc.
If there is no equal text in either of the other columns then I have tried to set it to a column by itself. Of course this comes data comes in all in one column with no spaces. I have searched the help files and the net and just can't find a way to do this. I'm thinking this might be attuned to Access more than Excel but I am not proficient in this. Right now I am doing a cut and paste to make this work but some of the imported text have upwards of 800 lines.
View 4 Replies
View Related
Mar 5, 2009
I have one worksheet with 238 rows and another with 163 rows. I want to pull information into the sheet with 238 rows whenever there is a match to a record in the 163 rows. I tried VLookup, but as soon as it gets to a record in the 238 that doesn't exist in the 163 it gives errrors and stops. I've looked at =offset and =offset(match) but I'm not finding anything that deals with the situation of not always being a one-to-one (or even one-to many) relationship. Ultimately I want to pull the Category & Project fields into the records matching on IDNumb in Sheet 1 but Sheet 1 has more records than Sheet2. example......
View 5 Replies
View Related
Aug 8, 2013
I am collecting data the temperature of an animal, i am also collecting ambient temperature at the same time when i plot the two data sets there is an 89% Correlation so i know the animal temperature is affected by the ambient temperature. i want to be able to take this influence of ambient temperature out of the recorded temperature. I dont think its a linear relationship
View 5 Replies
View Related
Jul 19, 2014
I have the following table and i need to be able to present it an indented list of parent child relationship. The hierarchy can go 1000+ levels deep. How do i achieve this using VBA?
OName
OPackID
PPackID
PName
ParentID
Name1
556
556
Box1
389
[Code] .........
If there is a better way of presenting it, it isn't limited to an indented list.
View 5 Replies
View Related
Feb 19, 2010
I'm trying to find customer loyalty by rep. So I need to find all the accounts that have a loyal relationship with a rep and separate them. All the other reps that sold to those companies are removed. Then I need to find the accounts that still did business with us from year to year, but not with the same rep. Any not loyal accounts are also removed.
The ultimate goal is a pivot table that shows reps on the vertical axis, years on the horizontal axis, with the data being total number of accounts per rep per year. All of the 2008 accounts will be counted, and of the 2009 accounts, only those that were loyal - retained either by the same or a different rep - will be totaled. For this purpose there is no difference between accounts retained either by the same or a different rep, they will be added together. So a rep may have had 10 accounts they sold to in 2008, and of those 10, 5 bought again in 2009, and the rep also picked up 1 account that bought from another rep in 2008. So the rep's totals would be 10 for 2008, and 6 for 2009. I've attached an example workbook to show you what I mean. A "1" in the 2008 or 2009 column indicates the account bought something that year.
View 2 Replies
View Related
Feb 7, 2008
I have an excel file (see sample attached) with a hierarchy with parents and children spread across different columns and rows. However, I am trying to condense it into one column with parents and one column with children. The only way I've been able to do this is by copying and pasting parents onto children rows, which is both a manual and time-consuming process (when you have a lot of rows). I feel like there must be a way to use VBA or a formula or something to get it to work. I have attached a sample, so you can see what I am trying to do (current data and desired data).
View 6 Replies
View Related
May 30, 2008
I'm an experienced Excel user working with very large Excel files that contain many sheets with thousands of formulas referencing various cells on different worksheets. I've been searching for an advanced solution for Excel and I am actually having doubts as to whether it actually exists on the market. (If it doesn't take this idea for free!)
I need a program that could generate a visual map that displays different cells and the links among them. For example, such maps are used in social networking to show connections among people - an example here.
In the end I would have a similar map and it would show me which worksheets are more or less isolated (i.e. don't reference directly any other cells).
View 2 Replies
View Related
Oct 8, 2006
I have been working for three straight days (and nights!) to accomplish something that is very simple in other languages, but I can't make it work in VB
Column X has, with spaces between 4 possible contents:
3Q
FM
1Q
NM
St1 St3
3Q today
tomorrow
-2
-3
-4
-5
yesterday
FM today
tomorrow
-2
-3
-4
-5
-6
yesterday
1Q today
tomorrrow
-2
-3
-4
-5
yesterday
NM today
tomorrrow
-2
-3
-4
-5
yesterday
3Q today
tomorrow
-2
and so on for 1500 rows.
I can write this in Lotus in about 2 minutes, but VB has me stumped. I hope someone can put me on the corrrect path.
View 8 Replies
View Related
Jan 22, 2008
I am looking to select two values from a range based upon their relationship to the maximum value. In this specific case I am trying to calculate the Quality factor based upon the frequency response of a transducer. So I need to identify the Maximum impedance and its corresponding frequency. I also need to identify the 3db points either side of the maximum impedance.
The 3db points are found at 2/3rds of the maximum value of the impedance (Impedance Mag in attached spreadsheet). Calculating the first 3db point isn't overly difficult as I just calculate the value for the 3db point and use the MATCH function to find the closest Impedance value and then work out the corresponding frequency from that. I am stumped as to how to calculate the second 3db point (above the Max impedance value). I have attached a spreadsheet with frequency information in column A, the other columns are used to derive the values found in columns M (Impedance Mag) and N.
View 5 Replies
View Related
Feb 6, 2009
This is a spreadsheet for a real estate office. The boss gave me a spreadsheet that he created to give a seller an idea how much money he will walk away with at closing. The spreadsheet includes all of the pertinate numbers but there is one item that he would like to have filled in automatically. When selling a house, you use a title service that charges a fee based on the price of the house. So, for example, If the house sells for $0 to $50,000, then let's say the fee is $112. If the house sells for $50,000 to $60,000, then the fee is $118. If the house sells for $60,000 to $70,000, then the fee is $125, and so on up to 1.5 million.
The boss' spreadsheet includes 5 different selling price scenarios, which I guess doesn't matter since you do the first one and the rest follow the same pattern.
So how do I automatically fill in a cell based on how one number falls within a range of other numbers? The fee schedule will be within the same spreadsheet, if that matters.
Ideally, I think they should be separate files, but this will be used in various locations as different realtors take it home or use it on their laptops.
View 9 Replies
View Related
Apr 24, 2014
I want to create a report using power pivot while creating the relationship between the linked tables, power pivot is throwing error "The relationship cannot be created because each cilumn contains duplicate values. Select at least one column that contains only values"
View 1 Replies
View Related
Oct 3, 2008
I have a list of names on a worksheet in cells C9:C:C395. Their respected team numbers are located in G9:G395. What I'd like to know is it possible to put a team number in lets say cell K2 and have a listbox appear with the names of each of the team member from that team.
View 4 Replies
View Related
Mar 12, 2014
I have a big Excel file with 20+ sheets. All these sheets have different margins and layouts.
I need to generate a Single PDF with all these sheets. Is it possible?
When I save the file as PDF, it converts only the current sheet and NOT the whole file. As I said all my sheets have different margins and layouts, I can not combine them into a single sheet.
View 2 Replies
View Related
Nov 12, 2008
I have an excel document that is a form.
Can I use the data collected from this form to generate another new document?
I want the user to enter some data and hit "Create" and a new excel document with that data is created.
Also, the new document has some data in it already. So it has some data and it will get new data from the form.
View 3 Replies
View Related
Apr 27, 2009
here it is..
500 to 599>>>> equal t0 600
600 to 699>>>> equal to 700
the numbers from equals is the scale of the drawings which i use plot.
sample:
32039.98
31959.50 ...
View 9 Replies
View Related
Jan 8, 2008
Analysis of Profit is very impt in business , that is why i keep trying to find way to use excel to generate profit report.
I have been trying to solve this for last one month. even when i bath... But no result.
May i ask any one here know how to convert my data to a report using . Below table said every thing.
Project (10) HIJ1My Raw Data File 2BrandMonthProfit In %3Brand AJan 0730%4Brand BFeb 0750%5Brand AJan 0730%6Brand BFeb 0750%7 8 9I Need Report In New Work Sheet 10 11BrandJan 07Feb 0712Brand A30%30%13Brand B50%50% Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Jun 9, 2009
I have a macro here that is not working becuase User-Defined Type not Defined. I think i understand it but i want to make sure and if there is any advice for the macro below even better.
what is in red below is what i think i need to identify as Type /end Type.
these are named cells from my data page.
Sub Makeworddoc()
' create a word doc
Dim wordapp As Object
Dim data As Range, message As String
Dim Notes As Integer, i As Integer
Dim Participant As sting, POC As String, phone As String, Email As String, Staff As String
Dim Contact As String, When As String
The rest of the macro is
' Collected information from worksheet
Set data = Sheets("data").Range("A13:G13")
Notes = Sheets("data").Range("notes")
' updated status bar
Application.StatusBar = "Creating Word Doc."
' assign current data to variables.............
View 9 Replies
View Related
