Using identifiers to allocate values into seperate sums

**Frantelle** · 05-10-2012, 10:14 AM

Hi guys,

I do not have anywhere near the technical understanding of excel programming
to achieve what I am trying to with a file we use heavily in a particular company sector I work in.

This is a great place to learn but I am in a position now where my workload hasn't allowed me
opportunity to take the time to research and learn myself like I would prefer.

So.... here is what I am trying to achieve and hopefully one of you
can assist me:

Looking at the attached example, you will see three sheets for each day of productivity
and one summary sheet at the end. They show a list of products in the rows
and the columns denote each customer.

The idea is to gather all figures for the days and display lastly in the summary so we can assess P & L
and that sort of thing for the week.

The issue is that with each product from row 20 to 33 the packaging can be either regular,
or a different kind we call 'Y' packaging

It important that we have a separate count in the summary for the different types of packaging on
these particular items and given the way we utilise this sheet we CANNOT accommodate
enough rows to have each listing as a separate item.

SO, to denote whether the item uses 'Y' packaging or not, we put
a 'Y' in the adjacent column (top half of the cell since its a split).

The problem now becomes obvious that in the summary sheet we need
the column "Y Packaging" to collect data only from the item that is input with a
'Y' next to the count.

The summary sheet is currently designed to pull the figures from the very end of
each daily production sheet (col CO) and add them together.

I did not design this system but I was told that I cannot change it without
considerable backlash from the guys who have been using it for years.
I have experimented with for loops and if statements but have achieved
absolutely nothing useful yet.

I have placed some example data for 1 item (highlighted) in the accompanying
excel sheet to assist any person who might be game enough
to tackle this one.

Appreciate any help that can be given
and will check back regularly to
provide any key information missed.

John

DailyEx.xlsx

**MarvinP** · 05-10-2012, 10:22 AM

Hi John,

You are making it tricky by using merged cells. Excel works because data is put into TABLES. A table has no completely blank rows or columns. The design of the way you have you data is just wrong.

Start by understanding what a table is at http://www.easyexceltutorial.com/excel-tables.html
Then modify your sheet to remove merged cells and put your data into a table and then repost your question.
Read the rules of the forum to change your thread title to a more descriptive question/problem.

Hope this helps.

**abousetta** · 05-10-2012, 10:53 AM

Hi John,

I have already created a solution for you but since Marvin has asked you to amend your title, according to forum rules I am not allowed to post it until you comply. I will check back in a few hours and if ammended then I will post the solution. You can also speed things along if PM me once you have modified your title.

Thanks.

abousetta

**Frantelle** · 05-10-2012, 11:54 AM

Thank you MarvinP and abousetta for reading and responding.

To MarvinP, The link you provide regarding Tables and their advantages was very helpful. If it helps, there is no blank rows or columns in this sheet when it is in daily use. There is an abundance of data that goes into these sheets every day including pricing, brands and other information that our workers need. I left out the irrelevant data to keep it simple and on point. This sheet was designed with a printed hard copy in mind since it is distributed to the workers in paper form. I am only trying to find a solution so that I can use this sheet to make my job of collecting figures somewhat less arduous.

abousetta, I am very keen to see what solution you have come up with and thank you for taking the time to help.

Kind Regards

John

**abousetta** · 05-10-2012, 12:37 PM

See if this fulfills your needs:

Please Login or Register  to view this content.

It assumes that all the sheets have the same structure. If not, then I need more information.

abousetta

**Frantelle** · 05-11-2012, 10:13 AM

abousetta

The code you provided did the job. I made some very minor tweaks to it to suit some small sheet adjustments and in testing it handled the data exactly the way I wanted it to so thank you very much. You saved me a lot of time and I'm grateful.

John