Excel Magic Trick 1355 Extract Data at each Bold Font, Display Horizontally: GET.CELL Macro Function

Download Files:

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:
1. (00:17) Introduction to Topic and showcase OZ and Kevin’s Video
2. (01:48) GET.CELL Excel 4 Macro Function to determine when a cell has Bold Font.
3. (04:40) Create Defined Name that can “read” Excel 4 Macro Function.
4. (04:50) See how to create a Universal Relative Cell Reference in a Defined Name
5. (06:41) Helper Column to identify Records for each parent that uses SUM function
6. (09:10) Count number of Parents using the MAX function
7. (09:39) Extract Parent Names and list vertically using the INDEX, MATCH, IF and ROWS function.
8. (13:55) Count Number of Children for each Parent with the COUNTIFS, ROWS and IF functions.
9. (16:06) Extract Children Names and list Horizontally using the INDEX, MATCH, IF, OR and ROWS functions
10. (22:11) Add Custom Number Formatting to show Numbers as Children Numbers, like 1 displayed as “Child 1”
11. (23:04) Add new records and test formula solution
12. (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.
13. (24:08) Summary
14. (24:35) Information about GET.CELL Numbering and how to extract different types of information from cells
15. (25:03) Thanks to our Online Excel Teammates: OZ, Kevin and brianxyz at YouTube.

[Victor-ollo]

Wow !! I never thought that it is even possible with regular excel formulas -> ExcelGrandMasterMike !!!

[dadgau]

As for previous video, you did a great job. Clear, effective and comprehensive video. Thank you

[johnborg]

Never seem to get enough of these amazing staff :)

[Radioguy]

Explanations are always so clearly stated. Thanks

[SyedMuzammilMahasanShahi]

Thanks Mike for this amazingly EXCELlent video

[maneshzaveri]

Crazily awesome video.... will have to try few times to master this...

[augustothomas]

Awesome example! Congratulations!
Brazil is watching you! \o/

[johnborg]

Never seem to get fed up of this video :)

[robertlevak]

Really enjoy your videos. Keep it up.

[mohamedchakroun]

What is this Mike, it looks like a "sharabia" at the beginning as the french man say :-) it is crazy and funny

[drsteele]

Really clever formulas Mike! I learn something with each of your videos. At 16:50, you discuss the need to create a locked Structured Reference formula to drag-copy the cells to the right to prevent the target field from adjusting right-ward. I discovered that if you copy the formula in F25, then select G25 to K25 and then click paste, the Structured Reference won't adjust in that annoying fashion. Quirky, but true.

[iainrourke]

Good video Mike.

I have very recently started to use macros (a struggle at first but worth it). Whenever I made a user defined function in VBA it would frustrate me that the I would have to manually re calculate the WS if a change was made. However a good trick that I learned is to use "application.volatile" at the start of the function in vba. That will turn the function into a volatile function that behaves similar to any other function in the ws and WILL re calculate automatically! Not sure if this will help you out with your function as I think this is an inbuilt macro that can't be edited (could be wrong). But you could create a new macro that calls the GET function and make it volatile.

I have started using user defined functions in certain situations for example a formula with a couple of variables but many complex operations. Just make a function in VBA to take some of the "ugliness" out of the formula bar in the WS.

[johnborg]

Great Trick Mike. I suppose that you could have used the instead of using the upper cell for the number of children.

[colangelofamily]

INSANE!!! You make me realize I'm an Excel-idiot! this formula was a little more advanced than I needed (but very cool). I just have about 1000 rows of text (sentences)- where each sentence starts with BOLD text. I wanted to grab that BOLD text (basically CTRL X) and paste in another column, while retaining the rest of the text. After reviewing your brilliant example, I still couldn't manipulate your version to my needs. thoughts?

[johnmatta]

It'd be great if you could make a vedio to elaborate the benefits of using table formula structure

[zhoujianzhou]

thanks Mike, still wonder how this defined name works like a defined function?

[susantan]

Wow!! This is amazing. Would like to see more videos like this. What if the parent-child hierarchy is segregated by cell indent? Will GET.CELL works?

[BillSzysz]

Great logic!! Thanks
I am curious if i can make it with structured references only (all in TPeople Table - of course using IsBold or similar in the second column).
You do not need Excel4 macro sheet.... just use defined name with Get.Cell.

[johnmatta]

Hey Mike, why do you bother using table formula; I'm sure you know it can be turned off from Options-Formula-working with formula- uncheck use table names in formulas

[jonathancooper]

Nice! I was waiting for a final trick at the end where you put the get.cell inside a volatile function to make it work automatically.


Would that work?