#REF! Error when using INDIRECT to SUM table column

[Valerie111]

Hey there,

I'm combining 2 different Excel Templates to make a budget, where one sheet is a yearly summary, and the rest are monthly budgets. Each monthly sheet contains tables with the general name tblMONTHCategory, where MONTH is the first 3 letters of that month, and Category are the budget categories. Those months and categories are summarized on the yearly summary, as below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Expenses[/TD]
[TD]Jan (Cell C10)[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]Food (Cell B11)[/TD]
[TD]=SUM(INDIRECT("tbl"&$C$10&""&B11&"[Actual Cost]")) which becomes
=SUM(tblJANFood[Actual Cost])[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Clothes
(Cell B12)[/TD]
[TD]=SUM(INDIRECT("tbl"&$C$10&""&B12&"[Actual Cost]")) which becomes
=SUM(tblJANClothes[Actual Cost])[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]School
(Cell B13)[/TD]
[TD]=SUM(INDIRECT("tbl"&$C$10&""&B13&"[Actual Cost]"))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Personal Care
(Cell B14)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I started with the formula as shown in the table and copied it into the other cells, which worked great until the line "Personal Care", since the table uses "PersonalCare", without the spaces. I saw where you can combine INDIRECT and SUBSTITUTE to remove spaces, and got this:

=SUM(INDIRECT("tbl"&$C$10&""&INDIRECT(SUBSTITUTE(B14,"",""))&"[Actual Cost]"))​

which resulted in a #REF! error. Using the Evaluate Formula button, I found that Excel turns this formula into

=SUM(INDIRECT(tblJANINDIRECT(SUBSTITUTE(B14,"",""))&"[Actual Cost]"))​

which explains the error message. Any ideas on how to fix it?


EDIT:
Just realized that Excel shows an error for the SUBSTITUTE(B14, " ", "") part of the function, changing " " (with the space in between), to "". How do I stop it? It wont let me use click out of the cell unless the space is removed.


Thanks for any help you can give!!
-Val

​

 

[Valerie111]

So... While taking a break to eat a can mandarin oranges I realized what I did wrong. The formula should really read:

=SUM(INDIRECT("tbl"&$C$10&""&SUBSTITUTE(B14," ","")&"[Actual Cost]"))

I guess I should take mandarin orange breaks more often