I trying to require data entry in Cell 2 if Cell 1 contains text. Normally Cell 1 is blank but once the user enters data (its a text field) I want Cell 2 to then become mandatory for data entry. (Cell 1 is actually C5 and Cell 2 is actually DK5. So formula I am thinking if is: If C5 contains data then DK5 must also contain data. If C5 is blank, the DK5 is also blank.
View 4 RepliesI need a macro that will check that the cells B1,C1,D1,E1 and one of the cells F1,G1,H1,I1 are filed out upon entry in A1.
If Not IsEmpty(.Range("A1")) Then
If WorksheetFunction. CountA(.Range("B1,C1,D1,E1")) <> 4 Then
MsgBox "All cells in row 1 are not filled out"
Application.Goto .Range("A1:I1")
Exit Sub
But how do I get it to also check that either F1 or G1 or H1 or I1 is also filled out. If possible I also would like it to beimpossible to add text in more then one of the cells F1:I1. Summary, upon entry in A1 I want cells B1:E1 plus one of the cells in F1:I1 to be mandatory, so in total there will be 5 mandaory cells.
I am currently trying to adapt the following code which is in VBA (Sheet1). It currently ensures mandatory cell entry of cell C2 before saving, and if this is not done then it would not save the item into the relevant directory. I would like to adapt this code so that cells B2 to R2 are all mandatory, and if they are not filled in a message box with the cell title (which would be in B1 to R1) would pop up. If the cells are not filled in then it will not save (the same function it carries out for cell c2 currently). The code is attached to a command button, i have tried to make the additional cells mandatory by playing around with the If Trim(.cells(2, 3).value) part of the code with the AND function etc however i have had not had any luck.
View 7 Replies View RelatedHow can I make a cell mandatory, if another Cell states XX?
For example:
If column B has "agency" then column C must be completed stating which agency was used?
However sometimes column B could say "no agency" therefore column c doesn't need filling!
how can I make a column mandatory?
basically if someone puts data in column C, they have to put data in column D
(people are not putting important information on my spreadsheet, so i would like to make that column mandatory)
I am trying to make different colour arrows appear based on a set criteria, I am having issues with the hide/show amber part, it doesn't seem to be doing the "AND" part.
'Hide/Shows Green'
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value >= 0 Then
ActiveSheet.Shapes("Arrow1").Visible = False
Else
ActiveSheet.Shapes("Arrow1").Visible = True
End If
'Hide/show Red'
If Range("A1").Value = 0 And Range("A1").Value
In Column B of my spreadsheet i have a drop down that only allows users to enter "Yes" or "No"
Is it possible to make Column C a required field if "No" is selected in column B.
So basically if cell b4 is "No" then i want to make it manditory that cell C4 is filled out with a reason why.
Same thing for cell b5 then i would like cell c5 filled out before they continue using the sheet. etc.
We have spreadsheets which record our sales team's prospects and orders. In order for the sales director's summary spreadsheet to be correct, the month which the order came in has to be filled in and correct.
What I would like to be able to do is when column K of a line is turned to "100 - Purchase Order In", a message to pop up saying something along the lines of "Is the Month In correct?"
Looking through previous answers, I think that this is something along the lines of what I need:
Sub Print_Out()
With Range("d6")
If .Value = "" Then
.Select
MsgBox ("Make sure you enter your surname")
Exit Sub
End If
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub
However, I don't know enough about VB Code to know how to change it for my purposes...
How do I make a row highlight based on a given number in a cell...
For example my spreadsheet has a balance column...when the balance shows 0.00 for that line i would like it to hightlight gray
I have a worksheet with running macros which hides & unhide rows based on input of data in yellow highlighted cells (see attached). I'm trying to make all cells mandatory. The below code works but I run into problems when certain cells made mandatory are hidden. For example if I select "Expense" as Type of Expenditure in the attachment cell C10 & C11 are hidden so if its mandatory I won't be able to move forward to next cell.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Me.Unprotect "dawnwh81"
Dim myCell As Range
Dim myRange As Range
[Code]....
I am trying to send my field reps a spreadsheet that will allow them to pick the proper location for each building. The problem I am having is that there are ~45,000 buildings, each with anywhere from 1 - 92 locations. Here is how my spreadsheet is set up.
Sheet 1
Column A has the Buildings THIS IS NOT A DROP DOWN. THIS IS A STATIC CELL PRE FILLED OUT
Column B is where I want the drop down to be available for the rep to pick the location
Sheet 2
Column A has a list of the buildings
Column B has a list of the locations
I understand that normally I would need to create a named range for each building and its locations. However, there are more Buildings than columns in Excel. Is there a way I can do this using Index/Match, or Offset, or Indirect? I have a sample spreadsheet that can be found in my dropbox account using this link [URL]
How would you write a formula where the sum depends on what dates you enter in A1 and B1?
Date cells C1 to C365
A1 start date
B1 end date
Example:
A1 = 1/1/14
B1 = 1/31/14
So cells C1 to C31 will be added.
"=sum(c1:offset(c1,(b1-a1),0))
This formula works, except if you enter a different starting dates, it just counts from c1.
I used this vba code before:
[Code] ......
And it worked perfectly, but now i wanted to use it in an other file but just change the sheets and I keep getting an Error 13.
It should copy the data from sheet insertmeasurements c23 till end of data in the column next to it (is dynamic) and based on cell B1 and the matching category code in column B, put that data in the matching cell in column C to the matching cell (based on the criteria in column B and C) on the sheet storedata.
Attached File : Profitibility Database v1.4.xlsm‎
I've sent out a survey asking people to sign up for one of two groups, A or B. I have a table with a long string of responses, with the name in column A, and in columns B or C there is an X that shows whether they have chosen Group A (column B) or Group B (column C)
I am trying to write a bit of code where it would paste the names into a new table with the headers Group A and Group B. Here is what I have pieced together so far:
Sub Create_Groups()
For i = 2 To Range("B2").End(xlDown).Column()
If Range("B" & i).Value = x Then
[Code].....
I need to create a formula that counts up the number of times a specific word is put into a cell lets say 'Carpet' only when another cell is left blank.
Count the number of times between C1:10 the word 'Carpet' is input only when D1:10 is left BLANK?
If looked into DCOUNTA formulas and SUMPRODUCT formulas but seem to be clutching at straws.
My problem seems to be at referring the formula to the BLANK cell?
G5 - can contain True or false
G6 - contains text but the if is on the basis of this cell being "Hand Delivered" or not
I have tried the following to illustrate what I after:
I have a list of teams, and beside them I need a calculation to be filled.
The criteria is based on a cell reference (a month which can be changed from a drop-down in BM2) and also the name of the team.
dummy 16.xls
In my country, students are penalized when they fail Mathematics subject. That is when a student gets "F" in Maths. The penalty is only for those scoring Division I and II. The divisions change into Division III for those penalized but points remain the same. For example if a student ie Juma gets Division I of points 17 and he fails Maths, he should be penalized and get Division III of points 17. NB: Those who score Division III,IV and 0 are not penalized even though they fail maths.
View 5 Replies View RelatedI'm trying to write a macro that clears the cell in the range V10:X14 if the value of that cell equals 99.
Sub MyDeleteCell()
For i = 10 To 14 Step 1
For j = 22 To 25 Step 1
If Cells(i, j) = 99 Then Cells(i, j).Clear
Next i
Next j
End Sub
It's giving me a "Invalid Next Control Variable reference" I've tried defining i and j as Integers but that didn't work.
I have a range of cells containing text values in BS15:DS50000.
What I want to do is look at each cell in the range BS15:DS50000 and if the cell contains the "" character, then populate text from column BH (if any) as a cell note.
For example, if cell DB42 contains the "" character somewhere within its text, then populate the text in BH42 as a cell note within DB42.
If BH42 is blank, then no cell note. (Also, if DB42 does NOT contain a "", then no cell note either.)
I have a series of data that I am trying to process based on various criteria and though this site I have solved most of the (simple) problems I had. I have one last problem to solve.
I need to add the previous cell to the next one based on a cell value in a different column.
Example: I want to add F2 to F3 if H3=3 but I need it to work so that if H4=3 it will add F3 to F4 and F3 now equal the new value i.e. (F2+F3). If H5 doesn't = 3 then F5=F5. I need the output to be a value not just an equation as I plan on going back and deleting all rows with H=3 in them.
P.S. I don't mind if the output of the calculation of the F column is to a new column or overwrites F. Overwriting would be easier for when I go back through and delete rows based on H=3.
I want to parse a cell if a date is present for example I have 03/26/1985 AL in one cell and want to parse to two different cells however in between those cells I have other cells with different data that I dont want to parse. Basically I need to parse the cells only if a date is present.
View 7 Replies View RelatedI am trying to create a macro that selects an entire row based on a date criteria. The explanation is as follows: I have fields in an excel sheet such as: Name, Date, Place, Time, Country etc….. There are many rows with data in them…. I require a prompt box that asks for a date range and then selects all the contents matching that criteria and copies into another excel sheet called Export.xls.
View 5 Replies View RelatedI am watching 100 stocks when the stock market opens at 9:30 EST. Not all the stocks will come available to buy or sell at 9:30 but will become available at different time intervals, sometimes 10 minutes after the market opens. When a stock opens it is common for it to spike up, then spike down, then go into a "normal" trading pattern, this is called a slingshot pattern.
If I have a predetermined price up or down for 100 stocks, how can I write a macro that will look at the stock prices and if it shoots above or below a certain value it will submit a buy or sell order? (I already know how to submit the buy or sell orders, just need to get an idea of how to get the macro to constantly check the prices and if it meets my criteria to take action.)
Note: I already have a macro running at one minute intervals to collect data. One minute intervals is to long, I need it in second intervals or less to pick up the slingshot pattern. Is this possible?
I need to write an excel function/macro/code to assign yes or no to a cell if the Description fits a certain category. I have attached a sample workbook and will explain what I mean.
In the sample workbook, there is data on a company. This included a unique ID, city, state, and description. What I want to do is fill out the yes/no column. So if a particular company has 2 of the 4 necessary "Descriptions", then column G will return yes, otherwise No. It must return yes for a particular company/ID if 2 of the "Descriptions" are "Sale", "Service", "Business", or "Par". I only want it to count at least two unique descriptions.
For example, in the sample workbook, Company A has all 4 of the necessary descriptions, thus everything in column G for this specific client should be yes. For Company B, only 1 of the 4 descriptions matches, so it returns No for all Company 2 columns. Company C has 2 of 4, so it returns yes for these columns. Note, it doesn't matter for Company C that "Business" was listed twice. It only needs to count the first unique time that description occurs.
filter my pivot table using a cell value, rather than manually selecting from the dropdown.
The report filter title is Rnd H, and values start from 0 through to 2, at .1 intervals (so 0.1, 0.2, 0.3, 0.4 etc).
Rather than select "0.3" manually from the dropdown, can the pivot table do this if "0.3" was written in cell A1 for instance?
I need to first merge cell entries of column 2 if cell entries of column 1 are the same; following columns always retain their corresponding first row (see cells highlighted in yellow in attachment).
View 14 Replies View RelatedI'm trying to create a macro that takes data from one row and inserts it into a new row. Attached is a workbook with a before and after example of what I'm trying to do.
Each row has a security transaction that includes principal cash and interest. The data needs to be formatted so that each transaction has one row for principal and one row for interest.
Principal is identified by the tran code PAYDOWN in column C. Interest is identified by the tran code INT. However, the raw data generated includes both interest and principal under the tran code PAYDOWN.
There could be 100 or 1000 transactions generated based on the day.
I would like a simple macro that would actually move a cell based on criteria. In my case it would be: In row A, if a cell starts with 'Agent Name' then that cell needs to move down one cell replacing the contents of that cell.
View 3 Replies View RelatedI am trying to use VBA to move data from one column to another while using values of a third column as a reference for where each value in the first column should be moved to in the second. I will try to explain as best as possible, currently I have a macro that copies from one column to the required but due to the irregular spacing of the data it results in the data not matching with the data its being copied into.
Say I have Three columns A, B and C. I have a range of data in column A say dates that have irregular amounts of spacing between them. Column B is empty. Column C contains Data that is again spaced out irregularly and not in line with that of A. The value of column C cells is just identical values. Now the aim is to move the values from column A in the order which they are listed and place them in Column B next to each value in Column C. So say the repeated value in column c is EXAMPLE then everytime EXAMPLE appears in column C i want to move one date from column A into column B next to this EXAMPLE value, in order which it appears in column A. So the first date will move next to the first EXAMPLE value and the second date which appears next to the second EXAMPLE and so on and so on until it reaches the end of the sheet.
My thinking was placing the A column data in an array and doing some sort of sort.