I'm trying to create a spreadsheet that will add a value to one column from one specific cell. For example: I want to enter a name in cell G10 and add that entry value enters in A1, then erase that name and enter a name in G10 again and that entry value enters in A2, and so on and so forth.
I am having a sheet where I keep track of when online surveys have been sent to users. The users enail address (column K) may be on the list for several times, but I need to make sure that there is at least a 7 days pause between sending the first mail and the second, depending on the visit date (column G).If there are less than 7 days between two entries with the same email address, the user is not qualified for taking another survey.
I was thinking to write an IF formula which returns either 1 or 0 and then let conditional formatting highlight and HIDE the row via a VBA loop.
We need to have various users input data into a spreadsheet, so I have created a userform. Just need some code with the submit button to populate the central sheet, I think that I will need it to open the sheet, paste the data, save and close it.
This is my submit button as it stands, with the form and spreadsheet in the same workbook:
Private Sub btnSubmit_Click() Dim RowCount As Long Dim ctl As Control RowCount = Worksheets("Sheet1").Range("B1").CurrentRegion.Rows.Count With Worksheets("Sheet1").Range("B1") .Offset(RowCount, 0).Value = Me.coboPropSalutation.Value
I have a spreadsheet where for traceability purposes, I get some vba to enter elsewhere on the sheet, the date and time that data is entered into a cell. Is it possible to also somehow capture the name of the person entering the data. We are on a network, so the pc does "know" which user is logged in.
Is there a way of a user can add a number in a cell and this would hide a different amount of columns.
Ideally I would prefer a drop down with dates and the user could select a forward date and all the columns with dates up to that would appear. This would mean they could look as far our as required and all unwanted data would be hidden.
I'm learning 'on-the-job' to code VBA macros and about a week ago I asked a colleague to test (UK/Australian) date entry into a textbox on a userform. I wanted to ensure users can enter virtually any acceptable date format. About 10 minutes after sending the colleague the workbook, she advised that inputting 29 February and a year that is not a leap year had the effect of showing the textbox date in reverse and transferring that date to the workbook with the year indicated as "29" eg. 06-Feb-29.
Since then, I've spent a good deal of time seeking a correction to the code. I've tried scripts for 'If IsLeapYear' with the 'MonthLength = 29' etc, various other if statements and shuffling the original date order of my code. The unadulterated code is below. Does anyone have an answer (other than an Error Handler with a msgBox, informing the user that the date format is wrong)?
Private Sub txtMonth_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) Dim dDate As Date dDate = DateSerial(Year(Calendar1), Month(Calendar1), Day(Calendar1)) dDate = txtMonth.Value txtMonth = Format(dDate, "dd-mmm-yy") On Error Goto EndMacro Exit Sub EndMacro: MsgBox "The date format you've entered is not a valid format!" txtMonth.SetFocus
Basically at the moment our static data group use a generic sheet which lists all possible field variables. However, what I want to do is design something which only shows the fields that need to be filled out (ie if the field is shown it needs to be input).
Once the form is filled out it will get saved and sent via email, where it is then printed out and input by our data group. One way I thought about approaching this was to have 18 sheets which are hidden, and which are then selected when a report type is filled out. The other way would be to have IF statements but I think that would get complicated on the one sheet. Just really looking at ideas at this stage.... For example there are 30 fields on the sheet that we use now, but some reports may only need 5 of these fields - I therefore need to "hide" the remaining 25...
I need of a formula that will look through a list of text strings, spread out through two columns) and compare each one to a user defined entry. When it finds a match against this entry I would then need it to compare another set of text strings on the same row as the entry that it previously matched against.
If it was to find a match on only the original text string it would need to return a true result and if it matched on the original text string and also one of the other text strings on the same rowit would need to return a false result.
I currently have a formula to compare all text strings against the user defined entry and if it is contained in the first 2 columns it returns a true result and if it is contained in both ranges of text strings it returns a false result but i need it to apply the rules per row.
Below is the formula that I am currently using if this helps as a starting point. If you need a subset of the data I can supply this.
=IF(SUM(IF('HBO Frequency Projections.xls'!NOT_HOMEBASED"",IF(ISNUMBER(SEARCH('HBO Frequency Projections.xls'!NOT_HOMEBASED,C5)),1)))>0,"NOT HBO",IF(SUM(IF('HBO Frequency Projections.xls'!HOMEBASED"",IF(ISNUMBER(SEARCH('HBO Frequency Projections.xls'!HOMEBASED,C5)),1)))>0,"HBO","NOT HBO"))
I have searched the forum but can't find an answer to my problem. I have a list of about 3000 streets, a sample of which follows:
ARBROATH ST ARCOLA ST ARGO PL ARIES PL ARMSTRONG AVE ARTHUR AVE ARVIN CT ASHGROVE CR ASHLEY GROVE CT ASHWORTH AVE...................
I know how to populate a List Box, but rather than having to scroll through the entire lot I would like to just have to type in a few letters and the output only display streets that start with only those letters. For example, if I type in AS only the following appear in the listbox.
ASHGROVE CR ASHLEY GROVE CT ASHWORTH AVE........................
I have created a simple userform that is linked to a button on sheet1. When the data in the userform is submitted, I set it up so it goes into a database under sheet2. The problem is, I don't want anyone modifying the entries in the database. Usually I would protect the sheet with a password, but when I do that, there is an error when the userform is submitted. The only thing I can think of is to hide sheet2, which doesn't seem like a great solution.
Is there any way to protect my database from being modified yet still allow the userform to be linked to it?
I need to save the data of For Eg. A form in Sheet 1, B form in Sheet 2, C form data entry in sheet 3.
what is happening now is that, all the entries are going in the Active Sheet that is open in the excel file. I want to automate the process of data entry, by making it enter data from specific form in specific sheet.
I have a table that i use for a customer database. and the end user adds new customers to this table, what i would like is for a message box to pop up whenever cell B2 matches an entry in a column in the customers table. the table starts on row 25, and the column i would like to check for duplicates is column B. I would like the message box to give the user the message "A customer by this name already exists, Would you like to load this customers file?" If the answer is yes, then the row that the match was found on would be copied and pasted onto row 1. if the answer is no, then nothing else happens. I hope this makes sense, i am posting this sheet of my workbook for reference.
I have a worksheet in which i am asking a user to enter manually a number in cell E3. Suppose the user starts with 100, then the next time he is entering in E3 he shoudnt be able to enter 100 nor any number less than 100. I dont have a range for the numbers that the user is going to enter.Therefore countif function does not work. Is there any way that i an store the number taht the user enters first in cell E3 and then use that database to avoid the user from entering the same or a number lesser than that number.????
i'm writing a refrigeration selection user interface, working from values on an excel spreadsheet. how to get the programme to automatically select a value from a list or range once a user has selected corresponding value from a list within a combobox. for example if a user sets the temperature of their refrigerator to -5 celsius i need the programme to automatically select the corresponding value of enthalpy for the air at that temperature.
I'm trying to use data validation to restrict the user to only selecting values in a list which I create. Right now, the list is a named range. I'd like to get rid of the range and just use a named list. I create a name using the following as my list.
Insert > Name > Create Name: Fruit
Refers to: banana,apple,orange
When I try to use the name Fruit in my data validation, I get the message "The List Source must be a delimited list, or a reference to single row or column." I thought my name "fruit" was a delimited list.
I have a list of account balances in one column. How can I pick up only the bottom number in that column? I have 20 worksheets and want a total across all worksheets of the last number listed in a particular column but each worksheet is different because they contain a different number of rows with activity. So, if in col. C I have:
$482 $1,000 $899 $456 $231
... then I want $231 to be what is captured for that worksheet, but if the 2nd worksheet contains:
$500 $1000 $344
... then I want $344 to be what is captured for worksheet 2. Does that make sense. In the end I want to sum $231, $344.... etc.
I am trying to reset various (data validation) drop down lists in a the dummy worksheet attached. Most of the code i have come across clears the cells completely, however i would like it to return to the 'select' option (first on the list) in the list.
I have come across this code, but it does not seem to work:
I want the clearing code to run from the command button in the 'instructions' tab to clear the drop down lists in the '1. Inputs' tab.
I have a database file with a list of components. Each component has manufacturers name, part number, description etc. I need to create an individual file from each of the components in the list. I would like a quick and easy way to copy the information in each Row and generate a new file for each Row containing the existing template I have.
I am trying to work up a referral tracker for my wife's employer. I have Named Ranges on sheet 2, a running log on sheet 1. I also have a userform that is being populated from the ranges on sheet 2. I have the project mostly functional, except for a feature I would like to add in. I am interested in adding to the named range that is being accessed if the entry is not in list, via the combobox1 on the userform. Also, if possible, I would like the list to re-sort behind the scenes so the added entry is properly located in A-Z format for next time. After searching the web a bit, I found some code that might work with some tinkering, but currently I am having issues with it. Here is the bit I am trying to use.
I have been getting 424 Object Errors and a few others as I continue to mess with this. I am also attaching the project if someone may see a better way of getting the task accomplished. The overall scope of this is to log all referral sources so monthly and yearly reports could be made.
What i want to do is to store data to the next available row in that list by entering "w x y z" in columns A1 B1 C1 D1. Then when i have new data ww xx yy zz, i again want to enter it in columns A1 B1 C1 D1 and automatically have it stored beneath my last entry at the bottom of the original list.