Copying totals of values from one sheet to another.

**MaximusPrimal** · 11-03-2011, 06:57 PM

Okay this is probably a lot more simple than I am going to make it sound so appologies now for it... But here goes.

With the project I am doing, I have a spreadsheet with multiple worksheets. There is a main worksheet which holds all the data in it. Then three other worksheets which gives part of the data in easier to read forms which I can then use and copy into a website.

At the moment I manually add to the main worksheet and then update the others. In all cases it is a case of adding to totals in the other sheets.

Now I am wondering if I can automatically get the other sheets to examine the main one and update the figures themselves. The issue that see is that the data is alphanumeric and the other sheets are purely numeric.

I have attached a partial copy of the spreadsheet to show this rather than explain what it looks like.

What I want to do is get the table display in one worksheet to auto update off the main worksheet by looking and saying "How many rows have "28A" in them, how many rows have "28B" in them and so forth and then putting that number in a relevant box on the table display.

I am trying to work out if this is possible as at the moment I manually do this and even after going through over 500 entries by filtering the results and copying the number of displayed entries manually I am still 4 out somewhere

I hope that all makes sense and if anyone can help please do let me know as I have no idea where to start as I am no Excel wizard sadly...

Thanks
Andy

**foxguy** · 11-03-2011, 09:21 PM

What you're asking for sounds fairly easy.
But I'm using Excel 2003. If you want to save your workbook in .xls format, I'll take a look.

**MaximusPrimal** · 11-04-2011, 01:50 AM

Hi Foxguy.

Thanks for replying, I have attached a copy saved out in Excel 2003 format for you to look at. Sorry never thought to that before..

Andy

**foxguy** · 11-04-2011, 02:28 AM

I assume that you don't want to change the format of the raw data, so I left it alone and created columns O:Q for the formulas to use.

I added formulas to the other 3 sheets for you to see.

Everything that I added I colored purple so you can see how they work.

**MaximusPrimal** · 11-04-2011, 02:44 AM

Originally Posted by foxguy

I assume that you don't want to change the format of the raw data, so I left it alone and created columns O:Q for the formulas to use.

I added formulas to the other 3 sheets for you to see.

Everything that I added I colored purple so you can see how they work.

Thank you for this. I will take a look properly at it when I get home tonight and try and understand the formulas you used and how they work - rather than just accept they do as a lot of people tend to do.

Thank you again for the help. It looks like it WAS simple to do but just beyond my very basic understanding of Excel sadly at this point.

Andy

**MaximusPrimal** · 11-05-2011, 02:37 AM

Originally Posted by foxguy

I assume that you don't want to change the format of the raw data, so I left it alone and created columns O:Q for the formulas to use.

I added formulas to the other 3 sheets for you to see.

Everything that I added I colored purple so you can see how they work.

Okay I have had a look at last, didn't get time last night typically...

I understand how the counting in the table works. Using the forumla " =COUNTIF(BraList!$C:$C,$A6&E$1)" You are basically saying: Look in each cell in colum C of the BraList worksheet for a string that equals A6+E1 (for example which would give "36E") and count how many times it occurs. That is great and makes perfect sense.

Same sort of thing applies to the hook counter and I have adapted this one to count the makes as well. Brilliant and very happy with this.

---

The problem I see is with the formula that checks for certain types of garment. The way it reads at the moment is as with the main counting table. Look in BraList for a string equalling A+B. But this means extra work for me when entering items and having to update over 500 existing entries. What I would like to be able to search for something that says "Look in Column A for xxx and column G for xxx and if they are both there add one to the count" This way I can leave the list as it is and keep the data I enter to a minimum. So I added an entry to cell A18 which simply said "Yes" and then looked in the help and came up with : =COUNTIFS(BraList!$O:$O,$A7,$O:$O,$A18)

But Excel gives an entry of 0 for this which should not be the case. I have attached a copy of this below to show you.

Any ideas why this is not working, I am assuming it is something in the way I wrote the formula which I am not seeing due to lack of knowledge. If you (or anyone else) can help that would be great as this would then have everything working the way I was hoping.

Thanks again for the help so far.
Andy

**foxguy** · 11-05-2011, 03:40 AM

There shouldn't be any extra data entry at all. I deliberately made it so that you wouldn't have to do anything different than you already were. I created columns O:Q on sheets("BrasList") in order for the CountIf()s on the Brands sheet to have a single column to look at. Just copy the formulas down the columns to the bottom of your data.

I don't know where you got "=COUNTIFS(BraList!$O:$O,$A7,$O:$O,$A18)"
There's only 2 parameters for the CountIf(), you have 4.

I believe if you look at the formula in column BraList!O:O, you should see that it already looks for a "Yes" in column K:K and puts the word "Padded" (to match the column heading in Column B of the Brands sheets.

I believe you can get rid of columns O:Q on BrasList by using SumProduct(), but it's hard to understand how it works (I have to review it myself every time I use it), so I don't ever use it to help people. The CountIf() formula can only look at 1 range. That's why I created O:Q.

I didn't do all of it. I just did enough to show you how. I left it to you to do the rest.