Referring To Formulae In The Choose Function
Jun 27, 2007
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.
View 4 Replies
ADVERTISEMENT
Feb 27, 2008
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.)
View 6 Replies
View Related
Nov 5, 2012
So far I have 2 Functions with a sub to test to do certain calculations.
VB:
[B]Private Sub test_getBaristaSalary()
Dim InputYear As Integer
InputYear = InputBox(Prompt:="How many years of Experience?")
[Code].....
View 1 Replies
View Related
Jun 29, 2007
I have this formula:
=IF(C38="C"*F38>=0,"1","")
which is meant to return the number 1 in a cell if C38 has the letter C in it and F38 is equal or greater than 0. I have met both criteria and it returns an error of #VALUE! What am i doing wrong? NB in F38 there is a Vlookup formula that returns a number (in this case it is 0).
View 3 Replies
View Related
Jan 13, 2010
I am trying to use the LinEst function in a program. There are some good examples on the web but they all refer to a range in the format “A1:A45”. Is there a way to refer to a column in an existing array instead?
For example (thanks to Shg for this code): ....
View 7 Replies
View Related
Jul 26, 2006
How does one pick up the data contained in a worksheet cell (say B5) and use it in the vba code of a custom function without passing the cell as an arguement
View 3 Replies
View Related
Jul 16, 2008
Title should read "Data Validation Lists"...oops
I'm trying to format a spreadsheet so that based on the value in Column A, Coulmn B displays one of two spearate dropdown menus.
I know the following formula will not work, but that is the effect I want in Column B.
View 7 Replies
View Related
May 28, 2014
I have this issue where I get an #N/A error when I i run a VLookup in cell H7. I have posted the worbook.
I want to find the value in column D for the value you choose in cell G2
Excelforum.xlsx
View 4 Replies
View Related
Feb 27, 2014
I am creating a box CBM optimizing tool at work for shipping our products internationally so that the sales department and logistics can estimate shipping costs and weights with out needing me to physically pack everything in advance. I have broken every product we sell into individual CBM's as well as the boxes that we use to ship. I have also set it up to give a break down of "boxes needed" to accommodate the approximated CBM by box size.
How to create a function that will take that info and calculate which combination of boxes will utilize the available space... In other words, how do i take my break down of the 6 box sizes available, with the "# of boxes needed" data i already have, and have it tell me which combination of boxes will utilize the maximum percentage of space, or leave the least amount of unused space based off of the cumulative CBM being shipped.
Snippet attached:
Capture.PNG
View 1 Replies
View Related
Dec 16, 2008
I have a workbook with the twelve months of the year as seperat worksheets. a thirteenth worksheet is a "total" page. I have three different names to choose from a drop down list in collumn E. I need a formula that will say something like, "if JanuaryE4 = John Doe, total page A1= January A1" Is this possible?
View 5 Replies
View Related
Jan 30, 2009
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 Replies
View Related
Apr 30, 2009
I 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
View 9 Replies
View Related
May 7, 2008
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 Related
Apr 12, 2006
Is 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
View 10 Replies
View Related
Jan 14, 2008
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 Related
Sep 11, 2007
I 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)))))
View 9 Replies
View Related
Jul 31, 2008
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
View 9 Replies
View Related
Apr 11, 2009
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")
View 9 Replies
View Related
May 2, 2006
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 Related
Sep 20, 2006
Everytime 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")))
View 4 Replies
View Related
May 8, 2008
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..
View 9 Replies
View Related
Jul 21, 2006
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' ...
View 5 Replies
View Related
Jul 27, 2006
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"
View 5 Replies
View Related
Nov 24, 2007
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)
View 2 Replies
View Related
Jun 17, 2009
I am trying to refer to a cell. Not sure if thats a good way to put it.
Example: I am in Cell B1 and I want to have a formula that says if C2 is 5 then A1 is 5, else B1 is 500.
I need A1 to be completely blank with the formula only in B1 and C2 has the number typed in it.
How can I put a number in A1 without having a formula or any text in that cell (A1)?
View 9 Replies
View Related
Jun 12, 2014
I need a better way to refer to an empty cell. I'm using “” but if the cell contain a formula but show empty it affect the conditioning format I'm using this =AND(cell <> "", existing_rule)
View 9 Replies
View Related
Feb 20, 2010
I have the following formula that I want to copy to a worksheet named Tankersley:
=IF('Tankersley Input'!B5=0,"",'Tankersley Input'!B5)
I would like the formula to read the worksheet name rather than entering the word Tankersley directly, so that if I copy it to another worksheet named Tyson, the formula would read:
=IF('Tyson Input'!B5=0,"",'Tyson Input'!B5)
How would this formula need to read?
View 2 Replies
View Related
Jan 27, 2009
Sub vocab6()
Dim fname As String
fname = "c:Documents and Settings
obertDesktopvocab.xlsx"
Workbooks.Open Filename:=fname
Workbooks(fname).Worksheets("tangible nouns").Range("A1").Value = 9
End Sub
I just want to open a workbook saved on my desktop (from a different workbook), and insert 9 into the first sheet (which is named tangible nouns).
View 9 Replies
View Related
Oct 5, 2009
I'm trying to use VBA to write from one workbook to another. I'm trying to populate other workbooks from a single 'control' workbook with a template.
I've managed to figure out how to open up Excel workbooks within a directory (this needs to be done multiple times) supplied by the user, then retrieve information from it to be collated, but I can't for the life of me figure out how to write to the file that I've opened.
I've got the code below, and it's very scrappy as I've been trying lots of different things but nothing's worked. I'll try and highlight as best as I can where I'm having difficulties, as I get the "object not defined" or some such error.
View 12 Replies
View Related
Nov 13, 2009
Lets say we have several numbers and combination of letters/numbers. What can we use to commonly refer to them correctly,
34566
R3456
88900
D2344
I tried to use
Like “*####”
But that’s not correct
View 3 Replies
View Related
