Rackin' my brain

[Walking Shorts]

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:

 

[Norie]

For your first question.

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

For your second question, perhaps something like this.

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

 

[Walking Shorts]

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

 

[Walking Shorts]

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?

 

[Walking Shorts]

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

 

[Walking Shorts]

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...

 

[just_jon]

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.

 

[Walking Shorts]

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?

 

[just_jon]

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

 

[Walking Shorts]

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?