Excel Help Manage Stock and sales HELP APPRICATED

Could you include a sample workbook? It will help us see the problem better. Thanks.

I see a duplicate tag number on sheet 1. This could be a problem. Please explain what the duplicate tag number is supposed to indicate. The rest of it is not an issue.

Also what is the significance of the green section on sheet 2.

I'll get to this later today. The solution will involve the use of Excel tables since they know how many rows they contain and this will keep you from having to rewrite the formulas every time you add or delete a row from the data.

Here is a link that explains what Excel Tables can do for you: http://www.utteraccess.com/wiki/inde...ables_in_Excel.

I hope you don’t mind, but I took some liberties in redesigning your spreadsheet. I hope you find the changes acceptable. I will explain what I did, why I did it and how I did it.

First of all, I converted the data into Excel tables. The main reason is that tables know how many rows they have so you don’t have to adjust formulas. Also formulas that use tables allow you to use the column header names. This makes it easier to understand the formulas. In other words, =SUM(Table_Sales[Cost All In]) makes a lot more sense than =SUM(‘Sales’!A11:A22).

I then added some data validation. Data validation helps you enter the correct value. Here is the reference for data validation: http://www.utteraccess.com/wiki/inde...ata_Validation.

This list can be fixed values such as Male,Female or a column in a table (another reason to use tables) or a named range.

I don’t know how many different breeds you will be dealing with, but I gave you a table in Column I on the Stock Sheet. In Column C on the Stock Sheet, I have a list-style data validation =INDIRECT("Table_Breeds[Breeds]"). This is how you tell the list to look at a table column as its source of data. If you were to add another breed to the list, it will show up on the dropdown list for Breed automatically.

I also used data validation on Date Bought to limit it to a valid date and to Today or earlier. Likewise, Cost All In is limited to a valid number greater than or equal to zero.

On the sales page, I moved some things around and added some things.

In columns M & N I added a table where you can define herds and the selling price of the herd. These values are used for data validation elsewhere on the sheet and to compute profits and averages.

Rows 1:3 are overall totals for everything in the sales table below.

In Cell B5 you can select a herd (as defined in the Herd Table) and it will compute the Cost All In, sold for, Number, Profit and Average for the selected herd. These formulas use SUMIFS and COUNTIFS.

The table below is where you fill in the sale information. Input the tag number (this is data validated against the tag number on the Stock page, so you can’t enter a bad tag number). The information in Columns B to F are looked up automatically.

You get to fill in the information in columns G:J and Column K. Column K is restricted to the herd numbers in the Herd table.

Finally, I threw in a pivot table just for the fun of it on the Summary tab. This pivot table summarizes the data in the sales table. Pivot tables are very powerful tools and they allow you to summarize data in all kinds of ways. I advise looking on line for tutorials on how to use them. The example is just one of many ways to display the data.

You can do the space thing in the sales table. What you would do is enter a single space in the next available row in column D (this is the only column without data validation) and press ENTER. This will open up an entire new row in the table.

Add the Herd # and the Selling Price in the Herd Table.

Then go back and start entering the information for the next sale under the blank row. This would add a (blank) entry to the pivot table on the summary sheet. To eliminate that I added another column to the table called Is Blank. You need to keep this column, but you can hide it if you wish. I put a filter on the pivot table to not show information for any row that doesn't have a Herd Number for the sale.

As for showing the By Herd for Cost all in, Sold for, etc. to the right. Yes, you would have to essentially copy the block every time. I set this up in the Sales (2) sheet. Copy and paste the two rows to the first line of each Herd Sales. Formulas will copy over automatically.

I also threw in a second pivot table and a chart on the Summary Sheet. Both pivot tables run off the same data. It just shows you some of the things a pivot table can do for you. You might want to consider what they can do for you when you have a large amount of data to manage.

As far as splitting the sale of a herd, it does not matter where the Herd Number is in the table. The formula will find it and pick it up.

I added a sold to column to the Herd Table and added the same thing to the mini-summary in rows 5:8. I still have the look up by herd number, because I am assuming that herd number is unique. The problem with a lookup on the person's name is that you may have sold several different herds to the same individual.

I also did something else. I expanded the Herd Table. You enter in the herd number, sold to and selling price. Forget the rest of the columns for the moment. They have formulas and they will be filled in as you fill in the sales table.

On the summary page, there is a pivot table built on this table. It summarizes sales by name with a break out of the herds sold to those names. This is a pivot table which means that I can move things around. If you don't want to see all the people at once, I can move the person's name to above the table and it will show only sales for the selected person. Pivot tables can do a lot for you and they can get complicated. However, the basics are just "drag and drop." I suggest you do a web search for a tutorial and check out the basics.

One last thing. I was mistaken on how to add a blank row between sales. Most of the rows either have data validation or a formula in them. If you add a space, it will either reject it, or you will clobber the formula for the lookup. The best and safest way to add a blank line is to click in the last row and column in the table (Is Blank) and push the TAB key. That will move the cursor to the first column of the next row.

In answer to your questions. If you want to use batch numbers instead of herd numbers, all you need to do is change the headers from Herd No to Batch Number in the places where it occurs. Cell B5 will still have the dropdown if you choose to use it, but you can also type in the value manually.

I assume you want to drop the Tag No on the Stock sheet and use Tag Id instead. When you scan the tag into Column A, the S-ex, Breed and Date Bought will be parsed out and added automatically.

