Link budget category with actual budgeted value

[Jelapprand]

Hello,

My wife and I are tweaking our newly made budget sheet. We mark down expenses, and those quantities are totaled and placed into a given category of expense. Next to that category, I added a column for budgeted amounts. Next to that column is another, where I have each cell calculate the difference between what we've budgeted for a given category and what we've actually spent. Values (negative or positive) in that "difference" column carry over to the next month's budgeted column.

I have attached an image of a blank month to give you an idea of what it looks like.

As the months and years progress, these expense categories are likely to change. Instead of having to change and re-order categories month by month, we have all categories in that column remit to the "Year" summary sheet (highlighted in the image), where we edit categories for the whole workbook. What I just realized, however, is that if I edit the categories listed in the summary sheet, the budgeted values and differences will no longer line up with the expense categories in the month sheets.

Is it possible for me to force a row in the "budgeted" and "difference" columns to stay next to a given text from the "category" column, even when the latter moves up or down because a new category was added?

Many thanks!

[vba_php]

Is it possible for me to force a row in the "budgeted" and "difference" columns to stay next to a given text from the "category" column, even when the latter moves up or down because a new category was added?

more than likely the answer is YES, however your description of the issue and all the details was a little confusing. can you simplify it any? someone else may be more receptive to what you had to say, but I didn't follow it too well.

[Jelapprand]

Hi there,

Sure Adam, thank you!

Again, basically we have our "year" summary sheet at the end of the workbook, where we have a list of income and expense categories. Each month's budget category copies that list. (So, for example, our list of expenses might be "rent"; "gas"; "car maintenance" - in each month, for rent, I put "Year!S6"; "Year!S7" and so forth.

The idea was that we might want to add a new category (e.g., we have lots of expenses in the "other/misc" category, and we realize we didn't have a category for "mail and shipping," we can add that to our categories list in the "Year" sheet, and the lists of categories in each individual month would automatically update).

But suppose we come up with a new category that's important, and we decide to put it in the middle or near the top of the list, not just tack it on to the end. This therefore changes the order of categories, which is fine since each month's sheet automatically updates, but this also means the budgeted amount next to each category no longer matches the row.

What we're looking for is a way to force those budgeted amounts to stay next to the expense category in the adjacent column, regardless of where each category ends up when they're re-arranged. In other words, force cells in three columns to stay next to each other, regardless of where one of them goes.

Thanks again,
Julius

[vba_php]

Julius,

with regard to what you're saying here:

What we're looking for is a way to force those budgeted amounts to stay next to the expense category in the adjacent column, regardless of where each category ends up when they're re-arranged. In other words, force cells in three columns to stay next to each other, regardless of where one of them goes.

are you talking about manipulating the data in the attached image, which I've highlighted?

[Jelapprand]

Hello,

That is correct!

For example, if "Mary cell phone" category goes down by two rows (if we add two categories above it), how to force the "50$" and the corresponding row in the "difference" column to stay next to it.

[vba_php]

Well what it sounds like to me is that you're simply inserting rows and if you insert rows into a worksheet a worksheet is nothing more than a grid and so information is squeezed downwards or upwards appropriately period and considering the data in all three columns that you're talking about is in the same row it should move automatically. Is that what you're asking?

[vba_php]

if that's not what you're asking then I might be a little bit confused at this point so you might have to offer another explanation or let me look back at this later on tonight if no one else jumps in because I've got a couple of obligations to tend to right now and for the next three or four hours

[Jelapprand]

Hi Adam,

No, that's fine, thank you! I really appreciate your trying to help. I think I found a makeshift solution for the time being.

It's still a fine program, I can say that for sure! Very grateful.

Best to you,

Julius

thanks again for taking all that time to help.

[vba_php]

ok..........