Excel Wizards Needed

[Bigus Dickus]

I'm definitely not a wiz - need some advice here.

I'm trying to create a finance tracking spreadsheet that is simple enough for my wife to use. I have all the pieces in place, such that all she needs to do is enter the date of a transaction, the amount, and click an option/radio button for the expense category. The sheet tracks each category by the date she entered, and creates a report that compares monthly expenses in each category to our projected monthly budget for that category.

See attached picture to see what the data entering sheet looks like.

Works pretty nicely... for one transaction.

Damned radio buttons all like to be in the same group if they are on the same worksheet. I want each row to act independently. The only way I've found around this so far is to draw a form box around each added group (such as the second row in the pic), but that is really cumbersome and ugly as hell, since I can't figure out how to make the box transparent. Don't ask me how the first row has a transparent box... the two rows even seem to have slightly different format/properties options.

Argh.

Simply put, I need to copy what is happening on the first row (a group of radio buttons, linked to a cell on another worksheet, along with the cells for date and amount entry) to a hell of a lot of rows below it. All doing the same thing, but each row independent of the others.

Any suggestions on how to do this in a non-kludged way?

 

[Bigus Dickus]

Oh, here's the pic.

Wait - doesn't look like I can post attachments. wtf?

 

[Bigus Dickus]

OK, let's try that.

 

[mcsven]

I don't see why you need radio buttons at all. Just use the cells themselves. If you get her to put the 'x' character in to signify an expense category and format the cells to 'Wingdings' then you'll get an 'x' in a box - or you can use the character map to find something you like instead (a tick or something similar).

From there building the report can be done via a combination of IF, VLOOKUP and OFFSET statements.

 

[Bigus Dickus]

Well, I like the operational idea behind radio buttons for this application - only one in a group can be checked at a time. Writing a function or cell conditionals that clears any other cell in a group of identified cell when checking one is something that didn't seem necessary when option buttons were created to do exactly that. Point being that I don't want two cells in a row to be accidentally checked, as that would attribute a transaction to two categories simultaneously and muck up the cost tracking/reporting.

That's why I didn't use the simpler check boxes as well. Perhaps you have a suggestion for how to build the behavior of option button groups into a group of cells in a way that would be easier than my current problem?

Right now the sheet works, and the radio buttons work, they are just (1) ugly due to the group boxes around each row and (2) very time consuming to copy as I have to make sure the new group box works right, usually by ungrouping and regrouping, etc., and then getting alignment correct.

For the first part, does anyone know how to get rid of the group box line? They all overlap and make following a row confusing. For the second, any ideas on perhaps writing a macro that can copy and format a row to a new independent row exactly one cell depth below?

 

[mcsven]

I'm somewhat confused about your workflow. Is this sheet a data-input interface?

If so, I see your options like this:

a) simple interface with no Active X controls but with error checking macro built into the data-export process

b) a data-input form associated with a 'Add New Item' button

Otherwise you run into the issues you described when you try and create new records. If this spreadsheet was for your use then I'd say go with a), though if that was the case you wouldn't need the error checking. Since it appears that you're trying to limit the possible user-idiocy variables I'd go with b).

 

[Frank]

Fair warning: don't make that yourself if you don't like digging into the innards and finding out all that arcane stuff yourself. Because, it will never be finished. There is always something that you really need to improve.

 

[Bigus Dickus]

DiGuru - don't make what myself, the sheet in general or the forms of entry that mcseven outlined?

mcseven - the workbook (is that what the whole spreadsheet is called) is for data input, calculation, and reporting. The specific worksheet that is giving me problems is the data input interface. The calculation and reporting is fairly straightforward... I even managed to implement the time codes to sort entries by month.

I'd be interested in an "add new entry" button type of thing that had fields for transaction date, amount, and type (a pull down list?), but I have less experience with that than with option buttons. Not that it wouldn't be easier... any suggestions on how to approach that?

 

[mcsven]

Getting Excel to do these things isn't that difficult anymore what with the advent of the web. If all else fails, the Microsoft Excel usergroups are awesome. MVPs hang out there all the time and they really know their stuff.

On the subject of a userform, this page is an excellent blow-by-blow account of how to make a form with lots of pre-written code that you can just drop in. That would eliminate almost all of your problems and make the whole thing look rather natty. For the first time it'd probably take you an hour or two but the benefit is that you become the expert...

 

[Bigus Dickus]

Thanks - I'll try that route. Seems at least as user friendly as the option buttons I was using, and probably less cluttered looking in the end.

 

[Bigus Dickus]

Well, it was tough going from that example to everything I wanted to do, but after half a day (and night) of Googling for answers I figured out how to implement everything.

I'll have to say I like the forms... but they aren't exactly intuitive the first go around. Not that difficult, but there are some things that didn't operate like I thought they would.

In the end it makes for a pretty slick spreadsheet, and I doubt there is anything my wife can do to mess things up too bad.

 

[mcsven]

Good effort dude. The fun is learning how it all goes together. More importantly, the template you built can be used for all the other data entry you want to do in any other Excel application. And you get to look like the grand-master when someone asks you about it.