Posts by Neale

Re: Select row of formula result - VBA

I usually use an If statement with a COUNTIF function to confirm the code exists in the column first and then use a MATCH function on the whole column to find the row number.

Re: Page breaks set in Excel disappear when posted into Word doc

Excel and Word have very different page handling techniques - Word is basically setup to print on a page as standard.

Excel has a huge grid that you have to tell it how to print.

When you paste into Word all bets are off and you have to re-set your page breaks in Word

Re: Using VLookup to Find phrase within 1st column and then getting sum from 2nd colu

Sorry if the two columns are in different sheets there's no real solution

You need the two columns to be in the same sheet (copy + paste) and asterixes around the text
eg
=SUMIF(Sheet1!A6:A503, "*160x600 AIR*",Sheet1!B6:B503)

instead of having the "*160x600 AIR*" in the formula you would typically have *160x600 AIR* in a cell (say D1) and use the cell reference in the formula

=SUMIF(Sheet1!A6:A503,D1,Sheet1!B6:B503)

Re: Using VLookup to Find phrase within 1st column and then getting sum from 2nd colu

You can use wildcard characters in a SUMIF

eg in cell d1 contain *160x600 AIR* then the formula

=SUMIF(A:A,D1,B:B)

will add up all the values in B that have the text 160x600 AIR in column A

if cell D1 has 160x600 AIR then use

=SUMIF(A:A,"*"&D1&"*",B:B)

Re: Order Dates Chronologically in Pivot Tabale

One way is to use the Grouping feature - right click the pivot table and selecting Group then select Years Months Days in that order

Re: Get refief form missing info

Have a look at Data > AutoFilter

It allows you to filter on blanks or non-blanks and then then you can use copy + paste into the other sheet

Re: Macro to add range & criteria in COUNTIFS Formula

Try replacing the Mid section with

Left(Orig_formula, Len(Orig_formula)-1) & ",FD, " & Chr$(34) & "F" & Chr$(34) & ")"

You could also use an If statement to confirm that it is a COUNTIFS function using Left(Orig_formula,9)="=COUNTIFS"

Re: Marking a cell adjacent to range to signify maximum based on criteria

Sorry, I see the problem.

The only solution I can suggest is to insert a column with a formula that only have values in it and no text an use that new column instead.

Re: Automating Conditional Formatting

Just use the same formula and remove the $ sign from the front of the D then copy + paste special > formats across

eg =(WEEKDAY(D$3)=1)

Should work

Re: Marking a cell adjacent to range to signify maximum based on criteria

something like - array entered

=IF(ISTEXT(B2),"",IF(MAX((RIGHT($A$2:$A$10,2)=RIGHT(A2,2))*($B$2:$B$10))=B2,"x",""))

Re: updating charts in worksheets

Its hard to answer based on your description.

It sounds like you need to use a dynamic range to refer to the updated data for your chart.

A dynamic range is a formula based name that expands/contracts as data is added/removed from the range.

Re: How to Split 2 CommandBars?

You could add three of the buttons to the right click Cell command bar and leave the others in Add-ins

There is more control over how they appear in the right click menu Cell menu

Not sure that you have much control over the placement of the icons that go in to Add-ins tab.

Re: How to enable the Protect and share workbook button

When using the Format as Table option you cannot Share the workbook - not sure why - but it a built-in setting that can't be changed.

To unformat you use the Convert to range button in the Design ribbon when the table is selected.

Re: Reducing un-used chart variables, for paramount scaling

To make the chart smaller you need to hide those rows wit zero amounts

hidden cells don't display at all

N/A are not plotted

Re: How to Split 2 CommandBars?

Couldn't you just change the text .Caption to a better description instead of "button 1"

There is a .FaceId as well that you can use to put an icon next to the button

Re: Matching odd variables using VLOOKUP in conjuntion with Match and..

Remove the zero from the end as you don't want an exact match

=VLOOKUP(E7,B7:C19,2)

the list must be sorted by the column used for the lookup

Re: Multiplying Time with Manhours

try

=(B30*24)*58

Re: Match/Lookup with multiple conditions

This is an array formula for cell G2

enter the formula and Press Ctrl + Shift + Enter then copy it down

=INDEX($C$2:$C$100,MATCH(D2&E2,$A$2:$A$100&$B$2:$B$100,0))

it will put curly { } brackets around it.

If you don't want an array you can use

=INDEX(C:C,SUMPRODUCT(($A$2:$A$100=D2)*($B$2:$B$100=E2)*ROW($A$2:$A$100)))

normal formula

Re: Extracting text fro string

Its a bit long because there are two /

String in A1

=RIGHT(SUBSTITUTE(A1,"/","",1),LEN(SUBSTITUTE(A1,"/","",1))-SEARCH("/",SUBSTITUTE(A1,"/","",1)))

Re: Multiplying Time with Manhours

Try using this custom format on the cell
[h]:mm