I have a formula that calculates the number of rows in a spreadsheet. I would like the results of that formula to complete a range description. For instance, if the range would be expressed as A1:A20 (for a file with 20 rows), and the result of the formula (the cell enty) for a different file is 35, I would want the range to be expressed as A1:Ax, where x is equivalent to the result of the formula (effectively A1:A35, but expressed with a reference to the cell with "35" in it, not the 35 itself).
I have created a very simple form. I want the user of my spreadsheet to fill in 1 cell in each section of the form, but they will have a choice of a couple of cells to fill in. For example, they can fill in one cell with the temperature in Fahrenheit or another cell with the temperature in Celsius. I want Excel to stop the user from putting data in more than one cell. (This way they can't put in a value for both boxes, which could confound their outcome.)
I have a range B1:B20. These cells are populated by the user and I want to ensure that no two entries are the same. Is there a way of presenting a dialog box that prompts the user to enter a different value if the value they are trying to enter is already entered somewhere in the range?
I have a defined range "Values" within my worksheet, which have no content. Within an existing sheet formatting macro I'd like to force the user to enter values into the cells until all the cells in the range are populated. The values must be whole numbers.
I can get a simple msgbox to appear saying that the range must be populated, but can't add any data whilst this is displayed.
Ideally I'd like the message to be displayed all the time the range contains an empty value, enter the values in the background, then have the message disappear and the formatting macro continue when all the values are populated with correct data.
I reckon I need some kind of ‘loop’ procedure but could do with some pointers.
but i need to have it split in to individual cells so
Cell A1 would be 101 hampton court B2 Hampton Heath C2 Hampton Town D3 Hamptonshire E5 HA01 1AS
each part of the address is split by a comma, so i have tried to use that as a identifier as to where that part of the address is, but failed on that, i can separate out the first part and the post code with a find and replace but not the middle.
also i need it to work backwards ie
it finds the post code first,
then the county
then the town
as those 3 are always the last 3 parts, but the address could only have 1 line of addres beofre the town or 3, and it would get messed up as all the post codes, county ans town needs to be in their respective columns
I am trying hard to use excel macro to round up cell value to next complete figure when it reaches to 0.6, however it should show the first five "0.1, 0.2, 0.3, 0.4, 0.5 exactly. I am giving increment of 0.1 digit to A1 Cell, it is going well. I am unable to get the next complete figure when 0.6 comes. Is it possible in macro.
I have got stuck on one piece of my code and having trouble fixing... Overall I am trying to find variable station name in cell L2 of Sheet 2 in Sheet 1 and then select and copy the data from the data in "cell L2 of Sheet 2" to the last entry of that row. I have attached an example test spreadsheet of the data and a macro is within Sheet 1 called test1. Please note that cell L2 in Sheet 2 will always be different station name and the station list in Sheet 1 will change with differing station name.
The code I am using is:
[Code] .....
The code that is not working and bringing up an error is:
I have a spreadsheet with date and time values of the format "dd/mm/yyyy hh:mm" in column A followed by some other data in cells of that row.
What I'd like to do is have a macro that will delete a complete row if the time value in column A lies between two times that I can specify in the macro (the dates are irrelvant)
I found this code for an auto-complete function from this website: [url] It works well, but see the web example before using it. You have to name a range MyList with your potential values for it to work.
Currently, you have to click the drop-down arrows to see the potential entries. I want to make it so that after the user enters the first 3 characters it will automatically "drop-down" and show the potential entries. This will save a trip to the mouse and let the user see whether their entry is in the system.
Option Explicit
Dim i As Long
Sub KeyEventOn() For i = 65 To 90 Application.OnKey "{" & i & "}", "'MyValidation """ & i & """'" Next End Sub
I would like to have excel automatically fill an excel cell with a predefined list. For example, if you put the letter l it will automatically include the word large. This is like you do when you fill out a form online. See attachment
Example. I have a range of information on sheet 2 in cells A1:A25 that contains values that are text. Another range in B1:B25 that has numerical values. I would like to link the last value entered from those ranges to cell A1 & B1 on Sheet 1. Is there a formula option to do this?
I'm having a problem with a dynamic range. I've re-checked my formula at least 10X and its syntax is correct. The problem is the dynamic range stops short 3 rows up from the last entry.
I have a user form that has a combobox, two textboxes, and a button on it. When the form is loaded, the combobox fills with data from a worksheet I created. THe worksheet has a column with the item names, and another column with the quanity of each item. The combobox is filled with the item names. SO far I have this done. My issue is that when the user selects an item from the combobox, and enters a quantity in to the first textbox, and then clicks the button, I want the quantity in the textbox to add to the quantity cell that the part number from the combobox references to. Then I want this new quantity to show in the cell and the second textbox.
I have created a worksheet with different validation rules for different columns for users to enter data. Is there a way I can make it so each cell must be filled in? I don't want any blank cells. Is it possible to create some so that the user must fill in the cell before moving on to the next cell.
I want to plot a graph where our customers can compare their results against our standards. (line graph) This has been easy in the past but rather than just having a single value as the standard we now a range for each standard a minimum value and a maximum value. How can i get both the minimum value and the maximum value plotted on the chart? This would be two lines with the area between the standards shaded in. The customers could then plot their results to compare against our standards?
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.
[Code] ....
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.
I have to report Actual Headcount every month. Since I cannot add the Headcount numbers in the FY Column. What is the best possible forumula I can use to pick the last month's reported numbers into the FY Column.
For example.
I have 13 columns (A - M). Jan - Dec and the 13th column is the FY number.
This month, I am reporting March numbers in Column C
Column D through N (Apr through Dec) are emplty
I need Column N (FY number) to read Column C and enter the value from that cell.
similarly when I report April numbers in Column D next month, I want Column N to read April numbers and ignore the prior months.
I tried using the min/max function but since h/c fluctuate every month I could not use that. I could use an "if" statement and go back every column but that seems to be inefficient way to solving for this. Any thoughts?
I have a number of named ranges on seperate sheets. As I add more entries to each sheet, the named range is naturally increasing so I dont have a fixed cell refence Effectively what I would like to know is - is there coding that would allow me to copy one named range - and paste the values to the the row beneath the last entry in a second named range. Filtering these I can do - it is just the copying that has me stumped
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.
Is there a formula that will allow me to look for the existence of any number value in a row of one worksheet and then return a specific number value in a cell on another worksheet? For example, if the formula finds any number value it will always return the number 15 to a cell on another worksheet.
I used a form with textboxes for data input for one of my vb macros. Currently I have the private sub from the form transfer those entries to a remote cell on the spreadsheet, (like in column "HZ"), so that the macro that will actually utilize them can retrieve them. Is there a way to pass that data directly from what is entered in the form in the textboxes to the macro that will actually use them?