Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p><strong>Option 1 - using formulas</strong></p> <p>LOOKUP is designed to retrieve a single value, which is what it's doing. It loops through a data range, checking whether the value is bigger/smaller than the reference value, and retrieves the first transition point. In your case, it finds the first match and stops, not what you're after at all.</p> <p>If you want to do this using formulas, you'll probably find SUMIF() and the newly added in 2007 SUMIFS() to be a much cleaner route. Also, side-note, in Excel 2007 Tables are your new best friend, they tidy the formulas for this kind of thing right up and look after things like expanding ranges.</p> <p>Your example above, using tables, would be translated to:</p> <pre><code>=SUMIF(Table2[Team],Table1[[#This Row],[Team]],Table2[Hours]) </code></pre> <p>where Table1 is the equivalent of your Sheet1, and Table2 maps to Sheet2</p> <p>Of course the reason that this won't provide what you're after is that this is summarising by team only, whereas you want to apply 2 filters, so you'd move to SUMIFS() and end up with:</p> <pre><code>=SUMIFS(Table2[Hours],Table2[Proj_Number],Table1[[#This Row],[Proj_Number]],Table2[Team],Table1[[#This Row],[Team]]) </code></pre> <p>The benefit is that your formula will always recalculate automatically, the downside is that you have to manually update your Table1.</p> <p><strong>Option 2 - pivot tables</strong></p> <p>Insert --> Pivot table will create your pivot table, set the range as appropriate.</p> <p>Then drag Project Number into Row labels, drag Team name below it also in Row labels, and drag Hours into the Values box.</p> <p>That'll give you a breakdown by project by team of how many hours. You can then mess around with the options to get it exactly how you want, by doing things like turning off the level 1 summaries. Those kind of options are best found by experimentation, they live in the PivotTable Design tab. The catch is the need to manually refresh the pivot table, the benefit is that when you add new projects they'll be taken care of easily.</p>
    singulars
    1. This table or related slice is empty.
    1. POExcel: Multiple Vlookups to pull in 1 data element
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USRichardW1001
    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. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

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