# Rackin' my brain

#### Walking Shorts

##### New Member
I know this is a relatively simple question, and I know I've managed to do something like this before, but it's not quite coming to my mind right now...

First off, how do you join text from two different cells in such a way that only PART of the words show up? Example, lets say I have two cells, b1 and b2. b1 says "December" and b2 says "2006", and I want "dec06" to show up in b3, what's the formula I should use?

And second, going based on what I just asked, is it also possible I could use the result to make a reference to another workbook? Sorry if I'm being a little confusing, I'll elaborate. Let's say I have a workbook named "dec06.xls", and I want to cell reference, say, cell c4 of that workbook. But I don't want to just type "dec06.xls" into my reference, instead I want the formula to refer to either cells b1 and b2, or just b3 from my example above for the name of the file.

Thanks in advance for any help dudes :wink:

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

=LOWER(LEFT(B1,3)) & RIGHT(B2, 2)

For your second question, perhaps something like this.

=INDIRECT("[" & B3 & "]Sheet1!C4")

Awesome, thank you! I knew there had to be a way to do it, thanks Norie

A little tougher

OK, I'm still a little stuck. Perhaps I need a different formula. Here's what I'm trying to do - I'm trying to make a spreadsheet that gives me yearly totals from various monthly spreadsheets for some statistics, based on which year I type in.

For example's sake, let's say I need the sum of the data contained in cell 'D6' on the sheet titled 'Monthly". Each workbook is named in a simple format, the first three letters of the month, and a two digit year (EX. dec04.xls, feb05.xls, mar03.xls) So I want to be able to type a year into a spreadsheet, and have the totals from all the workbooks of that year only appear.

Any thoughts?

I should also mention that the formula needs to support a "Year to Date" format, as I will need to gather some stats often before a year is completed. If anyone has any suggestions please let me know, I'm getting a lot of #VALUEs and #REFs, and other fabulous error messages, so the help is greatly appreciated

Have I been banned or something? None of my posts seem to be showing up, que pasa?

Edit Note: Well, they seem to be showing up now... but in red letters, that can't be good...

Walking Shorts said:
Have I been banned or something? None of my posts seem to be showing up, que pasa?

Not that I know of.

You should be able to use the INDIRECT function to conjure up the sheet name, then just glue a range reference to it.

Wrap that all in a SUM function, or the function of choice.

Well, that nearly gives me the results I need, thanks jon. Maybe I just need to change the formula a bit... Cause I get a #Value error if I don't have all the files for each month of that year (ex. this year, as I don't yet have a file for december) So I need to change it so it can support a 'year to date' format. Here is more or less what formula I am using right now-

=SUM(INDIRECT("[dejan"&(RIGHT(D3,2))&".xls]Monthly!D6"),INDIRECT("[defeb"&(RIGHT(D3,2))&".xls]Monthly!D6"), etc.

I'm thinking somehow the IF statement may be the way to go, but I'm having trouble poppin it in there. Any suggestions/other methods?

Are you aware that, with INDIRECT, all the target workbooks must be open in order for it to work?

No, no I wasn't. That could explain why I've been having quite so much trouble with it. I'm very self taught in excel, mostly through math formulae and simple trial and error, hadn't ever heard of the INDIRECT function before having read Norie's reply.

So is there a funtion I can use that doesn't require the file to already be open?

And if not, I still need to figure out a way to use the INDIRECT function in a year-to-date format, any ideas anyone?

Replies
6
Views
270
Replies
3
Views
735
Replies
1
Views
163
Replies
2
Views
348
Replies
3
Views
221

1,196,079
Messages
6,013,309
Members
441,760
Latest member
Sharina

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back