Automatically gathering data from another cell

[andrewb90]

I have a page in excel that has sales information for multiple company's and they may reoccur several times on the page depending on how many times they purchase from us per month. each sale starts with the company's name and a 4 digit number to identify them and all the sales information is in the column below. I am looking for a way to have a second page that will automatically copy and gather all the sales information based on the company's code regardless of how many times the company shows up. Basically I am trying to collect and sum up the total sales, but i don't know where the sales are going to be recorded. Can any body figure this out?

thanks!
Andrew

 

[DonkeyOte]

each sale starts with the company's name and a 4 digit number to identify them and all the sales information is in the column below

"column below" ?

Perhaps you could give some specifics in terms of range references ?

It may prove to the be the case that a Pivot Table will do what you want (quickly and easily) ... or it may not... either way we need more info.

 

[andrewb90]

Well in the page there are columns open to place to next account information in. it is 30 or so rows down and the first row i fill in a company code and the remaining rows is for gross/net sales, discounts, and types of products purchased. Now what is tricky is that this page is separated into 5 weeks with the space for five companies per week. i never know what company will be on what week or even how many times they repeat. week 1 & 2 sit on the same rows but week two is different columns. week 1 is column b-i and 2 is k-r.
weeks 3 & 4 and below 1 & 2 and 5 is below 3. Each week spans 30 or so rows and when a certain company's number is placed in top box of one of these columns, i need all the info from that column to be collected on a different page.

 

[Smitty]

I'd strongly suggest changing your format to something you can actually use. The format you're describing makes your data nearly useless, especially with blank columns in the mix, which quite effectively segregate your data (and can make you very unhappy if you accidentally sort the wrong way).

If you create a flat file format, where all of the pertinent data for individual customer orders are kept on their own rows I think you'll find your data much more usable. Once it's set up that way you have the perfect setup to analyze your data with Pivot Tables. If you can tie order date to a week # you can even separate orders by week.

Otherwise you might want to look at SUMIF in the helpfile. It will let you sum a range based on criteria in another (e.g. sum all sales by Customer A, where customer name is in column A and Order $ in column B).

HTH,