B20 is the number that the user enters. It can be any number and it will create new sheets below and text of sheet name in B21:F21.
If i change the sheet name below, B21:F21 would also automatically change.
I'm editing some else's workbook. He has a button on sheet 1 that creates sheets 2 ad 3. I can see and edit his old code. I need to add a differet button to these sheets and 3. I can easily do this once those pages are created, but I need those buttons to come up as the sheets are created from the button on sheet 1. T
View 4 Replies View RelatedI am trying to use a macro that creates sheets and places formulae in cells but the resulting formulae are not what I coded. Here is similar
Function CreateSheet(i As Integer)
Dim CellAddress As String
Set Adjustment = Worksheets("Sheet1").Cells(6, 3 + 4 * i)
Celladdress = Adjustment.Address(ReferenceStyle:=xlR1C1)
With Selection
.Offset(22, 9).Formula = "=Sheet2!B14*Rating!" & CellAddress
.Offset(23, 9).Formula = "=Sheet2!C14*K4*Rating!" & CellAddress
.Offset(24, 9).Formula = "=Sheet2!D14*K5*Rating!" & CellAddress
End With.............
i have the formula =CHAR(INT(RAND()*25)+65) which creates random number when dragged from say C1 TO C6 however i need the 3rd 4th or 5th character to be a random number between one and 9 is this possible?
View 9 Replies View RelatedI need a formula that will add .1, .2, .3, etc. to a repeating list number. See the example below.
Project Work Id
8069000
8069000
8069000
8069000.1
[Code]....
Is it possible to assign values to names in a list, so that when you validate it as a drop-down list, you can select a name from the drop-down and it's corresponding value will be added to separate cell? Basically, I have survey questions which have five possible responses: Excellent - Very Good - Good - Fair - Poor. We want to assign a value to each, 5 for Excellent, 4 for Very Good and so on, so that when a response is selected from a drop-down, it's value appears in a separate cell (so that we can calculate a total and average score from the selections).
View 5 Replies View RelatedMy company is soon to be rolling out a new payment system, and I'd like to be able to track commission for everything I sell. Briefly, we get paid via a percentage of the company's gross profit, and the way in which the company gets paid is tiered. (the cellphone business)
So, If a customer activates a plan thats below $39.99, we get paid X amount. If it's below $59.99 we get paid Y amount, and so on and so forth, there are multiple different tiers.
What I want to do is set up a list via data validation so that I can pick what plan they have (via the name of the plan) and have it return a numerical value (ie $39.99) and also have the ability to pick text messaging, etc as options so that the workbook will add the monthly rates (39.99 + 14.95) and then have it return value for the right priceplan tier.
If this is confusing, I apologize for not being more clear, but attached is an example. The top one is what I'm actually trying to code, but the bottom is completely filled in, so you can get a more clear sense of what I'm trying to do. Honestly I'm not even sure if it can be done.
I have set up a number of arrays e.g. DataSetA (1 to 100), DataSetB (1 to 100) etc.
Is it possible to use a string variable (e.g. DataIndentifier) to identify and assign values to certain arrays.
For example, under certain conditions
DataIdentifier would be set to "DataSetA" and under other conditions
DataIdentifier would be set to "DataSetB".
Having done this, I want to assign values such as DataIdentifier(1) = 3.4 or DataIdentifier (10) = 6.2 etc.
This obviously won't work because DataIdentifier is defined as a string not an array. How do I enable the string DataIdentifier to be used as an identifier for the array I want to assign values to? Therefore if DataIdentifier is set to "DataSetA" then effectively I want something like "DataIdentifier(1) = 3.4" to assign 3.4 to the array DataSetA(1).
I do have a data set from A1:C8 as follows:-
Brand Name(A1)Sales Value(B1)Margin(C1)Levi's100002000Arrow150002500Allen Solly120001300Hanes80001000John Miller250004500Disney280003200Ray Ban180002100
The above brands are categorized as follows:-
Brand NameProposed CategoryLevi'sConsignmentArrowBrandAllen SollyBrandHanesBrandJohn MillerPrivate LabelDisneyBrandRay BanConcessionaire
I would like to put formula from D1:D8 in such a way so that the Proposed categories are extracted automatically through formulae. This will help in getting the contribution calculated from different categories.
Brand Name(A1)Sales Value(B1)Margin(C1)Category(Desired Result)(D1)Levi's100002000ConsignmentArrow150002500BrandAllen Solly120001300BrandHanes80001000BrandJohn Miller250004500Private LabelDisney280003200BrandRay Ban180002100Concessionaire
I have two tables below. First table contains the list of domain names (given just for example, actual list may contain several more such Domain names) with the group names assigned to them. Second table shows the criteria for assigning them the group names:
Sheet2ABCDEFG1Table 1Table 22Domain NameGroup NameDomain NameGroup NameDomain NameGroup Name3NCG-W3K-SAT14-0DOSSNCG-W3K-EEC-SUN16-3EECEXCEEC4SVL-WIN-SAT08-0LABSNCG-W3K-SUN16-0DOSSDCDC5SVL-WIN-SAT06-0LABSNCG-W3K-TUE01-0DOSSNCGDOSS6NCG-W3K-MON04-0DOSSNCG-WIN-EXC-SUN21-0EECDMZETIS7NCG-W3K-EXC-MON04-3EECNCG-WIN-EXC-SAT16-0EECSVLLABS8NCG-W3K-EXC-MON03-0EECNCG-W3K-X64-DC-SAT14-0DC9NCG-W3K-EXC-TUE01-0EECDMZ-NCG-W3K-X64-SUN12-0ETIS10NCG-W3K-EXC-SUN15-0EECDMZ-NCG-W2K-SUN12-0ETIS11NCG-W3K-X64-EXC-SUN16-0EECNCG-W2K-SUN20-0DOSS12DMZ-NCG-W3K-SUN11-0ETISNCG-W3K-MON03-3DOSS13NCG-W2K-DC-SUN11-3DCSVL-DMZ-WIN-SUN12-3LABS14NCG-W3K-X64-DC-SUN10-3DCSVL-DMZ-WIN-SAT10-0LABS15SVL-DMZ-W2K-SUN12-3LABSDMZ-NCG-W3K-SUN12-0ETIS16NCG-W3K-EXC2-MON03-3EECDMZ-NCG-W3K-SUN10-0ETIS17NCG-W2K-SAT17-0DOSSNCG-W8K-X64-DC-SUN10-4DC18NCG-W3K-X64-SUN10-0DOSSNCG-W3K-TUE01-3DOSS19NCG-W3K-TUE00-0DOSSNCG-W3K-DC-TUE02-0DC20NCG-W3K-X64-SAT15-0DOSSSVL-WIN-SAT11-0LABS21NCG-W3K-WED00-0DOSSSVL-WIN-SAT07-0LABS22NCG-W8K-X64-SUN19-0DOSSNCG-W2K-SUN17-0DOSS23NCG-W3K-EXC-SUN20-0EECNCG-W3K-SUN20-0DOSS24NCG-W3K-ULZ-SUN14-0DOSSNCG-W3K-TUE22-0DOSS25NCG-W3K-TUE03-0DOSSSVL-WIN-SAT07-3LABS26NCG-W3K-ULZ-TUE00-0DOSSNCG-W3K-MON05-3DOSS27NCG-W3K-SUN19-0DOSSNCG-W8K-SUN18-3DOSS28NCG-W3K-EEC-SUN19-0EECNCG-W8K-SUN22-0DOSS29NCG-W3K-EEC-SUN20-3EECNCG-W8K-X64-DC-SUN11-0DC30NCG-W3K-EEC-THU00-0EECNCG-W3K-SUN17-0DOSSExcel 2007
My concern is to automate this process using a VBA code. How can I search for the keyword given in Table 2 in the first column of Table 1 and assign the group names as it is assigned currently, considering the below conditions and criteria.
1) The correct group name should be assigned to the Domain name containing the keyword given as per criteria in Table 2
2) It is possible that the domain name will include more than one keywords given in Table 2. For example Cell number A7 contains both keywords NCG and EXC, in such cases the preferance should be given to EXC and we will assign the group name as EEC. And in Cell number A12 also contains both NCG and DMZ, again the preferance should be given to DMZ and we have to assign group name as ETIS.
That means we will give second preference to keyword NCG. If domain name contains none of the keywords except NCG as per given criteria then only we will assign grup name DOSS.
Code:
Sheets(Array("Sheet 1", "Sheet 2")).Visible = False
How do I convert the above to using Sheet Codes Names, Sheet1 and Sheet2?
Want to ensure my code will work if the user changes the sheet name.
I know it is possible in Access auto assign a unique ID (primary key?) to each record in a table. I am creating records in a table using Excel and Visual Basic to write to a ".mdb" file without using Access. Is there a way to auto assign a unique ID within the coding?
The reason I ask this is if I had two users writing to the ".mdb" file at the same time, I don't want them to end up with the same ID's for two differend records.
Sub UploadRecord()
Dim DBName, DBLocation, FilePath As String
Dim DBConnection As ADODB.Connection
Dim DBRecordSet As ADODB.Recordset
Dim XLRow As Long, XLColumn As Long
Dim DataRange As Long
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Worksheets("Find Record").Range("A2").Value = Worksheets("Index").Range("H3").Value
Worksheets("Find Record").Range("B2").Value = Worksheets("Index").Range("I3").Value
Worksheets("Find Record").Range("C2").Value = Worksheets("Index").Range("J3").Value
Worksheets("Find Record").Range("D2").Value = Worksheets("Index").Range("K3").Value
Worksheets("Find Record").Range("E2").Value = Worksheets("Index").Range("L3").Value
Worksheets("Find Record").Range("F2").Value = Worksheets("Index").Range("M3").Value
Worksheets("Find Record").Range("G2").Value = Worksheets("Index").Range("N3").Value
Worksheets("Find Record").Activate.........................
How to create a spreadsheet with what I think will be a very simple formula?
If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2.
If date in B2 - date in A2 is 7 or less days but more than 1, put a 2 in cell C2.
If date in B2 - date in A2 is 30 or less days but more than 7, put a 3 in cell C2.
If date in B2 - date in A2 is 90 or less days but more than 30, put a 4 in cell C2.
If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.
OR
Another, maybe simpler, way of saying it is:
If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2.
If date in B2 - date in A2 is 2-7 days, put a 2 in cell C2.
If date in B2 - date in A2 is 8-30 days, put a 3 in cell C2.
If date in B2 - date in A2 is 31-90 days, put a 4 in cell C2.
If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.
I would like to make a list of vehicles with auto servicing dates appearing in the next column. How can I do this, example would be say 30/11/2008 was the last service, next column would be next service due 30/02/ 2009 and then a column next to that to say when service was done.
Pretty simple but I can't seem to get it to auto bring up the next service date which would be in 3 months time or 90 days.
Does anybody have a formula to use to auto enter an alphabet? With this formula I would like the last letter in the alphabet Z to be followed by AA, AB, etc. If I delete row 5 (letter E) I would need the letters to auto generate in order.
View 9 Replies View RelatedI've exhausted my search engine skills and I'm about 99.9% sure Excel is incapable. This message board has been great over the years of figuring out even the trickiest of problems, but is there any functionality in 2003 or the slight possibility that after a user inputs a number in a cell (no tab, no enter) that it will move to the next cell.
After reading about this, most people say it is not possible even with VBA or Macro, and I certainly believe it, but the whiny, and horrible department that I work for are curious if this is possible. So, is it? If this is the wrong area for this questions, I apologize, I was considering putting this in the Macro/VBA area, but I gave up in deciding where to post this.
What I want to do is if I add data into B7 or F7 then the current date will appear in J7. I also don't want the date to change every time the project is opened.
Thus,
If I enter "withdraw or any word" into B7 or F7 - The current date (4-Dec) would appear in J7.
then if I close excel and I open it tomorrow, J7 would still read as 4-Dec, while if I enter "deposit or any word" into B8 or F8, the current date (5-Dec) would appear in J8.
- would need to be able to enter multiple entries on one day as well.
- also i am not familiar regarding macros.
Here's a sample of a completed table I would like to have.
Sheet2 *ABCDEFGHIJKL68*# ofSTART*wk #wk #wk #wk #wk #wk #wk #wk #69ACCTWEEKSWKRATE1234567870110181 $ * * * *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 71110242 $ * * * *10.00 * $ *10.00 $ *10.00 $ *10.00 $ *10.00 ***72120143 $ * * * *10.00 ** $ *10.00 $ *10.00 $ *10.00 $ *10.00 **73120272 $ * * * *10.00 * $ *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 74120381 $ * * * *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 75130151 $ * * * *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 $ *10.00 *** Excel tables to the web >> Excel Jeanie HTML 4
If given columns B and C, is there a VBA code that will look to those columns and auto-enter the payments across the week matrix based on that criteria?
i have an excel sheet where i enter company information including Zip code. the zip codes around here all start with 797 and end with numbers from 01 to 12
i am looking for a way to enter just the last 2 numbers and have it automatically insert the full zip
example:
line one i would enter 01 and 79701 would show up in the box.
I know there are ways to do this, just can't remember its been so long since I've had to use excel.
I have a procedure that processes data in a spreadsheet. It analyzes data in 365 sheets ( named "1" through "365") and creates a table/report with the results. When I run this on a scaled down version of my workbook (5 instead of 365 worksheets), the result is almost instantaneous. When this is on my normal workbook, the initial processing is about 1 second per day on Day 1, and the last day it speeds up to almost be instantaneous. It is not a linear relationship between the processing time (still working to get more exact timing information). Also, even if I limit my processing to 5 or 10 sheets, Days 1-10 are always slow and days 350-365 are always fast. The code being run is in the structure as shown below:
numSheets = 365
For counter = 1 To numSheets
x = CalculateSomething(counter)
Next
Private Function calculateSomething(counter As Integer) As Integer
Dim strCounter As String
strCounter = counter
With worksheets(strCounter)
For i = 0 To someNumber
For j = 0 To someDifferentNumber
'Data analyzed on worksheet
Next
Next
End With
End Function
The functions obviously aren't copied and pasted/functional, but the relevant efficiency stuff should be there.
I'm wanting to make a checkbook register. That part I can do (I am totally an Excel idiot, but I'm fairly decent with Google and I couldn't seem to find anything about this), but I'm wondering if auto-drafts are possible. What I'd like to do is have a table of regular auto-withdrawals (or deposits, for that matter) and then on the appropriate day have them auto enter into the register. Ideally, I'd like them to enter 2-3 days in advance, or even at the beginning of the month.
For example: Netflix on the 1st, Life Insurance on the 12th, Auto Insurance on the 15th, etc.
I have a excel sheet in it. Is it possible? That when in any cell I input data in a cell and press "enter" then it save automatically.
View 8 Replies View RelatedI have code which creates a tool bar which works Ok, however I can't get the statement correct to assign code to it.
View 8 Replies View RelatedI have a list of numbers such as:
A01
A23
A53
A64
A74
A128
B01... the goes on too approximatly D128
and i wanted to know if there is a way to assign the value to the row?
For example:
A23 in column A row 23
A53 in column A row 53
is there any method that achieves this?
I currently have a spreadsheet which I use to do an audit of the office stationery supplies.
There 3 columns at the focus of this question:
F - Number of items in stock
H - Stock re-ordering level
I - Order Required
I currently have an IF statement to tell me if I need to re-order any stock, by comparing the Number of items in stock against Stock Re-ordering level.
The statement is =IF(H1>=F1, "Yes","No")
This works fine under most circumstances. However, in some of the H cells, I have the text "Special" which indicates that the stock will only need re-ordering on a special occasion.
Any value I enter in F will cause the I to say "Yes".
Is there any way to make the word "Special" equivalent to 0, so I says "No"?
In column 'o' I have inserted a number for course name in column 'p'. I want to assign a course name to that particular number as follows:
Column 'o' Number ... Column 'p' Course name
1: A. B. C.
2: J. K. L.
3: P. Q. R.
4: X. Y. Z.
And so on as per course names.
If I enter number 1 in inputbox then I want to enter the course name in column 'b4' respectively.
I have a list of random dates between 09/01/05 and 07/01/06. I would like to assign a week number to each date. For example, I would like it to be week 1 if the date is between the dates of 09/01/05 and 09/07/05. Is there any simple way of going about this?
View 3 Replies View RelatedI having a bit of trouble entering numbers in Excel, every time I try to enter a number it seems to divide it by 100. For example : - 1 entered in to a cell becomes 0.01, 100 becomes 1....etc. I've tried formatting the cells and had no luck, tried Tool>Options but not sure where to look.
View 2 Replies View RelatedI want to enter a name in a cell from a list of 70 names I have on another work sheet. By just typing the first few letters of the name into the cell I need it to complete the entry. I do not want to use a drop down box it takes too long to find the name. I need a formula to do this for my spread sheet.
View 6 Replies View RelatedI use the same number as a lookup value in several sheets(say 1-10). The number can only be changed in sheet 1, the others referring to it. So if you want to use a different value in say sheet 9 you have to return to sheet 1 and change it, then back to sheet 9. Is there any way that a number can be inputted into ANY of the sheets and change ALL of them, without using macros? I've searched long and hard for the solution to this, I'm sure it's either a trivial answer or a trivial question that doesn't deserve an answer.
View 9 Replies View Related