Excel Magic Trick 1355

Extract Data at each Bold Font, Display Horizontally: GET.CELL Macro Function

DESCRIPTION

See how to extract records from a vertical column that contains Parent Names in Bold and Children Names in “Not Bold” and display parent and children records horizontally. Learn about the GET.CELL Excel 4 Macro Function to create a formula that can detect Bold Font:
(00:17) Introduction to Topic and showcase OZ and Kevin’s Video
(01:48) GET.CELL Excel 4 Macro Function to determine when a cell has Bold Font.
(04:40) Create Defined Name that can “read” Excel 4 Macro Function.
(04:50) See how to create a Universal Relative Cell Reference in a Defined Name
(06:41) Helper Column to identify Records for each parent that uses SUM function
(09:10) Count number of Parents using the MAX function
(09:39) Extract Parent Names and list vertically using the INDEX, MATCH, IF and ROWS function.
(13:55) Count Number of Children for each Parent with the COUNTIFS, ROWS and IF functions.
(16:06) Extract Children Names and list Horizontally using the INDEX, MATCH, IF, OR and ROWS functions
(22:11) Add Custom Number Formatting to show Numbers as Children Numbers, like 1 displayed as “Child 1”
(23:04) Add new records and test formula solution
(23:26) Learn that the GET.CELL Excel 4 Macro Function requires a complete recalculation using Ctrl + Alt + F9 to get the function to update.
(24:08) Summary
(24:35) Information about GET.CELL Numbering and how to extract different types of information from cells

WORKBOOKS

Download “Excel-Magic-Trick-1341-1360.zip” Excel-Magic-Trick-1341-1360.zip – Downloaded 6980 times – 23 MB







Be the first to comment

Leave a Reply