Once you scan a batch in, it would be a good time to copy and paste the tag ids into column A on the sales table. The information on these head will transfer over to the sales automatically.

I made one last improvement to the sheet. I protected the sheet and locked down cells that should be calculated rather than have data entry. This will prevent you from accidentally clobbering the formulas. There is no password, and I elected to prevent you from even selecting locked cells (those with calculations) so if you use the TAB key, you should move to the next cell in which you can make an entry.

As to what happens when you scan a barcode, I can't tell. It's been decades since I've used a bar code scanner. I think what will happen is the value will get entered into the cell, and nothing else. If that is the case, then press the TAB key and you with jump to the Bought From cell. Press the TAB key again and you will go to the Cost All In. Press it one more time, and the cursor will wrap back to the next row down in Column A, ready for the next scan.

A similar thing will happen in the sales table except your will "skip out of" the table for a couple of columns because there are unprotected cells for the Herd Table.

You may have to unprotect the sheets to do things like resize columns.

I can think of a potential improvement: On the stock sheet, I can have two check boxes on each row (left of the tag id possibly) called sold and to sell.

The sold check boxes will fill in when you add a batch number associated with the tag id on the sales page.

The to sell box would be for you to check manually. Click on those head you wish to sell and then click on a button on the page and those head with be "transferred" to the sales table.

In fact, since most of the information is duplicated, there should be no need to keep it in two places. So the Stock Sheet contains only unsold head, and the Sales Sheet contains only those head already sold or pending sale.

What this will look like is that I will give you a way to indicate which head you wish to sell on the Stock sheet, you push a button, and those head will be removed from the Stock Sheet and placed as a new batch on the bottom of the Sales Table.

This would take a bit of coding and you would have to enable macros for the workbook.

OK. I think I got this one.

Hello thanks for your reply
I have attached the file again. There is a few minor things i can see.
Firstly the stock table is perfect.
On the sales table if you see at the totals table (cell C3) this number is 23. There is only 20 cattle sold. This has counted the blank columns also to get the 23.
Also i noticed you had 2 columns hidden. One for the false and one that sold batch. This batch was the actual one that was corresponding to the formulas. So i changed one to herd number.
Are you able to swap the batch and herd colmuns around in the table and able to get the herd number to insert auto when i select the batch number. (this will happen after i insert the info into table on the right. Blue table)
Could you freeze the first 7 rows so i dont have to keep scrolling up to see the profits etc.
Could you also explain the steps to insert the info correctly into the sales table as i may be doing it wrong.
Thank you

I think I made the changes you wanted.

Cell C3 was counting the Tag ID however, the tag id column contains formulas that evaluate to the null string (""). You don't see the null string, but it gets counted. I switched it to read the Tag Number that has true blank cells.

As for filling in the Sales table. To open a new line. put the cursor in the last row, last column of the table and press the TAB key.

Enter in the Tag Number. You can type this in manually or use the dropdown list. Tag ID, ***, Breed, Bought From, Date Bought and Cost All in will be looked up automatically.

Enter in sold to either manually or use the dropdown. This column is validated from the list of names in the Herds Table. If you use the dropdown list, you will notice that there are duplicates. I have a means of eliminating the duplicates if that is an issue for you.

Date sold is a manual entry, but validated to a valid date.

Address and phone number are free-form manual entries.

Batch Number can be typed in manually or use the drop down.

Herd Number is looked up automatically from the Herds table.

I just noticed how the data falls out. If I added a date sold and address and phone number to the Herds table, these would not have to be entered manually. When you enter the batch number, they would be looked up automatically from the Herds Table. See Sales (2).

Actually you should be able to do all of these yourself.

First unprotect the sheet.

To move a column, click in the column header and then move the cursor down to the bottom border. You will get a cross with arrows pointing outwards. This means you are in drag mode. Left click and the whole column will be shaded. Drag the column to where you want it and release the mouse.

Sometimes this might mess up a formula, but in this case it won't.

Freezing and unfreezing rows and columns is under the View Ribbon.

If you right click on the status bar (the very lowest part of the spreadsheet where it says "Ready") you will get a pop up menu. On the menu are selections for count, sum, average, etc. These are the things that Excel will calculate for you when you highlight a group of cells. Normally count and sum are already selected.

So if you want the total of Cost All in for batch 1, highlight cells G10 to G17 and you will see that it is 4,000 Euro and that there were 8 entries on the right side of the status bar.

Hello Thanks again.
Last thing is that i am going to use Sales (2) sheet. But i notice i cant add a batch number to this sheet. I get the error 'The value doesnt match the data validation restrictions defined for this cell'
I notice that i can add batches on the sales sheet and once this is done i can add whatever batches i have added to sales sheet to the sales (2) sheet. I am looking too delete the sales sheet. But it seems the sales sheet is the main sheet and sales (2) works off that sheet.

Could you delete the sales sheet and make the batches work on sales (2) OR even add the date sold, address and phone number to the sales sheet and have them working like sales (2) sheet. (when select batch they auto insert) as the sales table seems to be working fine.

On the sales pages when i write a tag number in manually sometime i get the 'The value doesnt match the data validation restrictions defined for this cell' error but i am able to select it from the dropdown box. Other times when i write in the tag number manually the info enters. Happens alot when i skip a line to seperate batches.

thanks

Try this. .....