Any Excel Guru's out there - I need a formula

GratuitousApplesauce · Jul 31st, 2006, 07:48 PM

I'm slowly teaching myself how to use Excel, but finding it to be tough sledding mostly. I'm gradually making my spreadsheets more useful and efficient, but I know there must be better ways to do some of the things I'm doing. The formulas seem to be a complete mystery to me and my attempts at building my own haven't really been successful. So far I've found a few that suit my needs either within Excel or by searching the net.

I have a spreadsheet that contains columns with various item descriptions, selling prices and a column that is formatted to show dates, which shows the date sold.

I've got another spreadsheet that is a monthly report. Right now I have to manually copy and paste the rows that contain items sold in a particular month and paste them into the relevant monthly report.

I've figured out how to "link" between spreadsheets so that they automatically update each other when opened. What I would like to do in this case is find a formula that will automatically update the monthly report from the first spreadsheet with the complete row of data that contains something sold within that particular month.

Anyone out there who might know what I'm talking about and could tell me how to create this formula?

Beej · Jul 31st, 2006, 08:11 PM

There are "month" and "year" functions to help with this. They can take a column of dates and convert them to simple integers which can be conditional summed. If that doesn't help, PM me contact information and I'll work on it with you tomorrow.

I consider Excel the only quite good piece of software MS has put out (one or two others are decent and maybe even good) and, although I'm not making macros anymore, I still enjoy using it.

I will not require a blood-pact with the society of The Right, but it wouldn't hurt.

Rob · Jul 31st, 2006, 10:41 PM

From your description it seems the best solution would be to write a macro. I think it would be a fairly simple macro, but if you don't have any programming background it may be too difficult.

The copy paste method can also be very fast if you organize the spreadsheet properly. Excel can sort the data for you so that you can copy the whole month at one time instead of each individual row. In order to do this you select/highlight all the rows and columns that contain your data. Then you go to the Data menu and choose Sort. You want to sort by the column that contains the dates. Once you do that it will rearrange the whole spreadsheet by date so you can just copy the whole section for a given month and paste it into your monthly report.

To go back to your original layout you reselect the rows and columns with data and then sort by the column with the items (I'm presuming they are in alphabetical order).

You can sort by any column as long as the data is in alphabetical, numerical, or date order. Don't forget that you have to select all the data. If you miss one column then it won't get rearranged properly and will screw up your data. Experiment a bit with a junk file to get the hang of it.

GratuitousApplesauce · Jul 31st, 2006, 10:45 PM

	Quote:			Originally Posted by Beej
	There are "month" and "year" functions to help with this. They can take a column of dates and convert them to simple integers which can be conditional summed. If that doesn't help, PM me contact information and I'll work on it with you tomorrow. I consider Excel the only quite good piece of software MS has put out (one or two others are decent and maybe even good) and, although I'm not making macros anymore, I still enjoy using it. I will not require a blood-pact with the society of The Right, but it wouldn't hurt.

Yes, I realized that the dates can be converted to numbers, although I have no idea at all how to use that info.

Aha! Society of the Right - I knew it! The conspiracy exists! Secret agenda - 9-11 - Deep Throat - The House of Saud - Roswell - helping lefties use Excel - it all fits. (I'll PM my info after I go to the store to get a big roll of hat-making foil)

Rob, thanks for the sorting tip. I had played around with that function but I hadn't put it together that I could use it for that.

MacDoc · Jul 31st, 2006, 10:47 PM

While Excel can certainly do what you want I'd suggest Filemaker for your purpose would be a lot more approachable.
$179 or so well spent.

GratuitousApplesauce · Jul 31st, 2006, 11:16 PM

	Quote:			Originally Posted by MacDoc
	While Excel can certainly do what you want I'd suggest Filemaker for your purpose would be a lot more approachable. $179 or so well spent.

Yes, that's on my to-do list. "Get FileMaker and start in on big learning curve"