Need to populate a table with aggregated reference data from other sheet

Hi elnarho,

Please have a look at the attached file. Let me know if you need help setting up the rest of the named ranges, but it looks like you already know how to do that.

Hi Elnarho,

Did my solution fix this problem? If not, you might want to post something to let people know. Usually people won't provide more possible solutions until they know it's necessary. Otherwise, if the solution I provided was what you needed, can you please mark this thread as Solved?

Thank you,

Hi Arjan,

Good, glad to hear it's what you needed.

Yes, what you are seeing in the formulas on Clientfacing-overview!E10:E12 are named ranges from the Project Summary & Estimating sheet. What I did was to name some areas on the Project Summary & Estimating sheet. D39:D48 is a named ranged "DesignRoll", O39:O48 is a named range "DRW1M", P39:P48 is a named range "DRW1Tu", Q39:Q48 is a named range "DRW1W", R39:R48 is a named range "DRW1Th" and S39:S48 is a named range "DRW1F".

How the named range names relate to the area they are named for:

DR=Design Roll Section on the Project Summary & Estimating sheet
W1=Week 1
M,Tu,W,Th,F=The days of the week for Week 1

In order to mimick what I've done, for the rest of the weeks from T39:EN48, you'll need to name each day for the section (DesignRoll) of the week that they refer to. So for the range of T39:T48, you would name it as DRW2M for Design Roll Week 2 Monday, and so on for U39:U48 as DRW2Tu for Design Roll Week 2 Tuesday. Do this for each day of each week all the way across for the Design area, then you will do the same for the other sections, Build and UAT where you would name D51:D60 as "BuildRoll" (minus the quotes), D63:D72 as "UATRoll" and also each of the days for each of the weeks to the right of each but associated to either BuildRoll or UATRoll as BRW1M or UW1M, etc. for all days of each week. There is a lot of work to do to get all of the named ranges setup correctly but in the end it will make your formulas on the Clientfacing-overview sheet much easier to deal with.

To the left of the formula bar there is a dropdown menu. These are the named ranges currently in your workbook. If you use the dropdown and select one of the named ranges, Excel will take you to that range and highlight it so you can see what it references. Also, if you highlight a range of cells that haven't been named yet, such as the ranges I talked about in the previous paragraph, you can click inside of the dropdown and give that range a name. Just enter what the range will be named into the dropdown area and hit enter.

Once you get it setup, it will work for all of the weeks. Let me know if you need any further explanation.

Great to hear that it will work out for you, Elnarho! Yes, named ranged can be extremely useful and help very much to simplify your formulas.

Thanks for the feedback.

Good luck!