Sum Of Numbers But Dependant Upon Another Columns Entry
Apr 6, 2009
I want to show the number of weeks remaining from the sum of a column BUT I want it to distinguish between the entry in another column.
See below.
Columns B to L represent a number of weeks
EG1
Column C: Year 1 has a set amount of 47 and C156 displays this.
C157 should display the amount of weeks in Column C but only when a number "1" appears in Column "Y" C158 displays the total amount remaining
EG2
Column H: Year 2 has a set amount of 38 and H160 displays this.
H161 should display the amount of weeks in Column C but only when a number "2" appears in Column "Y" C162 displays the total amount remaining
View 2 Replies
ADVERTISEMENT
Jan 3, 2010
I've never dealt with vba before and I'm not even sure if that's correct. I wish to lock a worksheet with two cells left unlocked, when an entry is made in one or other of these two cells I need other cells to become unlocked.
View 2 Replies
View Related
May 4, 2009
I would to say thank you to RoyUK for helping me out with this DependantlistBoxes where a Userform has two columns. Now I've manupilated a Userform so that whenever I double click on the cell, the selected text from the second box in Userform is pasted to the cell in the next column to the right.
But I what I really want it to do is to past the text fromt the first box in Userform to the active cell and when move to the second box in Userform, the selected text will be paste to the cell on the right.
Like I double click on B3 (in "Fullarton") sheet, a userform appear. When I choose CB from the first box in userform, it should paste in B3. Then when I choose Electrician from second box in userform, Electrician should be paste in C3.
View 2 Replies
View Related
Aug 24, 2006
I need to have a message box appear if a user tries to enter a value not between 992200000 and 992299999 in the range B11:B1182. I used the following code to test this out (only for B11).
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("B11").Value < 0 Then
If Range("B11").Value < 992200000 Or Range("B11").Value > 992299999 Then
MsgBox "Please enter the Associate ID"
End If
End If
End Sub
The problem is A: I need to apply this to every cell in the aforementioned range. And B: I need to only restrict this when there is a number in the cell (I tried to add the first If statement to do that, but it didn't work).
View 7 Replies
View Related
May 23, 2014
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?
View 1 Replies
View Related
Jan 8, 2014
I have a sheet with thousands of rows of ID's which are made up of a series of letters, numbers, underscores and or spaces. Within the sheet there are numerous duplicates so I need to able extract the numerical element, which is the key data to be able to identify the duplicates.
The numbers I need to extract are mainly 6 and 7 digits long however don't always sit in the same place within the ID. I don't need the 3 digit number sequences, just the 6 & 7 number sequences
e.g
AB_123_456789_Jones
CD_131_7654321_Smith
AB_123_Jones_456789
I've found a formula that strips out all the numbers, however it also included the 3 digit numbers which I don't want. I've also used Text To Columns to break up the ID across a number of columns however that leaves me with 2 and sometimes 3 columns where a 6 digit ID appears
Is there a simple way to extract the number if it's a certain length? i.e. 6 or 7 digits long
View 14 Replies
View Related
Oct 16, 2009
I m trying it out I find in the Win, Loss, Draw columns when there is no entry the formula leaves a 1 when there should be a 0 in the Draw column.
View 4 Replies
View Related
Mar 28, 2009
Is there a way of a user can add a number in a cell and this would hide a different amount of columns.
OR :
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.
View 4 Replies
View Related
Jan 10, 2009
I have a sheet which is a reconciliation exported to excel from our in house system, what i am after is for the code to look at the values in Column F and Column I and copy every entry which is equivalent to $1million AUD TO Sheet AUD1M, I do have a sheet called Exchange_Rates in which the code can look into, they are all equivalent to 1 AUD. I have provided an example in Sheet AUD1M what the macro should do....
View 9 Replies
View Related
Jun 12, 2014
I would like to know how to write the macro that execute a formula everytime it detect an input in another column.
Attached is the example of what I want to achieve.
Macro to perform when entry detected.xlsx
View 1 Replies
View Related
Jun 14, 2007
I am trying to resolve a calculation issue where I want to sum accross columns depending on an entry in the column immediately preceeding. The layout is an Attendance sheet, The columns are for the days of the Month ( 1 - 31 ) and the rows are the Months. There are 2 columns associated with Each day. The first column is for the type of Time Off ( Vacation, Sick, Personal, etc ) the column next to it records the number of Hours some one took off. The work book has a Sheet for Each Employee and a running total needs to be maintained for the amount of "off time" each employee takes by the various time off categories. I have tried setting up range names but this won't work as there will be multiple sheets. I believe the problem is the mixture of Text and Numeric data but could not resolve.
View 3 Replies
View Related
Mar 21, 2008
I work for a charity and I have to cancel the donations of people whose credit card donations have been declined in three consecutive months.
If in Column A I have a list of donor IDs whose credit cards were declined in Jan 2008, in Column B I have a list of donor IDs whose credit cards were declined in Feb 2008 and in Column C I have a list of donor IDs whose credit cards were declined in Mar 2008, is there a way of showing in a fourth column which donor IDs were common (repeated) in Columns A, B and C? I would have a title for each column in A1, B1 and C1, and also the column where the repeated donor IDs would be displayed.
View 9 Replies
View Related
May 3, 2006
I need to compare two colums by number decription for example m344 in one column and fsh344-1 in another. All I want to match is 344. In column a I want to indcate the match by placing an X by each match. View my attachment for reference. I don't know if it makes a difference but the columns are centered in my original spreadsheet.
View 8 Replies
View Related
Jul 16, 2012
I have a userform with several textboxes and 2 comboboxes. The data that I need protected from duplication is based in the comboboxes. If I ignore the other fields and presume the following it may be easier to understand:
The comboboxes are populated by the data stored in sheet1 and when the userform is complete, the OK button populates all the data into sheet2
Combobox1 contains names eg, John, Julie, Bob etc
Combobox2 contains colours eg, Red, Blue, Green etc
I want the userform to allow the data to be entered into sheet2 only if it is not an exact duplicate of the choices in BOTH of the comboboxes
For Example
John Red
Julie Red
Bob Blue
John Blue
These are all ok, however, to try and enter John Red again would bring up a message box indicating a duplicate entry and prevent the data from being entered.
View 2 Replies
View Related
Mar 20, 2014
I have a percentage in R3.
If I make an entry in D13 then I want the R3 to be duplicated into C27 otherwise C27 should be 0.
View 4 Replies
View Related
Sep 11, 2013
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.
View 3 Replies
View Related
Mar 13, 2008
I am trying to do with data validation, trying to stay away from vba on this... and it is probably very simple:
Cell A1, they can select Rice, Cheese, or Rabbit
I want to use custom data validation on B1, so that if A1 = Rabbit, they can only enter 1. If it is blank or the other two choices, they can enter 1 through 10.
Can I do that with data validation? I can't get any if thens to work in it.
View 3 Replies
View Related
Mar 14, 2014
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.
View 3 Replies
View Related
May 1, 2009
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?
View 4 Replies
View Related
Aug 3, 2009
Basically what i need to do is create a validation list, the contents of which will be dependant on the entry in another cell. The catch is that the other cell contains an "if" formula.
eg.
A1: 10
B1: =if(A1=10;"A";if(A1=11;"B";if(A1=12;"C";"Invalid Entry"))) ....
View 9 Replies
View Related
Nov 21, 2006
I want to sum a range of cells but exclude any that are in a particular colour (in this case red).
View 3 Replies
View Related
Oct 17, 2012
I have an order form created in excel with a list of about 1600 products. I have a column set up for the customer to place the ordered amount of each product. What I need now is a way to transfer only the rows that have a value in the "ordered" column to a new sheet. I have seen it before , but don't know how it was accomplished.
View 1 Replies
View Related
Apr 5, 2009
in cell d1 i have
hk/q46559
in cell i1 i need to return whatever is before the "/"
in this case hk
and in cell i2 i need to return the number after the q
in this case 46559
View 3 Replies
View Related
Oct 12, 2008
on sheet1 which is called working sheet and cell d25 is asking what type of goods been sold
i have several different sorts"
ie
s25
s28
s50
s69
s70
s82
abd
t70
t140
i want the spreadsheet to look up somewhere on a sheet called delivery all the components that could be used for that type
so i guess i need to name define?
ie if s25 is selected
on delivery sheet in a20 it would then list all the components underneath each other.
View 14 Replies
View Related
Jul 8, 2013
I have a worksheet with 4 drop boxes that have the following options in order: 2,28,10,24. My main concern is to make the last drop box depend on the third: so the one with 10 listings will determine which of the 24 will appear according to selection. The other two are not necessarily needed to be dependent although, they will be pulling different data according to the selections. How to make the last two be dependent? The combo box selections will determine the values pulled for four different time frames.
View 4 Replies
View Related
Sep 19, 2007
I'm creating a spreadsheet to track orders as part of a project, the spreadsheet currently lists all the different options that can be ordered, it then lists all parts of the order, installation & setup etc.
However it is likely that not all of the options will be used.
What I would like to do is create a few drop down boxes for each option, if 'yes' is selected then the order tracking for that option shows below.
Example, the wants 'internet' so 'yes' is selected for internet using the drop down at the top of the page. Further down the page all the order tracking info is shown for internet. If 'no' is selected nothing is shown for interent.
View 14 Replies
View Related
Sep 30, 2009
In the file I have attached, I need to work out how to give the final total (cell G27) inclusive of VAT, dependent on the value assigned in cells K17-K26, which are explained in the list starting cell B29.
At the moment the value given is net of VAT, and I need the total figure in G27 to recognise that the first item needs to be at 15%, the second at 5% and the 3rd at 0%.
View 13 Replies
View Related
Feb 8, 2010
I have a Filter set up across a few columns of data. Lets say one of the columns is called "Hours", and another is called "User". There is another cell called "Totals" outside of the Filter.
What I would like to have is a self-updating Totals cell that will Sum the total number of Hours for whatever the Filter is currently showing.
Examples:
If I set the Filter to show only UserA's data, the Totals cell will Sum the Hours for UserA.
If I set the Filter to show All Users with Hours greater than 2, the Totals cell will Sum all the Hours greater than 2.
If I set the Filter to show UserB and UserC and Hours less than 5...well you get the idea.
Can I write a function that loops through the data that meets the Filter's criteria? Do I need to create a macro to update the Totals cell each time the Filter is changed? Is there an easier way that I don't know about?
View 2 Replies
View Related
Jan 16, 2010
so basically, im trying to find a way to get cell G3 to have Value X dependant on value from Cell G2. e.g. if cell g2 is 8 and 9, g3=10 if g2 is 11, g3=20 and so on. i dont mind having to add a preset table of values if we need, but it would be set on sheet 2.
EDIT
Value from cell G2 will always be between 4 and 20, set by the user. what i want is that each of those values gives G3 a pre-planned value.
if g2 has value 4. g3 value would be -5
if g2 has value 5. g3 value would be 0
if g2 has value 6. g3 value would be 5
if g2 has value 7. g3 value would be 5
if g2 has value 8. g3 value would be 10
if g2 has value 9. g3 value would be 10
if g2 has value 10. g3 value would be 15
if g2 has value 11. g3 value would be 20
if g2 has value 12. g3 value would be 20
if g2 has value 13. g3 value would be 25
and so on until it reaches g2=20
i was thinking of setting a table on sheet 2 for the g3 values, and get an IF function to do something like IF g2 = a then x, if g2 = b then y etc etc. im just not sure how it would work, unless an INDEX function would work better?
View 4 Replies
View Related
Oct 7, 2008
I've been making a userform to enter data into a list of clients for our school counsellor. I've been using date picker to select the date, which works fine, but is a bit fiddly for a novice user (which the counsellor is) to jump down years.
My half-solution has been to change the data to a very rough guess of their data (so it's no more than a year or so away) - by taking their school year (years 7 to 11) in one combo box, adding 5, multiplying by 365 and taking the that number of days from the current date.
Private Sub CmbYear_Change()
DTPicker1.Value = Date - ((5 + CmbYear.Value) * 365)
End Sub
And although it changes the content of the datepicker date, I get an error when I click my ADD button.
Run-time Error '13':
Type Mismatch
And here's the code for my ADD button.
Private Sub CmdAdd_Click()
ActiveWorkbook.Sheets("Clients").Activate
Range("A3").Select
Do
If IsEmpty(ActiveCell) = False Then
View 9 Replies
View Related
