Hi, In excel 2007, I have a formula in a cell like the following: =COUNTIFS('2008-10-31'!$C:$C;">="&'$A7) Now I want to make the name of the sheet ('2008-10-31') be dependent on the value of some cell (say A1). Something like: =COUNTIFS(A1!$C:$C;">="&'$A7) // error Is there is way to do this? Or do I have to write a VBA-Macro for...
Are the formulas represented in an AST then recalculated using a design pattern like the Visitor pattern? How would you go about reproducing the recalculation process in code? ...
I have a SUM array formula that has multiple nested IF statements, making it very inefficient. My formula spans over 500 rows, but here is a simple version of it: {=SUM(IF(IF(A1:A5>A7:A11,A1:A5,A7:A11)-A13:A17>0, IF(A1:A5>A7:A11,A1:A5,A7:A11)-A13:A17,0))} As you can see, the first half of the formula checks where the array is greater t...
I have an Excel table with several items 1, 2, 3..., each of which has subitems 1.1, 1.2, etc. I'm using the list of subitems as my key column and populating the main items using vlookups, but only showing each main item once. /| A | B | C | -+---------+----------+----------+ 1| Item1 | 1.Note | Item1.1 | 2| ...
I have a column array with the following values in my sheet: 11, 15, 5, 7, 2. I want to get a reference to the cell which contains the value 15. How would I go about doing this? Thanks ...
I have a table "A" with 2 columns "Foo" and "Bar". I have a formula with the structured reference A[Foo]. When I fill this formula horizontally I want the reference to stay A[Foo] but now, in the second column, the reference turns to A[Bar]. Is there a way to make this structured reference absolute? It'd be shocking that this isn't sup...
I have a table with 3 columns. I want to write a formula that, given a structured reference, returns the index of the column. This will help me write VLookup formulas using the structured reference. So, for example, for the table MyTable with columns A, B, C I'd like to be able to write: =GetIndex(MyTable[C]) and have it return 3. Righ...
I am very familiar with vlookup and hlookup functions in Excel. However, I am looking for a method of doing both. Take this example: A B C 1 Resources 2 Task Mgr Sr. Mgr 3 ----------------------------- 4 Task 1 30% 70% 5 Task 2 40% 60% ...
I'm doing a hlookup against a value that spans multiple columns. My data is similar to this: A B C D --------------------------- 1| Col1 Col2 2| x y z w 3| 4| In rows 3 and 4 (A3, B3, C3, D3, etc.), I'd like to put formulas that will do an hlookup somewhere else in the workb...
Hi. I'm trying to do conditional formatting so that the cell color will change if the value is different from the value in the cell left of it (each column is a month, in each row are the expenses on certain object. I want to monitor easily changes in prices over months.) I can do it per cell and format-drag it, but I would like a gene...
How do you use the Excel function trend? How do you fill the condition? ...
Hi, I have two columns in Excel, like this: A B 0.5 0.4 0.6 0.59 0.1 0.2 0.3 0.29 I want to count how many of the values in B are less than their corresponding values in A. In this case, the answer is 3. I can do this by adding an extra column, B-A and then doing COUNTIF(RANGE, "<0"), but I am wondering if there's a way to do...
Is it possible to open a worksheet in excel from matlab and edit the formulas? The idea is to automate an uncertainty analysis by creating a second sheet with the uncertainty in each cell for the value from the previous cell. Essentially, I want to treat the cells as variables and do SQRT(SUM(Partials(xi)^2)) for each cell. Matlab sho...
This is a doubt that arose in my workplace, and it should be pretty straightforward. We have two columns of numbers, say: 1 1 1 2 2 1 2 2 And we want to get the number of rows that have equal numbers (2), without a helping column for each comparison. We can't get a working matrix operation (wich is the way we think correct). Any idea...
Sample data A B 1 Date Amount 2 Apr 1 $6,000 3 May 1 $4,250 4 June 1 $2,750 5 July 1 $1,000 6 Aug 1 -$0.075 <- This Cell/Row 7 Sept 1 -$0.2500 In a column of numbers (in reality 100-200 rows), when the value changes to negative, e.g. if these we're amounts owed on a l...
Hello there, i have a report that i would like to send via excel. it will include the recipitents, subject and the information in the body. actually it could copy the cells in question. what i did so far is create a button and assign a macro to it with this code: Private Sub CommandButton1_Click() Application.Dialogs(xlDialogSendMail)....
I have the following worksheet #1 (simplified for the question) of person and age: Person Age Bob 40 Brett 35 Brian 38 I would like to setup another worksheet where I have the following: Person Age Spot #1 FORMULA The user will enter a name in Spot #1 and I want a formula/code in the cell titled FORMULA where t...
Hi, say if I see this Vlookup formula in my Excel spreadsheet: =VLOOKUP(G37,'tb 2008'!$H$142:$M$247,6,FALSE) Then is there an efficient way to find out which cell it is referencing? I don't want to manually perform the Vlookup in my head to find out which cell it found that fits the parameters. ...
I'm working with ASP.NET Webforms and I need to create an report as a Excel file. That should be easy, by changing the ContentType, but I need to dynamically add formulas to some cells. Any Ideas? PS: The format of the excel file should be XLS not XLSX. ...
Hello everyone, its a bit confusing to write my idea but ill give it a try. Here is what i was able to do. I have a drop down on my vertical cells, so i have a list of names to pick from which is linked by [name]-> [define] and [data] -> [validation]. what i would like is another vertical cell to populate another drop down depending on t...