1. N

    Sum Hierarchy Data Using Flattened Hierarchy

    Excel 365 for Enterprise -- I have access to LET, and Xlookup etc. Hi, I am trying to create a rollup of data using an unsorted collapsed hierarchy, by which I mean that the parent child relationships do not follow each other in the spreadsheet so I cannot just take the sum of rows above or...
  2. T

    UDF to reformat hierarchical numbering for sorting

    The fact that Excel is unable to identify and correctly sort hierarchical numbering (ex. 1.1, 1.2, 1.2.1, 1.2.2, 1.3...) is an age-old problem which has been widely discussed in many fora. There are numerous workarounds, but they are often project-specific or formula-based. At the moment, I have...
  3. M

    Seat Allocation formula

    Hi, need help on how to use Excel to meet below requirement. Below sample is just small population of total seatings so would need to automate. Appreciate your help please, thank you!!! =) Requirements: 1. Allocate the Box capacity following the Position hierarchy, such that "Head" starts to...
  4. A

    Excel Hierarchy

    I have some data found here: https://1drv.ms/x/s!AktAOe8JUNECiKkS30iC2jH4jX5GdQ?e=eYGYBa Column A is the PARENT with 00000000 the TOP value. Looking at the data in column B, we see 00000000 has many 2nd level values. Looking at the first 2nd level, 00000004, we then look that up in COLUMN A...
  5. M

    treating dates as dates in pivot table

    https://snag.gy/QFOWNd.jpg Here I have a date column which is a date format in power query. There are no nulls or non date values. How do i filter this with the traditional date hierarchy or the option to select all dates after a date. It's random when excel deigns the data unworthy of...
  6. A

    Multiple cascading drop-downs, vba, form tool?

    Hi, I'm seeking to effect the most robust way a user can to drill through 57k rows of organisational hierarchy in a cascade style. <ul> <li>My ask is to create a tool for users to log business services. So it needs to be quick and robust and intuitive</li> <li>There are 8 levels of hierarchy...
  7. G

    Count text in cell and find next cell with one more

    Hey team! I'm working with some hierarchical data output from our ERP system's vanilla reporting software and my finance team wants a low maintenance excel spreadsheet that identifies all of the levels of a hierarchy as attributes / dimensions. Our hierarchies are built as parent/child combos...
  8. M

    Optimalizing an existing sheet

    Hi there, A question about optimizing sheets from colleagues and exterior firms. I've read a bit on best practices and rules of good tables to increase user-friendliness and reduce the reader's cognitive load. As a practice, I'd like to improve and automate the following example. The following...
  9. B

    Quotation Marks in Formula

    Hi, This formula works when it is in the worksheet but when I try to transfer it to VBA I am missing something with the quotation marks etc. Any assistance appreciated. I'm sure it is a quick fix for the right person. Thanks Ryan "=IF(RC22="""","""",IF(HLOOKUP(RC22,INDIRECT("'CATEGORY...
  10. G

    Generating value based on hierarchy

    I've got a tough one, been racking my brains on this for a bit. In short I'm trying to create a "hierarchy" key based on everyone above an individual. For example if I'm four levels down from the CEO, my hierarchy key will be my id, my bosses id, his bosses id, and the CEO. The CEO doesn't have...
  11. B

    Vary a sheet name in a formula

    Hi, I am trying to reference different sheets in a formula which are named: CATEGORY HIERARCHY (1) CATEGORY HIERARCHY (2) CATEGORY HIERARCHY (3) The (X) value I have in cell AG537 but for some reason the following formula is not working. I have entered the correct sheet name and the formula...
  12. B

    Use a named range in cell formula

    Hi, I want to insert a formula into columns AP:EE that is referencing a sheet named for example "Category Hiearchy (1)". The numerical value is dependent on the value in column AG. I am almost there but cannot figure out how the insert <VALUE IN Range AG> into the defined name. This is the...
  13. N

    Client/Owner/Beneficiary Hierarchy VBA - HELP!!!

    So guys, I have a problem...tried using several org chart style fixes...but essentially I have multiple links to the same manager (using the org chart terminology)... So essentially I have the following data Owner, Account, Beneficiaries Mr Smith has account 1 (his own account) Mr Smith is a...
  14. D

    excel smartart labeled hierachy chart keep shapes inside leves

    Hello everyone, As I add levels using the text pane to my labeled hierarchy chart the shapes do not move or resize to remain in the labeled level. How do I keep them in the correct levels?
  15. H

    Sort financial statements pivot with custom list

    Hi, . I'm trying to sort a pivot with financial statement line items with a few parent categories. The idea is once it calls up trial balance amounts/time series it should look like an actual income statement/balanace sheet etcI have a hierarchy defined (below link). Could anyone give me a tip...
  16. D

    VBA: ActiveChart.Parent.Name ...Why?

    Why is the name of an active chart in the Parent of the chart? what is the parent of a chart if it's not the worksheet?? Thanks (this question is not a problem-solver, I just want to know the specific hierarchy...)
  17. J

    Flat hierarchy -> Treemap/Sunburst Chart

    Hi, I have hierarchical data in a named range "Table1" that looks like below, where the "Count" is the value for each Category, and The "Cumulative Count" is the sum of the current level and all child levels. Is there a way to quickly get this into a format to use the Treemap or Sunburst plots...
  18. N

    Grouping of Hierarchy

    Hi all, Can someone help to have the following table auto-grouped? VBA would be easiest I think. The goal is to have the hierarchy in a good overview people can get to the desired level and fill something in on the columns I will paste next to the table. And therefore Pivot will not work...
  19. L

    Create parent child hierarchy from flat file

    I am looking to write some code that will take a flat file and create a child/parent hierarchy from it. The flat file is sorted in order so that each new row is either a new level in the heirarchy or a new child member. This is a sample of the sorted flat file from which the hierarch should be...
  20. A

    Insert calculated row in Pivot table

    Hi team, here is the issue that I have: I read data from multidimensional cube and there is this hierarchy:

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back