2 votes

VB Code for MS Project

Here is the corrected version of the macro above: Sub CompletePercentSub() Dim t As Task Dim i As Integer i = 1 For Each t In ActiveProject.Tasks SelectRow Row:=i, RowRelative:=False If Not ...
2 votes

Excel Conditional Formatting - Using Formula as Part of a Cell Reference

You don't need to employ complex, indirect methods using ROW, INDIRECT and ADDRESS. Simply use the following for row 4: =AND(D4="",$A4<>"") and set the Applies to range to ...
1 vote

How to get cell location of all matches in formula

I1= =IF(B2:B6>2;"A"&INDEX($A$1:$A$6;ROW(INDEX(B2:B6;)));"") J2= =IF(C2:C6>2;"B"&INDEX($A$1:$A$6;ROW(INDEX(C2:C6;)));"") I8= =FILTER(I1#;I1#<&...
  • 143
1 vote

Center Across Selection - Button

Ok, if there is no custom button available, one can use this macro and store it in Personal.XLSB If one records the macro there is other format which will applied also, which might not be desired. Sub ...
  • 143
1 vote
Accepted

Center Across Selection - Button

There is no such entry in the "Customize the Quick Access Toolbar" dialog, so you need to create this entry, like this: Select a single cell that is not set with Center Across Selection ...
  • 417k
1 vote

Filter Data in Excel

a quick and simple way to do this involves adding in a new column C called something like IsMax. In here you can have a formula to find out the max Coefficient for the given Accumulated. Something ...
1 vote
Accepted

Reference a cell by content (Date)

Try a combination of INDEX and MATCH functions. Adapt the following formula to reflect your sheetnames (Sheet2 being my 1st sheet, Sheet3 being my 2nd) and ranges. =INDEX(Sheet3!$B$2:$B$5,MATCH(Sheet2!...
1 vote

shorten excel formula to count over multiple tables on multiple sheets

Assuming your tables are consistently named, i.e.: MainDay1, MainDay2, ..., MainDay16 WaitingDay1, WaitingDay2, ..., WaitingDay16 CancelDay1, CancelDay2, ..., CancelDay16 =SUM(COUNTIF(INDIRECT({"...
1 vote

How to create a list of items in an Excel cell, and be able to data filter this column?

Create a cell with a dropdown of possible values to filter on Create a helper column with the following formula: =IF(ISNUMBER(SEARCH(H$2,B2)),"Match","") Filter on that column: ...
1 vote

How do you copy and paste formulas exactly as is in Excel?

Here is a great resource for the many different options for copying and pasting formulas in Excel. Ablebits blog: How to copy formula in Excel. You identified in comments ways that 2 approaches for ...
1 vote

How to enclose a column with curly brackets and commas?

Use Substititute to replace the space with a comma, and then Concat data together: =CONCAT("{",SUBSTITUTE(A1," ", ", "),"}")
  • 1,570
1 vote
Accepted

Looking for values based on some conditions

I believe HLOOKUP MATCH and Concatenate Operator '&' should give you the required results. See the below screenshot. In this example I am purposely using numbers in attendance sheet just to test ...
  • 4,887
1 vote

Dynamic Table Name in Excel Lambda Function with Structured References without INDIRECT

You can look up arbitrary data from a table without INDIRECT fairly easily. You just need to pass the full table reference with the [#All] qualifier instead of just the table name as a string, and ...
  • 11

Only top scored, non community-wiki answers of a minimum length are eligible