Help needed for Indirect formula

**spurredhal** · 01-14-2013, 06:01 PM

I am struggling with an Indirect formula which is designed to link two worksheets, one called "Report" and one called "Budget".

I want to change the name of the worksheet called "Budget" to the name of "Annual Accounting". When I do this though, I get the error #REF! in the cell #B23 where the formula below is in. Can you advise me how to edit the name of the worksheet without messing up the formula?

Formula:
=SUM(OFFSET(INDIRECT("Budget!A"&MATCH(Report!A23,Budget!$A:$A,0)),0,IF(ytd,1,$B$5),1,IF(ytd,$B$5,1)))

I attached of the area where I'm getting the error.

Thank you-Max

Spreadsheet Screenshot.jpg

**rcm** · 01-14-2013, 06:28 PM

Does the indirect alone render a value?
What if ytd is not 1 (you want a "false")? or for the second if is not equal to b5? (another "false") ?

**Richard Buttrey** · 01-14-2013, 06:33 PM

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

Please don't upload pictures. None of us are inclined to recreate your data when you have a workbook available - see guidelines in the Rules area of this forum.

**Andrew-R** · 01-14-2013, 06:36 PM

If your sheet name has a space in it then it needs to be enclosed in single quotes:

=SUM(OFFSET(INDIRECT("'Annual Accounting'!A"&MATCH(Report!A23,Budget!$A:$A,0)),0,IF(ytd,1,$B$5),1,IF(ytd,$B$5,1)))