Check out some similar questions!

jakester · Feb 17, 2011, 01:16 PM

Ok - I built a personal finance spreadsheet for myself and I am trying to build some functionality into one of the sheets using a Combo Box. This will be a drop-down menu containing the option to choose data for a given month during the year.

The way I have the workbook setup is I have a sheet called Actual, another sheet called Budget, and a 3rd sheet called Comparison. Actual tracks all of my income and expenses by line item for each month. Budget sets my budget by line item for each month. And Comparison compares Actual vs Budget for each line item.

What I want to do is on the Comparison worksheet, select from the Drop down list a given month, and have all of the line items for that selected month (both Actual and Budget) populate into each individual line item:

The cells would Change from Actual!B5 and Budget!B5 to Actual!C5 and Budget!C5 when I changed from January to February, etc. as an example.

One of the problems is that the cells are non-contiguous. So for example, under the Income Section I have 5 rows of data and then then the next Section is Spending and it is separated by two rows.

I tried to attach my excel file but it said it was an invalid file.

Thanks, guys.

ScottGem · Feb 17, 2011, 03:39 PM

Do you have Office Pro? This is a lot easier in Access.

jakester · Feb 17, 2011, 04:28 PM

Originally Posted by ScottGem

Do you have Office Pro? This is a lot easier in Access.

Scott - unfortunately, no.

I've been thinking more about this and I believe my problem could be solved by a UDF maybe? I'm not sure.

Here's what I have been thinking. When I select a month from the drop down menu, the Cell Link will put a value (1-12) into cell J2 on my Comparison spreadsheet. Let's say I choose March; it will return a 3 in cell J2. I built a small table with values from 1-12 and a corresponding Column Number and Letter.

Value Column # Column Letter
1 2 B
2 3 C
3 4 D
4 5 E

And so on.

I could use a vlookup to check the value in J2 against the above table and return either the column # or the column letter in K2.

I'd enter a formula in cell A4 that would look like:

=Actual!&K2&5

K2 is the Column Number (or Letter) and 5 is the cell number. I just don't know which is more elegant of an approach.

Does this make sense?

That's really all I need to do.

JBeaucaire · Feb 17, 2011, 04:39 PM

I think you want the INDIRECT() function. It lets you piece together a reference that way:

=INDIRECT("Actual!" & K2 & "5")

However, INDIRECT() is volatile, which is fine in small doses, which may be all you need here.

But if you want to post a desensitized copy of your wb and point out the cells you're trying make dynamic, I'll see if there's a better approach.