Excel Help

[mitch37]

Hey guys

Please look at the attached file...this is gonna be difficult to explain...but e.g. look at row 35...each of the codes e.g. BD01, PD02 etc are breakdowns. I f we insert hours available underneath as i have done in row 36, and the look at the table starting in row 92, what formula could I use in e.g. cell C93 to sum the hour values for all "BD01" breakdowns in the entire table?

Please help

 

[SkipVought]

Mitch,

Many of us cannot access files from the network, due to company security restrictions.

If you want to increase your chances of getting a sucessful solution, you might consider posting a copy 'n' paste example here, spending some time to Preview Post and line up the data properly, before you Submit Post.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mitch37]

i cant post now coz i am at work but thanks for the advice will log on this evening

 

[xlbo]

If all your hours are in row 36 and all your breakdowns are in row 35 then:

=SUMIF($B$35:$BR$35,RIGHT(A93,4),$B$36:$BR$36)

should do the job

Spreadsheet is really not set up efficiently for either data entry or aggregation though

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mitch37]

thank you..will give it a go later. i know its not set up well, but its the boss's creation and cant change it immensely

 

[SkipVought]

Let your boss know that his/her creation, although it may make sense to him/her, is not designed to leverage the data analysis features of Excel. Consequently, something that would take seconds to answer, if this application were structured in accordance with best and accepted practices, might take significantly longer, thereby severely affecting his/her performance.

It is your obligation to your boss, to advise him/her of this expert opinion.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mitch37]

definitely gonna do so once i show him how to do what he is asking for appreciate your guys help

 

[SkipVought]

If all your hours are in row 36 and all your breakdowns are in row 35 then:

Unfortunately, the design of the workbook makes this VERY DIFFICULT, since this caveat is not the case.

THIS sheet, one of many, (another potentially HUGE problem with this workbook design) has BD20 in rows, 29, 39, 57, 68 and on the production JUL sheet, in rows 26, 35, 39, 42, 63, 66 & 69.

WHAT A MESS! And we're just getting started, as I would assume that you will eventually have 12 months. What a design/maintenance nightmare!!! You will have a FULL TIME JOB maintaining this monstrosity! What a waste!



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I see that you only have these 2 rows per sheet. xlbo must have already figured that out. That simplifies things.

Select the ROWS with the column A values, REASON_FOR_UNDERACHIEVING:Hours Available.

On each sheet, Insert > Name > Create - Create name in LEFT column. This will create 2 identical named ranges on each sheet,

This basically makes xlbo's formula a bit more user friendly and it does not matter how many columns your data is in, but Geoff's solution will work...
[tt]
C92: =SUMIF(REASON_FOR_UNDERACHIEVING,RIGHT(A92,4),Hours_Available)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}