Excel help!

[Jenova314]

Hi everybody,

I'm trying to compile a personal monthly expenses spreadsheet, with a master sheet drawing from monthly sheets. I'm referencing several cells in the master sheet from January, 2007. For exampel, my monthly set costs from January would be referenced from the '01-2007' sheet to the master sheet via:

=('01-2007'!$K$16)

However, I'd like to reference any month without having to manually edit every single '01-2007' to something else, since I have about 20 of these. Would I be able to replace the '01-2007' with say... a variable, dependent on another cell, which I may input? So that I may use somehting like:

=(R1!$K$16)

Assuming I type the name of the sheet (e.g., 02-2007) into R1. Of course, that doesn't work, because Excel simply looks for a sheet named 'R1', which doesn't exist. I'm sure there's an easy solution to this, but I'm just too ignorant to figure it out. Can anybody help??

 

[mayest]

If I understand you correctly, then the Indirect function is what you want. For example, suppose that you have the text string Sheet2 in cell A1 on Sheet1. Now, to pull in a reference to Sheet2!A1 you would (in Sheet1) use:

=INDIRECT(A1&"!a1")

Now, if you change the text in A1 (Sheet1) to Sheet3, then it will pull in the value from Sheet3!A1. Just put the name of the sheet that you wish to reference in A1.

Note that you have to build the reference as a string, and then the Indirect function will convert it into an actual address.

 

[Jenova314]

Thanks a bunch, that did exactly what I needed it to. I had some trouble getting it to work, since my sheets were named 01-2007, 02-2007, etc. Excel didn't like my dash/subtract, so I had to change it to 01_2007, 02_2007. That got it squared away!

 

[mayest]

I'm glad it worked. The Indirect function is one of the hidden gems that most people don't know about. They should.