Forum Discussion

Chardie's avatar
Chardie
Copper Contributor
Jan 20, 2020

Excel Problem with indenting

I have a workbook with a 3 sheets to show all projects my teams are working on.  The first sheet is a dashboard showing Project details.  You can select "ALL Project" or individual projects.  The center pain will display project details from another sheet.  One column is the WBS number (1, 1.1, 1.1.1, 2 and on).  The sheet was working as expected  I had the following function in the Task Name that would evaluate the row to Project Name of task, and if it wasn't "All Projects", only show based on the Project selected. The Q column has the Project for each row.

=@IF(AND($J$1<>"All Projects",$J$1<>Q4),0,OFFSET('Data Sheet'!$B$1,'Project Plan'!$I4,'Project Plan'!J$3))

 

To make the dashboard more readable I added indenting.  I began to evaluate the column that WBS and indent based on the number (no decimal - no indent, 1 decimal place -  1 space, 2 decimal places-  2 space and so on.

 

=REPT(" ",(LEN(INDEX(Table1,I4,2))-LEN(SUBSTITUTE(INDEX(Table1,I4,2),".",""))))&OFFSET('Data Sheet'!$C$1,'Project Plan'!$I4,'Project Plan'!K$3)

Now, the Project group doesn't work.  

 

Any suggestions would be greatly appreciated.

 

 

excel
Formulas and Functions

2 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Jan 20, 2020
    It isn't clear to me what the problem is precisely. Which cell(s) exhibit the problem, what do you see currently and what do you expect?
    NB: I see you are using OFFSET intensively. Note that that is a volatile function which recalculates regardless whether anythin in its precedents has changed. It potentially makes your model slow.
    • Chardie's avatar
      Chardie
      Copper Contributor
      Jan 20, 2020

      JKPieterse 

      Thank you for your input.  I would like the Project tab to show ONLY tasks for the Project selected in the drop down on the tab (cell K1)  It should scroll through looking at Column R, which contains the Project selected in K1 and only show appropriate tasks.  In addition, for selected tasks, indent based on WBS number. (no decimal, no indent, 1 decimal - 1 space indent, 2 decimals - 2 space indent and so on)

       

      I am new to excel, and downloaded this template and modified it to better suit my need and correct it where it was not working as expected.  I don't know an alternative to OFFSET, yet.

       

      Any input is appreciated!!!

Resources