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'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.)
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).
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?
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
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.
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?
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.
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.
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:
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?
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
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.
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.
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")))
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:
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)
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:
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.
