I currently have an input screen where users copy and paste data from a seperate spreadsheet into a range of cells B11:B15 in the input screen.
A macro is assigned to then extract this data and paste into the recording sheets behind.
I would like to format somehow cells B11:B15 so that only numeric entries can be pasted in and not any alpha including #.
I do not want to send each rep a file only with their own sales figures as this will be very time consuming. If I could send one file to all representatives but restrict their view to their own figures only, this would make life much easier. If necessary I can set up passwords for them.
I have 50 share returns for 2009 (A2:A51). I am solving for optimal portfolio by allocating weights to my 50 shares in 2009. in solver, i am changing cells B2:B51, but the criteria is that if any of the 2009 returns is greater than 75 then the weight on that share should be 0. i tried to write an if function in column C of the changing cells but solver overrides the command when i solve.
View 4 Replies View RelatedI created a Macro to do some copying and pasting. Can I restrict that Macro so that it can only be used from my PC?
View 6 Replies View RelatedI have taken over a workbook that due to various constraints elsewhere I cannot change the structure.
Each of the three columns in question C, E & F have dropdown validation lists.
Column C is either Import or Export
Column E is a list of locations where work is Imported From
Column F is a list of locations where work is Exported To.
Based on whatever choice is made in column C either Import or Export I only want the user to be able to select a value in E or F.
So if Import is selected then a value cannot be entered in F
I am trying to do is extract the volume size of products in 'ml'
from 10k plus products from a description field cell.
this description field could also contain the weight of the product in grams
so I cannot just do a search for a numeric string ,
it has to be associated with the milli-litres statement .
is is possible to do a sort of ' *ml ' search and then select and copy to another cell ???
We published a 2003 spreadsheet for people to enter expense information, but some get cute and add worksheets to it (instead of sending in multiple spreadsheets). How can prevent a worksheet to be added to our spreadsheet?
View 3 Replies View RelatedI am trying to get a macro that converts Japanese digits to their Roman counterparts but does it only in a specific column. My original version just selected the J column and then Cells.Replace ran just fine on just that column. I have discovered, however, that the users may make the crucial column either J or K, so I changed it to search for the header first, and then select that column. It replaces in the entire sheet, however.
Here's what I've got (I've only given you one digit replacement. The What doesn't display properly because this forum doesn't display Japanese):
I have a cell in a spreadsheet which has a lot of text entered. The spreadsheet needs to be used by another application (Business Objects).
My problem is that I need to limit the number of characters entered in the cell to 255 characters. I know I can use Data, Validation to limit the text length, but this only works once all the data has been entered, then a message is displayed.
My ideal soulution would be:
When a user is entering text and has reached my 255 character limit, no more text can be entered;
or
When a user is entering text and has reached my 255 character limit, the text automatically continues in the next cell.
A not so elegant solution (but a usable) would be:
A character counter to let the users know when 255 characters have been reached.
This may be a two part question, I finally was able to build a calender contol to insert dates into a active cell, now what i am trying to do is be able to restrict the usage of certain dates. For instance, dates prior to the current date cannot be chosen, nor dates that fall on 29th, 30th and month depending the 31st. Is this possible? Its is being used to calculate amounts for certain days, for example the calender object places a date in Cell A1, which falls between 1st-28th, another column also has a date option, the date to be chosen cannot be before todays date, and cannot be after the 28th of any given month. Is there a way to restrict in the calender or would a if statement apply? OR would a more simple approach to use data validation? where as (A1=MM, B1=DD, C1=YY) so that D1=A1/B1/C1 ie. 07/22/09, so that when using D1 it would be a date format. which would be the start date and E1,F1,G1 for a new date, then use conditional formatting to restrict or prevent choosing dates before TODAY() for the second date colums, I know a message could be displayed for it if it is less then/prior to the current date, or should i have the data validation adjust (if at all possible)for the first set of date options?
View 11 Replies View RelatedI have cut and paste some code provided by members of this great forum to insert a row in a second worksheet at the same row number when one is inserted in the active worksheet. e.g. If I insert a new line at Row 14 in worksheet "admin" I also get a new row at Row 14 in worksheet "report".
Code is as follows:
Private Sub Worksheet_Change(ByVal Target As Range)
Set sourcebook = ThisWorkbook
Set sourcesheet = sourcebook.Worksheets("admin")
Set targetbook = ThisWorkbook
Set targetsheet = targetbook.Worksheets("report")
myRow = ActiveCell.Row
targetsheet.Activate
ActiveSheet.Rows(myRow).EntireRow.Insert
sourcesheet.Activate
End Sub
However, anything I do in worksheet "admin" triggers a new row to be inserted in "report".... If I change text in any cell, or make any changes at all, I get a new row in "report".
Is there a way to restrict this action to only a line insert?
I'm an intermediate Excel user that can navigate around pretty well, but now my boss wants me manipulate Excel in such a way that I cannot figure out how to do.
He wants the worksheet to be "locked" so that I cannot select another or "the next" cell unless the active cell I'm in has something entered into it. The purpose of this is so that when a manager is buying off (proofing and approving) my work, he can't put his initials in a cell next to the purchased item unless the cell that decribes the purchaed item is filled in. I'm not exactly sure what terminology to use when describing this, so forgive me if I'm being vague.
Can I restrict a user from "moving on" to another cell unless the active cell is filled in? And if so, how do I do this?
I need to find the number of cells containing a specific word. Here's an example:
Site # Visit
1001 12-Aug-2008
1001 LATE
1002 08-Jul-2008
1003 09-Aug-2008
1004 LATE
1005 12-Aug-2008
1005 LATE
I need a formula that will calculate how many "LATE" cells there are.
I want to restrict a textbox to only let the user fill negative values. So first of all he/she shouldn't be able to fill in a text, and only values. And if a value is filled in, then it should be a negative value (automatically).
View 4 Replies View RelatedI want to restrict a textbox to only let the user fill negative values. So first of all he/she shouldn't be able to fill in a text, and only values. And if a value is filled in, then it should be a negative value (automatically).
View 1 Replies View RelatedI was wondering if there was a way that I can have a user only able to edit the worksheet that pertained to them in a workbook of many sheets. So if there was a sheet called "Alicia" and another one called "Love Child"; user Alicia would only be able to update info on the "Alicia" tab and user Love Child would only be able to update their info. Is this possible? Also can this be done for a particular row in a spreadsheet? So if row 3 has Alicia's info, she's only allowed to update the info on that row.
View 3 Replies View RelatedI am trying to write a macro in excel to clear all cells within a range that are non numeric. I seem to be going round in circles trying to find out how to do this.
I assume I have to use the IsNotNumeric(Target) argument but I can't find how to specify the target within a range.
If I have the following cells and values
A1 = 0
A2 = 0
A3 = ""
A4 = 0
I want a vba if statement that makes A5 = "Numeric.
If I have:
A1 = 0
A2 = 0
A3 = A
A4 = 0
I want A5 to = "Non Numeric"
Similarly if:
A1 = ""
A2 = ""
A3 = ""
A4 = ""
I want a5 to = "Non Numeric"
Is there a way to do this without looping through each cell in the range?
I need to test a condition that at least one cell in the range (A1:A10) is not numeric. Is the syntax smth like: IsNumeric=False or smth like that? I cannot recall.
View 9 Replies View RelatedI am looking for macro to paste this formula: [abc.xls] Total BA def'!E12-'[NEW abc.xls]Total BA def'!E12. into any cell that contains a numerical value. the cell reference would change to correspond with each cell it is pasted into. just like it does in excel when absolutes are NOT used. If possible i would not want it to affect any cells that contain headers which would only have letters in them. If this is not possible it would still be worth having the macro replace everything with something in it and i could just replace the headers from an original file.
View 2 Replies View RelatedI am trying to total the number of cells in a column that are 0 or greater but less than 365. I use countif for for a specific digit but cannot compute a conditional range using it. I need to count the number of positive digits btwn 0 and 365. The column is a computation of the difference btwn today and past and future dates. A plus digit means we missed sevice and better be ready for an angry customer.
View 3 Replies View RelatedTrying to make an excel macro that changes the background of a cell dependant if the value is between one number and another or equal to another number.
Cell values =
a1 = 250
a2 = 475
a3 = 715
vba Example:
Case Is > 200 and < 400
colchoice = 4
Case Is >450 and < 550
colchoice = 5
Case is >600 and <700 or = 715
colchoice = 6
If i run the macro the cell background should be
a1 =4
a2 =5
a3 =6
I am trying to code a command button to clear a range of cells if any of them contain a numeric value. For example. If any cells of cell range A1:C10 contain a numeric value then they would be cleared. Not all the cells in the range but only thouse contaiing numeric values. I have tried various methods with not-so-good results.
View 9 Replies View RelatedI have 2 large rows of data next to each other imported from a different programme. Some of the cells in these 2 rows consist of values, some consist of words. I only want to cut the cells with values in these rows and paste them in a new row, called TXid. I have written VB code that cuts all the data in the rows and pastes them onto the new row. Now I would like to cut only the values in the row. How do I select only the values in the cell?
Cells.Select
Cells.EntireColumn.AutoFit
Range("I1").Select
ActiveCell.FormulaR1C1 = "TXid"
Range("G3").Select
Do
If ActiveCell = IsNumber = True Then
Selection.Cut Selection.Offset(0, 2)
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell = ""
Range("H3").Select
Do
If ActiveCell = Isnumber = True Then
Selection.Cut Selection.Offset(0, 1)
Loop Until ActiveCell = ""
The ActiveCell = Isnumber = true is not working the way I want it to.
I have a column of several thousand entries listed as numeric with a scientific symbol eg.
1.4mSv
19.53mSv/1mSv (some have a mix and or alpha/numeric range)
I want to convert them to the numeric value only. I'm extracting to a chart which is not recognising the alpha and throwing the data out. I tried find and replace, trying various options within the 'replace format' tab with no joy.
I have a few thousand products codes (i.e ABCD123BLA08 or SHU267BLA) They are non standardised in length or structure. I wish to pull out everything upto the end of the third numeric digit.( ie ABCD123 and SHU267) I cannot use left as they are all different lengths. Ideally i would serach for a non numeric char after the number then use left up to that point. Search can't do this, FIND can't do this.
Summary
ABCD123BLA08 would be ABCD123
SHU246BLU would be SHU246
I147ORT08-12 would be I147
I have not found a way to control the width of a validation drop down menu. I have 3 drop down list. Two of them are in wide column cells and one a narrow column cell. For some reason my narrow column cell gives me a wide pull down even though the length of the data in this field is only 3 to four characters.
View 2 Replies View RelatedI'm trying to find out exactly how I can use sum product on cells that have text and numeric values. Here's my sample data (6 points):
TN FX1576 20, TN FX1577 25, AZ FX1577 30, AZ FX1577 35, FW FX 1577 40, and FW 1577 45.
I wish to do the following: I want to sum all of the right numeric values based on the first two text values(TN, AZ, or FW). I want to sum these numbers to their respective total cells at the top of the page. So far I can sum the numeric values fine using the following formula: =sumproduct((right(range,2)*1) but when I try to differentiate between the respective locations (AZ, TN, or FW) I get an error message.
Attached is an example of what I'm trying to do along with the formula I'm currently using and yellow shading to represent where I want my respective sums to go.
ExA.xls
I am creating a large vlookup. I have multiple instances of a name, twenty to be exact. I copy the first cell it is name1 and then I paste it for the next 19 cells below. Is there a quick way that I could have it go name2 name3 name4 etc all the way to 20? Currently I am just having to click and change the value manually.
View 11 Replies View RelatedHow do I select or highlight cells from row 1 through row x (a numeric variable) in column A? I'm eventually hoping to shift those cells in column A (from row 1 to x) to the right by one.
View 1 Replies View Related