I have a database on one sheet and a 2 count if formulae recording information on the next to be exact one formulae counts the number of monthly values and the other count yearly values. I want the sum of these formulaes to be equal or less than 25. and to show an error if the sum of these is mor than 25.
View 9 RepliesI am slowly working my way thru Excel succesfully but have come up with another one I can't find an answer for..... YET
I simply want to subract the value of two cells, however the values of those cells are produced by formulae. Hence I get the #Value! error.
Once again I know there's gotta be a simple solution, I just can't locate it
I need to add a piece of code to a spreadsheet that will turn all formulae within into values. The number of tabs in the spreadsheet is likely to change over time but I want the code to apply to all the tabs - without needing to change it if a tab is added/ deleted.
View 3 Replies View RelatedIs there any way to 'evaluate' a dotted number (similar to an IPv4
address but not just those) using standard worksheet formulae (ie no
UDFs, VBA, or ATP functions)?
To make it a little more complicated, I need to be able to evaluate
not just IP addresses (although that would be a use) but also a
'number' such as 9.0.2 compared to 8.7.5 so that they can be sorted
and / or compared.
The views expressed are my own, and not those of my employer or anyone
else associated with me.
My current valid email address is:
1bupdvc02@sneakemail.com
This is valid as is. It is not munged, or altered at all.
It will be valid for AT LEAST one month from the date of this post.
If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.
The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:
ewygchvboocno43vb674b6nq46tvb
is it possible to have a sheet that i can type in formulae and the formulae will automatically copy itself into the same cells on any new sheets i open?
View 13 Replies View RelatedI found this post in the archive section. Its purpose is to find the Last Used Row in a Range, "A:A".
Using it, I get a #Name? error. I have placed the UDF in a module, placed the formulae in K1.
I have mixed data from A1:A34.
Function Used(r As Range) As Range
Dim q As Range
Set q = r.Parent.UsedRange.Cells(r.Parent.UsedRange.Cells.Count)
Set Used = Intersect(r, r.Parent.Range(r.Parent.Cells(1, 1), q))
End Function
=INDEX(A:A,SUMPRODUCT(MAX((LEN(USED(A:A))>0)*ROW(USED(A:A)))))
Have this workbook with over 34 worksheets. Exactly 31 sheets have over 700 rows & 5 columns of array formula. The cell references for these formulae are on "Sheet2" and the values in Sheet2 get populated after the macro is executed for Sheet1.
The macro when run, takes helluva time to complete and populate the values in these 31 worksheets. If I turn off the calculation, it throws out a an exception and on the top of it I have noticed sometimes that the computer hangs itself and becomes DEAD. The only option I got is to press the Reboot Button.
The same macro when run on less than 100 rows, works like a treat ! Could someone help me resolve this high density problem, the Excel way (I am not in a position or a situation right now - to use ADO Recordsets to rectify this problem and neither do I know how to use it). So if your suggestion asks me to use the ADODB then unfortunately it will not be helpful to me at the moment
I have tried by all means and now I am stressed out.
Sub try()
Worksheets("sheet1").Activate
Range("A5").Select
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("A5").Select
Selection.Copy
Range("A6:A65536").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
If I have a string of the form:
S:GenerealfoldereferenceFolder2FolderTest_ABC_2009_v2.xls
I would like to extract the ABC term, i.e. the term that liues in between the 2nd and 3rd "_" in the Workbook name (in the above example "Test_ABC_2009_v2.xls")
is it possible to have more than one IF function applying to a cell eg. if value in cell A1 is greater than 10 then Positive is written but if value in cell A1 is between 8 and 10 then Suspect is written.
View 2 Replies View RelatedEverytime that I insert a row into a worksheet which a number of formulae refer to, those formulae get updated automatically in such a way that it is not in my favour.
The formulae refer to rows 2:2000 on a number of different columns. Adding a new row shifts the references to 3:2001. I presume that I can stop this from happening
Example:
= SUMPRODUCT(('List of DD Donors'!E2:E2000)*('List of DD Donors'!F2:F2000='DD Tally'!E6)*IF(DATEDIF(('List of DD Donors'!G2:G2000)-1,DATE(2006,3,31),"m")>12,12,DATEDIF(('List of DD Donors'!G2:G2000)-1,DATE(2006,3,31),"m")))
changes to...
=SUMPRODUCT(('List of DD Donors'!E3:E2001)*('List of DD Donors'!F3:F2001='DD Tally'!E6)*IF(DATEDIF(('List of DD Donors'!G3:G2001)-1,DATE(2006,3,31),"m")>12,12,DATEDIF(('List of DD Donors'!G3:G2001)-1,DATE(2006,3,31),"m")))
I have a number of different formulae I want to invoke according to a choice. I am able to request the formula I want by entering them ‘in line’ as value1, value2 etc into the CHOOSE formula … e.g. =CHOOSE($H$1,IF(N3>O3,1,""),IF(N3<O3,1,""),IF(N3=O 3,1,””))
but this is cumbersome and confusing since I have a growing number of choices and some of the formulae are quite long. I want to enter them in a list and extract the formula which corresponds with the Index_num in the CHOOSE formula. I also need the cells in the formulae to be relative so this rules out using NAMES to refer to the formulae.
I would like a macro which works on the selected cells:
A number of the selected cells contain formulae with an apostrophe in the beginning.
I need the macro to remove the apostrophe and calculate the formula in those cells..
I have a macro which inserts rows at certain places in a data table. This works fine. I then want to copy the formulae from the the row aboove or below (dependent on certain criteria). This sort of works.
I have is that when the row is copied, the row numbers within the formulae do not change relative to the new row. Eg if I copied down "=A6" one row, the formula still says "=A6", rather than "=A7".
I have tried variations of
Selection.PasteSpecial Paste:=xlPasteFormulas
I also tried the AutoFill method (but this didn't work at all when I tried it with a whole row)
Previously, I would have selected ranges and then pasted on the active sheet. However, I am trying to re-educate myself in writing macros that do not 'select' ...
I want to write a For loop so that it checks the range G2:R2 of Sheet1 for the first non-zero cell in that range. When it comes to the first non-zero cell, it will change the formula of A2 on Sheet2 to have the SUM of that non-zero cell and the next two cells to the right. Example:
SHEET1-
G2 = ""
H2 = ""
I2 = ""
J2 = "3"
K2 = "4"
L2 = "8"
M2 = "9"
N2 = "2"
O2 = "5"
P2 = "3"
Q2 = "11"
R2 = "7"
SHEET2-
A2 = SUM('Sheet1'!J2:L2) --->"15"
I'm trying to write a function that returns, from a different sheet, the value in column C and row 6*current row.
For example, if the function is typed into cell B5 in Sheet 1,
the resulting value would be whatever is in cell C30 in Sheet 2
I tired the following but it gave me an error
=Sheet2!&"C"&(ROW()*6)
I'm trying to make a schedule of deposits made for the month of March 2008. (Deposits are made on a daily basis.) With this, I wanted to know when the deposit would clear with the bank using the WORKDAY formula given that I have to count 6 banking days after the date of deposit (Saturdays and Sundays excluded).
I'm using Mac and I recently upgraded to Office 2008 (never tried WORKDAY formula in other versions). I am aware of the syntax used for this formula and it works just fine in other cases. But I noticed that with start_dates falling on a Thursday or Friday of the week, Excel would give me a result date that falls on a Sunday, which is odd given that it's supposed to ignore non-working days or weekends. (I haven't even gotten to inputting holidays yet.)
I'm using an IF formula – BV2 =IF((OR(BQ2="yes",BU2="yes")),"Yes","No") - which just shows "#VALUE!" whenever BU2 shows "#VALUE!". Is there any way round this so that, if BQ2 showed yes and BU2 showed value, BV2 would say yes?
View 9 Replies View RelatedI have a sheet with a reference field that I want to sort. Each cell in the reference field has a formula in it, which specifies that the cell remain blank unless there is a value in the cell. The values are either text values or number values. e.g. =IF(ISERROR(S4),"",VLOOKUP(C4,'Rider Data'!A:G,6,FALSE))
When I sort the field in ascending order by the column that contains the formula above (using a macro written for this sort procedure), where the expected value is a text value, all the blank cells are sorted ABOVE the cells with text values in them, rather than below, even though they are blank cells. However,if I sort on a different column which contains time values or numerical values, the sort function works just fine. I have tested the VBA code that produces the erroneous sorting and confirmed that it happens only when sorting on columns that contain text values. I would like to sort text values in ascending order with blank cells BELOW the sorted cells.
We have an internal web site that has files I need to download daily. The filenames have date strings in them. I've setup some formulas to make the url based on the NEXT dated file I need to download.
And I don't have direct access to the drive the files are stored on, I can only get them through this web site.
Right now, I have individual macros for each file I need. They'll follow the url and download the file if it's there, or return a message to me if it's not. But there are several different files. I have to run each macro one at a time, at different intervals during the day until they get downloaded.
Is it possible to make a macro loop through all the URLs (I have them stored on a sheet, called "FileDownloader" in Range G2:G10) and check if the URL's are valid (without actually attempting to download the file). I can then make some kind of dashboard to tell me when the files are ready for download.
I have a spreadshhet which has data by month, year, week and bi-weekly. I would like everything to be monthly. How can I put a formula which will look up the cell and see if it monthly it will the value of the cell beside it, if it is weekly it will take the cell value and multiply by 2 and so on.
View 4 Replies View RelatedI have a range of cells (A1:A50). If "Hello" is written in any of those cells then a MsgBox says "Are you sure?". If vbYes the cell is colour coded blue. If vbNo then it is red.
The problem I have is that "Hello" may already exist within the above range. I only want the above to fire on the cell that has just been changed within the range.
I have some code but it checks every cell within the range whenever any cell is changed within the range. Whereas I just want it to fire on the active cell if that makes sense?
How can I validate that all values in a range of cell are the same, excluding
empty cells?
I have a ComboBox on a UserForm. One of the fields that I fill in is a date.
After the date is entered I want to check if it's a valid date.
VB:
Private Sub cboEnterDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
' Make sure a valid date was entered, it must be in the format 2013/10/21.
On Error Goto ERR
'If Not IsDate(cboEnterDate.Value) Then
[Code] .....
ERR:
MsgBox "The date entered is not a valid date", vbInformation
Cancel = True
cboEnterDate_Enter
End Sub
The code:
VB:
'If Not IsDate(cboEnterDate.Value) Then
execute the If routine if I enter the date 2014/03/33
When I enter the date 2014/03/0001 it do not see it as an invalid date and exit the routine.
The code:
VB:
If Not IsDate(FormatDateTime(cboEnterDate.Value, vbShortDate)) Then
send the execution to the ERR routine if I enter 2014/03/33
When I enter the date 2014/03/0001 it do not see it as an invalid date and exit the routine.
How can I get the validation to catch the 2014/03/0001 as an invalid date as well.
I would like to simply validate the value entered to a cell against cells of a number of columns. I’d like it to return a vlue to be able to report it in another cell as below. (had to cmma separate the columns, can't get them aligned in this editor)
A, B, C, D, Q, R
1, b, 1, 2, 1, existing
or
A, B, C, D, Q, R
1, b, 1, 2, 5, not existing
So the user enters a value in cell Q to check against the other columns and return a value to R. Additionally, the cells to be validated (A - D) may contain more than one character, i.e. cell A could = 1b2.
I have to validate a cell to only accept the letter a, d ,f ,g h,. I know this is probaly very simple but i havent touched excel since last november and have completely forgotton the most simple of things!
View 4 Replies View RelatedI have a button that opens a workbook. What i need is if that workbook is already open then continue with the code. I know how to do this workbook.activate and error messages but I want to avoid activating the workbook. I was hoping there was a way to use something like on message resume next like when using error messages.
View 3 Replies View RelatedWhat is the best option to validate a value entered through the below prompt?
View 4 Replies View RelatedHow to check whether a cell contains email address or not..
For example:
if a cell contains xxxxxx@xxx.com or .in or .net , i want to show the cell type as email in next column.
I need to determine if excel cell contains any character except number If it contains any character then place 1 in adjacent cell for example, otherwise 0
Column                A                    B                          Â
150 000Â Â Â Â Â Â Â Â Â Â 1Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
150000Â Â Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
150,000Â Â Â Â Â Â Â Â Â Â 1Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
150.000Â Â Â Â Â Â Â Â Â Â 1Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
150000 kzt     1                          Â
150000kzt       1
I have a sheet that contains two values in seperate columns that I need to concatinate into one value for the drop down. If I use one value my code works but doesn't show all the values. If I use both values I get a 1004 - Application Defined or object defined error. I bolded the section that if I add I get the 1004 error. Is there some sort of restriction when creating the validate list for a drop down? My list is not reaching 65000 characters which is the string cut off.
Dim intAdEventCol As Integer, intAdEventNmCol As Integer, intRow As Integer
Dim intLastRow As Integer, intLastCol As Integer
Dim strAdEvent As String, strAdEventNm As String
Dim strAllAdEvent As String
Do Until intRow > intLastRow
strAdEvent = Worksheets(gstrcDataWorkSheet).Cells(intRow, intAdEventCol).Value
strAdEventNm = Worksheets(gstrcDataWorkSheet).Cells(intRow, intAdEventNmCol).Value
If intRow = 2 Then..........................