Calculating Outline Levels in Excel

quigley6

New Member
Joined
Oct 2, 2006
Messages
22
Hello,

I have set of data in excel that is organized in nested groups (using the "Data, Group and Outline, Group" step).

Does anyone have a clever way to determine the “outline Level” of this set of data? As I understand it, outline level is simply a numeric reference for how many indents there are in each row (1, 2, 3, 4, etc).

Thank you all.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
What is it about the data that is stopping you from seeing the outline levels? Or is it that you have multiple outlines in one sheet?
 

quigley6

New Member
Joined
Oct 2, 2006
Messages
22
Hi Glenn,

I need to a quick way to record the Task Name indentation structure (that already exists in Excel) into a column so that I can cut and paste it into MSProject along with the existing spreadsheet information. MSProject has a column field called Outline Level (which is neat by the way) that will automatically indent the Task Name column rows according to the numerical values in the Outline Level column (for example; 1=one indent; 2=two indentations; 3=three indentations and so on.

I have a 2000+ line Excel file that has the tasks identified and indented the way they should be (which is outputted data from another program). I need to cut and paste the Task Names to a MSProject file and capture the indentations at the same time. When merely copying the Task Names from Excel and pasting them into MSProject, the indentations are lost. In essence, I need to come up with the "outline level" for each row in my excel spreadsheet so that these numerical values can be incorporated into the outline level column in MSProject.

I would appreciate any help you could offer. Thanks, Burt
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Here's a quick and dirty way to do it:
select cell A1 of the sheet,
do menu command Insert/Name/Define, and create a name called DetailLevel, with a formula ( in the Refers To box ) of:
Code:
=GET.CELL(28,Sheet1!A1)
( alter the sheet name Sheet1 to whatever your sheet is called )
Now in any blank column of the sheet type the formula =DetailLevel and copy down.

That will give you 1 for no indent, 2 for level 2, 3 for level 3. That might be 1 out for your purposes. If so, just make the formula you type in the cells of the blank column be:
=DetailLevel-1

Hope that does it.
 

quigley6

New Member
Joined
Oct 2, 2006
Messages
22

ADVERTISEMENT

That was truly brilliant!

Glenn,

That was truly brilliant! It worked like a charm in Excel. Sorry to belabor you with another question. The “Outline Level” column in MSProject will not let me paste the values calculated in Excel. I tried tricking it by creating a “Number1” Column in MSProject, pasting the values calculated in Excel, and then trying to enter a formula (Outline Level= Number1) in the “Outline Level” column (using Tools, Customize, Field). Can’t get anything to work. Still trying.

Thanks, Burt
 

quigley6

New Member
Joined
Oct 2, 2006
Messages
22
That was truly brilliant!

Glenn,

That was truly brilliant! It worked like a charm in Excel. Sorry to belabor you with another question. The “Outline Level” column in MSProject will not let me paste the values calculated in Excel. I tried tricking it by creating a “Number1” Column in MSProject, pasting the values calculated in Excel, and then trying to enter a formula (Outline Level= Number1) in the “Outline Level” column (using Tools, Customize, Field). Can’t get anything to work. Still trying.

Thanks, Burt
 

quigley6

New Member
Joined
Oct 2, 2006
Messages
22

ADVERTISEMENT

That was truly brilliant!

Glenn,

That was truly brilliant! It worked like a charm in Excel. Sorry to belabor you with another question. The “Outline Level” column in MSProject will not let me paste the values calculated in Excel. I tried tricking it by creating a “Number1” Column in MSProject, pasting the values calculated in Excel, and then trying to enter a formula (Outline Level= Number1) in the “Outline Level” column (using Tools, Customize, Field). Can’t get anything to work. Still trying.

Thanks, Burt
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Unfortunately I don't know MSProject at all. Maybe someone out there knows what you need.
 

quigley6

New Member
Joined
Oct 2, 2006
Messages
22
Incorporating Group-Level Summary Formulas in a Sheet

Glenn (et al),

I have a spreadsheet that is a few thousand lines long. These lines are nested and grouped like they would appear in a Project sheet. At each "roll-up" level, I need to execute a particular formula for that particular roll-up level. The spreadsheet is just too long to paste the formulas. Does anyone have a way to do this? I am thinking a macro...but I have no experiance with them. I would very much appreciate any thoughts/help on this.

Thank you, Burt



Here's a quick and dirty way to do it:
select cell A1 of the sheet,
do menu command Insert/Name/Define, and create a name called DetailLevel, with a formula ( in the Refers To box ) of:
Code:
=GET.CELL(28,Sheet1!A1)
( alter the sheet name Sheet1 to whatever your sheet is called )
Now in any blank column of the sheet type the formula =DetailLevel and copy down.

That will give you 1 for no indent, 2 for level 2, 3 for level 3. That might be 1 out for your purposes. If so, just make the formula you type in the cells of the blank column be:
=DetailLevel-1

Hope that does it.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Do you have access to the unnested ungrouped list? Does the logic of the nesting and grouping make it suitable for Excel "Data/Subtotals" processing?
 

Forum statistics

Threads
1,136,350
Messages
5,675,246
Members
419,557
Latest member
razlevav

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
Top