Posts by travellerva

    Please see attached sample spreadsheet. I'm trying to find a formula that provides a count of the number of times in two columns that the values in corresponding rows are equal. I've tried various versions of COUNTIF without success. I realise this could be done by creating a "shadow" column that flags each row that's equal; then counting that column but I don't want to add a third column - even hidden. I also don't want to do it as a macro. Many thanks for all ideas.

    Re: Truncate variable in chart title

    Quote from Max1616;772296

    Try this:

    With ActiveChart 
        .HasTitle = True 
        .ChartTitle.Text = "Last " & Format(Age/ 30, "#,###.0") & " months" 
    End With

    Many thanks. That works fine. I hadn't realized I could embed a Format method inside a string.

    I am trying to truncate a variable in a chart title. See:

    With ActiveChart
        .HasTitle = True
        .ChartTitle.Text = "Last " & Age / 30 & " months"
        End With

    The variable "Age/30" needs to be truncated to 1 decimal place but I can't figure out how to do that.


    The attached sheet is an extract from a much larger workbook. The table provides monthly entries for revenue from multiple projects. Project names are in A8:A55 and project type is in B8:B55. Columns D:D through O:O contain revenue entries for each calendar month for each project.

    I need to sum the total for a particular month for all T&M projects and all Fixed Fee projects. The month specified would be in B1 and the results are to be place in B2 and B3. In the real workbook, these results will be on a different sheet but i can deal with that

    I realize it would be simple to add a couple of rows to the table and sum the totals for each month; then to pick up the relevant total from that. However, it is not possible to provide the space for such rows (there is much more dynamic stuff going on in the workbook) and I'm looking for a formula for B2 and B3 that would give the totals according to the value in B1. If it helps, the value in B1 could be the alpha equivalent of the month sequence (Jan, Feb etc.) to match the headings in cols D:D through O:O.

    Im thinking some form of a SUMIF with INDIRECT to get to the appropriate column but can't figure that out. Any help much appreciated.

    Re: Formula for total backlog

    Thanks again. I had no cells with either text or #N/A in them but by experimenting with batches of rows, it finally worked ok. Maybe I had finger trouble somewhere.

    Either way, your solution works well - thanks

    I have a sheet with columns for Project Name (col A:A); Estimated Total Value (col B:B) and YTD Revenue (col C:C).

    I'm looking for an array or other formula to give me a total Backlog for all projects. This is the sum total for any project where the Estimated Total Value (B:B) is greater than the YTD Revenue (C:C). In some cases, the value in col. B will be less than the value in col. C - these should be given a zero value, not a negative value.

    I know I could just create another column of =IF(Bn<Cn,0,Bn-Cn) and then sum the results but I do not want to have another column, even hidden. What would be a SUMIF or SUMPRODUCT way of doing this?


    Re: Find earliest date for client

    Many thanks, Maqbool. However, that array formula returns the latest date, not the earliest.

    But your advice led me to the opposite array formula, SMALL, which gave me what I wanted - thanks!

    I have a 2-column table where the first column is a series of dates (A:A) and the second column is a list of clients (B:B). There are multiple entries for each client; each entry with different dates. The table is sorted in alpha order of client but random order of date entries. A sample table is similar to the attached "Date-client" sheet.

    I have a separate list of clients (E1:E4), one entry for each client, and need to find the formula for the earliest entry from the A:B table for each client and insert it into the corresponding E:E table entry in the next column (F:F). I do not want to use a pivot table and cannot do repetitive sorts.

    It would seem to require a VLOOKUP to find all the instances of each client, then to find the earliest date. I'm struggling with this one. Any help much appreciated.

    I have a macro that needs to FIND an instance of "TOTAL" in a cell range. "TOTAL" occurs with variable text following it in the cell but can also occur as a word inside other text. I only want the cell where TOTAL is in the leftmost position. I don't want to change the content of any of the scanned cells.

    Obviously the following example catches all variants of TOTAL. How do I restrict it to only cells where TOTAL is the first word?

    Many thanks for any ideas

    Re: Selecting a sheet array in VBA for Excel for Mac

    After more research, I believe the problem can be narrowed down to the ExportAsFixedFormat command in Mac Excel. I can show that the code to select the array of sheets works fine in both Windows and Mac. However, the:

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF 'etc...

    statement only picks up the last sheet of the sheet array when running on Mac.

    Further testing on Mac shows that simulating this vba action by doing a manual selection of multiple sheets, followed by a File>Print>Save as PDF on the Mac has a similar issue - only one sheet is saved.

    Can anyone help as to how to work around this difference between Win Excel and Mac Excel?

    I have a macro that creates a single PDF file of several worksheets of a workbook and saves it in a folder selected by the user. The array of worksheets varies according to an initial selection by the user. The arrays of sheet names to be included in the PDF are maintained in tables inside the workbook. Once the sheet names are selected as an array they are saved as a single document using the ExportAsFixedFormat VBA command.

    The macro is run by different users - some using Excel 2010 for Windows and some using Excel 2011 for Mac. The macro works fine on Excel 2010. On Excel for Mac, although the array of sheet names is created correctly only the last sheet in the array is saved in the selected folder. Here is the relevant portion of the code:

    In case it has any effect, the subroutine used on Mac for selecting the saved folder target is:

    Anyone have any idea why the sheet array doesn't get selected (or saved) correctly on Excel for Mac?