Databases
StackOverflow2013
Postsdbo
POExcel 2007 - How to determine a column is calculated or not
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExcel 2007 - How to determine a column is calculated or not
    primarykey
    data
    text
    <p>I have the following situation:</p> <p>I have a work sheet which contains a list of work with the estimated hours. These hours can be assigned to users (listed in column D and E) using column C. Now I want calculate the assigned hours per user in columns D E and F.</p> <p>I am able to fill in the values for the not calculated fields using the following function: I am using already the following functions (removed the $ sign for readability and I am using record 3 as example because it is not calculated)</p> <ul> <li>Calculation for assigned hours : <code>=IF(C3=D1;B3;0)</code>. So if the name of the user is equal to the name of the assigned user then the value will be used, otherwise 0.</li> <li>Calculation for not assigned hours : <code>=SUM(D3:E3) - B3</code>. So if the assigned user does not exist in the columns then the hours are not assigned. <ul> <li><strong><em>EDIT</em></strong> I have edited the calculation for not assigned hours. The previous version was:<code>=IF(AND(C3&lt;&gt;D1; C3&lt;&gt;E1);B3;0)</code></li> </ul></li> </ul> <p>These two calculations works fine but of course not for the calculated fields.</p> <p><strong>What I want to accomplish</strong></p> <p>Now I want to fill in the calculations on the question marks. This is easy if you know where the calculated fields are placed but that can be everywhere. So I need to have a function which does the following thing:</p> <pre><code>if: The value of columns B is calculated then: calculate all values for the certain user until the next calculated row else: use one of the two calculations I have already described </code></pre> <p>Requested results using the sample sheet:</p> <ul> <li><code>?1?</code>: <code>SUM(D3;D6)</code></li> <li><code>?2?</code>: <code>SUM(E3;E6)</code></li> <li><code>?3?</code>: <code>SUM(F3;F6)</code></li> <li><code>?4?</code>: <code>SUM(D8;D9)</code></li> <li><code>?5?</code>: <code>SUM(E8;E9)</code></li> <li><code>?6?</code>: <code>SUM(F8;F9)</code></li> </ul> <p><strong>Example sheet</strong></p> <pre><code> A B C D E F 1| Tasks | Hours |Assigned To| User1 | User2 |Not Assigned| 2|Main Task |=SUM(A3:A6)| | ?1? | ?2? | ?3? | 3| Sub Task | 10 | User1 | 10 | | | 4| Sub Task | 25 | User2 | | 25 | | 5| Sub Task | 14 | User2 | | 14 | | 6| Sub Task | 17 | User1 | 17 | | | 7|Main Task |=SUM(A8:A9)| | ?4? | ?5? | ?6? | 8| Sub Task | 22 | User2 | | 22 | | 9| Sub Task | 43 | | | | 43 | </code></pre> <p>Explanation:<br> Column <code>A</code>: Each sub task has one extra indent (using the <img src="https://i.stack.imgur.com/8jta3.png" alt="indent"> button) in comparison with its parent task. </p> <p>Column <code>B</code>: The value in this column will be calculated if the item contains subitems</p> <pre><code>Task 1 Calculated because contains subtasks (Task 1.1 and Task 1.2) Task 1.1 Calculated because contains subtasks (Task 1.1.1 and Task 1.1.2) Task 1.1.1 Not calculated because no subtasks Task 1.1.2 Not calculated because no subtasks Task 1.2 Not calculated because no subtasks Task 2 Not calculated because no subtasks Task 3 Calculated because contains subtasks (Task 3.1) Task 3.1 Not calculated because no subtasks </code></pre> <p>Column <code>C</code>: Only not calculated columns can be assigned to a user</p> <p>Column <code>D - E</code>: The header contains the name of the user where tasks can be assigned to</p> <p>Column <code>F</code>: Contains the not assigned hours per task</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. UShwcverwe
    1. UShwcverwe
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POExcel 2007 - How to determine a column is calculated or not
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhy don't you just fill the `SUM()` formulas in ColA over to the other columns?
      singulars
      1. POExcel 2007 - How to determine a column is calculated or not
      1. USTim Williams
    2. COI'm afraid your example doesn't make much sense. For one, your `SUM`s would be circular references. Also, it seems like your table is poorly laid out and even redundant. What is the overall goal of this worksheet? If you're asking what I think you are, then why not insert a table with a Total Row, then select `SUM` from the dropdown box for cols C:E? In fact, you could do that for col A, then just filter col B (user) to see their total hours. Please clarify your objective, though.
      singulars
      1. POExcel 2007 - How to determine a column is calculated or not
      1. USZairja
    3. CO@Zairja Can you explain why the `SUM`s would be circular references?
      singulars
      1. POExcel 2007 - How to determine a column is calculated or not
      1. UShwcverwe
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload