i wanto set up validation in two collmuns of data the collumns are g and h. the validation that i have set up in g2 is whole number <h2. this works but i want to do this in the whole collumn of g. for example i want the validation in g3 to be <h3.
also the validation in h2 is >g2, what i want is basically to set up a formulae to copy these 2 validations down the colummns,
I am using the standard validation from the tool bar. What I am trying to do is in
check the date in a cell and if the new date is Greater and or equal to another date in another cell. The problem is I am having is when the cell that I am checking for a date is Blank the validation does not work and one can put in a date. I tried unchecking the "Ingore Blank"
Spreadsheet example attached.
Is there a simple function for calculating the number of months between 2 dates.
Eg 1/5/2012 - 8/9/2020
I am doing a club handicap type of scoring awards for a waterski club
the info I get off the internet from each event is like this example below.
I need to convert the scores to a simple number value additionally if possible choose only the highest score since I only use one score from each event. some events have 2 or 3 scores per skier.
Then I have a vlookup table setup and functioning to bring the handicap to my main sheet.
John M44.00/1200
3.50/1200
2.00/1200
MARK M41.50/1125
2.00/1125
2.50/1200
DAVID M43.50/1425
1.50/1425
.50/2300
KEN M42.50/1300
5.50/1425
2.00/1425
basically if you replace the / with @ you have your score of 4@12 meters this being 6 more points than say 4@13 since the 12 meter rope is shorter and more difficult to get around the bouy.
So can or should I use a replace "/" with"@". but then what I ultimately need is like 4/12 need to return a number like 96 and 4/13 should give me 90.
Would a vlookup be easiest? Does excel have a function like this?
Additionally it would be great if it can choose the highest score and bring only that into the main sheet.
is these event do have non club members in then as well, can I using my existing vlookup sheet return only those names?
I need to bring the new pac code NR. from sheet 1 to sheet 2 and when i bring them back only the 1st comes and when i copy past down it comes only the 1st number. I have changed all formats but simply doesn't work.
View 2 Replies View RelatedI have 246 rows of data. Each row has 4 values, reading from left to right.
I need to make this all into one simple column, making 984 rows (246 x 4).
I am exporting data from a database and will want to split up the information into separate worksheets & eventually into separate workbooks.
What I am looking for is a macro code that will automate the cut/paste of information onto other worksheets. I know I can record the macro but the problem is the data sets vary in size each quarter.
What I need is a macro code that looks for a particular phrase and when found it automatically puts all rows undernear (until phrase appears again)on a new worksheet....make sense? The phrase might appear twice or 50 times, so each time it is found the data following will cut/paste.
I'm trying to create a simple Excel spreadsheet (not looking for Access or complex relational database stuff) to have the following:
A tab with data about a person : name, date of birth, join date, and a list of sessions they attend (eg. Monday Morning, Monday afternoon etc).
A different tab in the same sheet, or a form to select from that list of people, and allow changes to the information, and the ability to save it back to the data tab. Ability to add new people or delete people would be useful.
I am looking to create this as a basis for managing sessions, creating a register etc....
Any simple spreadsheet which I can use as a starting point, or to use as a reference in getting it established?
I am trying to use FIND and an array formula to find the position of text in a range of cells (A2 and A3 in the example) which could be one of a number of options (C1:D1 here). But the array formula throws up the following error: "A value used in the formula is of the wrong data type". The simplest illustration of the problem is as follows. The formula in B2 is
Code:
{=FIND(($C$1:$D$1),A2)}
and $C$1:$D$1 contain REF and ATM respectively. [/CODE]
REF
ATM
203047 05AUG 08.55 OKEHAMPTON ATM
#VALUE!
CO-OP GROUP 380611 REF 191 7553375222 BCC
22
We see that B2 has a #VALUE! error - wrong data type. But for some reason B3 is ok returning 22!
SIZE-help.xlsx
I need to show the sizes to shows from column A to show in column b.
I would like to change a number to a letter and then drop a digit from the end.
Say my data in A1 reads 81234568, and I would like it to display in cell A2 as h123456.
This is going to sound very simply to most here I'm sure. I have a need for a simple ledger, credit, debit, balance. I will only have maybe 12 accounts and want to keep track of hand written invoices and payments.
Here's what I would like: I return to my office, click on a tab for an account, enter the date and amount of an invoice in a debit column for that account. When payment is made, enter the amount received in the credit column. Having a running total in the balance column. A monthly statement option would be nice, but not necessary.
I have searched for templates of general ledgers and get results with budgets, projects . . . just too complex for my needs now.
Is there any reason why this wouldn't work in Excel 2007 despite working fine in 2003?
.Range("C2:C15132") = Right(.Range("C2:C15132"), 7)
If Channel A = Channel B, only fill "Primary Channel" (A or B)
If the Revenue A is bigger than Revenue B then take the Channel A as primary and secondary channel column as Channel B
if the Revenue B is bigger than Revenue A then take the Channel B as primary and Channel A as secondary
Sample File
[url]
I just like to do a simple loop but canīt seam to work it out.
I like to loop through column F and on the last cell or the first empty cell I like to get the sum.
how that works? I only find samples which donīt seam to help me much.
Also I would like to know a code to select all cells and rows and copy it into a different worksheet. Like cut and paste with vba.
I am trying to do a nested IF statement in which if Cell A has a number, display it. If Cell A = "", look in Cell B. If Cell B has a number, display it but if its "", then display Cell C.
I have the IF statement working for the first two pieces but don't know how to incorporate a third statement: =IF(A="",B,A)
This works - how do I get it to check Cell B and show Cell C if B is empty?
way to count cells in a spreadsheet (not values in the cells but just the cells themselves). So I have a whole bunch of cells, some are red, some are blue, some are green based on the info i have to keep track of.
so can i write some COUNTIF for red cells green cells, etc
I have a VBA userform with various textbox fields like 'Name' and 'Address' and 'Amount'
I would like an input box to pop up that asks the user to 're-enter for verification'
So that they have to type the same thing twice, to protect against typos
How do I code it so the program compares the inputbox to the textbox and passes only if they are identical?
(or even, using two inputboxes instead if that would be easier)
What I would like to do is to insert the text "Agent" into the empty cell A# immediately above the block of names if "Agent" isn't already there. I'm having trouble with the syntax... The cell A# won't always be the same as the position of the block of names varies. In this case it's A9.
View 2 Replies View RelatedI'm trying to write a basic macro which will increase the value in a cell by 1 when I click on the button I've made. I've searched for hours on the Internet, including this site, and found several websites which give a formula needed but none of them work, I always get an "Object variable not set" error or something about sub-functions which I can't recreate so I can't give the full error message. Sorry.
Based on what I've seen on the Internet, I currently have:
Using Excel 2003. I have a very basic SUM formula I am using to show how many forms are still in-house. Pretty simple.
Column D is how many came in.
Column F is how many were completed.
Column E is what is left.
To get the daily count I take the previous day's Column E, add to today's Column D and subtract today's Column F.
=(E2+D3-F3)
Puts the results in E3.
This formula is copied on down the worksheet which results in:
=(E3+D4-F4), with the results in E4. And so on down Column E.
The problem is that if there is no data entered in Column D or F, the results from E are carried (copied?) as far down as the forumla. i.e., if today's column E results in 4, then that is displayed for as far down as I have copied the formula. What I'd like to do is set the formula so that if there is no data in columns E or F, then there is nothing displayed in Column E.
Here is my simple Private Sub that I can't get to work,
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
For Each c In Target
[Code]....
I have a checkbox control that changes the value to "True" or "False" in column 4. Here lies the issue, if I use the check box control to change the value, the private sub does not work. If i type the value of "True" or "False" where the check box control stores its value, the private sub works. I would like to see if there is way to get the private sub to work by simply clicking on the check box control only.
Trying to work out array formula.
basically if B2 = list of figures in range(F1:f22) then "G14" otherwise "".
{=if(b2=(F1:F22),"G14","")}
doesn't work.
How to do simple table lookup. I have a table showing #of colors being used for screen printing down one side and qty of shirts ordered going across the top. I would like a formula that when I enter the # of colors and the quantity of shirts it will return the correct price.
12-35
36-71
72-143
1
1.50
1.25
.90
2
2.00
1.50
1.10
3
2.75
1.75
1.30
Cell A1:A10 has a simple validation dropdown list of "Yes", "No", and "NA". Cell B1 has "if" formula if Yes selected in A1 then "1" is the result in cell B1, if no selected then 0 in B1 if NA selected then NA in B1. What is the formula to sum B1:B10, calculating the 1's and ignoring the "NA's"? I tried subtotal, sumif, etc.
View 2 Replies View Relatedi need a way for a simple input box to come up and request a single generic password prior to runing a macro
View 9 Replies View RelatedIs there a way that I can make an excel sheet and save it in a way that when it's opened the person JUST sees the cells in a really simple window...
Not sure if I'm confusing people in how I explain this... but basically I don't want it to show the scroll bars, the A B C D etc and 1 2 3 4 etc, the tool bars... etc. JUST the specified cells in a sheet.
i need to do is send an email via excel. In previous practice I have used the Activeworkbook.send method, but in this instance I dont want to send an attachment, all I need to do is send an email to someone, making use of the to: cc:subject and main body text fields.
View 9 Replies View RelatedI usually create fairly simple macros using 'button' feature. However, now I'm using the 'CommandButton', as I had to make buttons highlighted once selected - So I have a choice of five options, whichever the user chooses that option background changes, the others stay grey...anyway, thats the fancy-editing done, but now when i enter a simple instruction - hide rows function - I get the error message "Select method of range class failed"
Here is the full code - like i say it works fine when its just the editing, but when i try to actually hide rows it doesn't work. - I will also add that the code is inside 'Microsoft Excel Objects' and further 'Sheet 1 (INDEX)' - not a module - I'm not sure if this is important.
Private Sub JackStart_click()
JackStart.BackColor = 32896
JackStart.ForeColor = 16777215
JackStart.Font.Bold = True
Majix.BackColor = 8421504
Majix.ForeColor = 0
Majix.Font.Bold = False
Sheets("Bank Reconciliation").Select
Rows("3:3").Select
Selection.EntireRow.Hidden = True
Sheets("INDEX").Select
Range("A1").Select
End Sub
does anyone know how to calculate the interest so it matches this? .......
View 13 Replies View Related