Help make this simpler (if statements)

**harmbasi** · 04-12-2012, 10:38 PM

Hey guys,

I have this formula which works fine, but I wan't to see if it can be simplified.

=IF($A$1="JAN",[CUISPremiumRecB1.xls]JAN12!$C$13,IF($A$1="FEB",'[CUISPremiumRecB1.xls]FEB12’!$C$13,IF($A$1="MAR",[CUISPremiumRecB1.xls]MAR12!$C$13,IF($A$1="APR",[CUISPremiumRecB1.xls]APR12!$C$13,IF($A$1="MAY",[CUISPremiumRecB1.xls]MAY12!$C$13,IF($A$1="JUN",[CUISPremiumRecB1.xls]JUN12!$C$13,IF($A$1="JUL",[CUISPremiumRecB1.xls]JUL12!$C$13,IF($A$1="AUG",[CUISPremiumRecB1.xls]AUG12!$C$13,IF($A$1="SEP",[CUISPremiumRecB1.xls]SEP12!$C$13,IF($A$1="OCT",[CUISPremiumRecB1.xls]OCT12!$C$13,IF($A$1="NOV",[CUISPremiumRecB1.xls]NOV12!$C$13,IF($A$1="DEC",[CUISPremiumRecB1.xls]DEC12!$C$13,))))))))))))

Ok so what I'm trying to do is:

I work at a credit union and needed to balance our insurance GL's. I have 12 files which I update with information (CUISPremiumRecB1, CUISPremiumRecB2, etc...) and each file will have a lot of sheets, 1 for each month (JAN12, FEB12, etc). Once I've balanced the 12 branches, I need to pull certain information to this sheet, which I give to others to process the postings. Simply this sheet shows which accounts will be debited/ credited. As you can see from the formula above I have A1 where I will type in which month's information I want. So if I put in JAN, the sheet will update with information from my Branch 1's file for January 12.

The thing is I have 12 branches + multiple postings like this for each branch + different months. If I do it like this, it will take a long time. I would have to edit the formula for each branch + each year it will have to be edited as well.

Is there any way so Excel can automatically update based on multiple conditions. Per say. I could have multiple cells where I enter information. So A1 could be the branch, B1 could be the month and C1 could be the year.

Eg. A1= Branch 1 B1=January C1=2012

Now I would need Excel to automatically find the file CUISPremiumRecB1 and find the sheet named JAN12 and get the value from C13. Any help would be appreciated.

Thanks.

**protonLeah** · 04-12-2012, 11:30 PM

Forum Rules
Please read and adhere to these simple rules!

1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more hours have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.

2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Select your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button). The result will appear like this in the post window:

[code]
your code here ...
and here ...
and here
[/code]

**rscsmith** · 04-13-2012, 01:37 AM

This looks like a job for INDIRECT:

Please Login or Register  to view this content.

This does rely on the other workbooks being open. If you want to do it with the other files closed, you'd be in macro territory (which we can help you with).

Best regards, Rob.

**protonLeah** · 04-13-2012, 02:58 PM

@rscsmith
In the future, when a request is made to modify a title, or use code tags, etc., you should wait for compliance before responding with a solution. That title is not searchable (and, therefore, of no help to anyone else); the post is difficult to read without the code tags.