Rackin' my brain

Walking Shorts

New Member
Joined
Nov 23, 2005
Messages
17
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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
For your first question.

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

For your second question, perhaps something like this.

=INDIRECT("[" & B3 & "]Sheet1!C4")
 
Upvote 0
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?
 
Upvote 0
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 :LOL:
 
Upvote 0
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... :oops:
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Are you aware that, with INDIRECT, all the target workbooks must be open in order for it to work?
 
Upvote 0
:eek: 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?
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top