INDIRECT and name ranges

[Diane]

Is there a better (more effective/efficient) way to handle
this? The sample formula below pulls various information
into a summary sheet from worksheets which are copied
using a VBA procedure into the workbook from various
companies' financial workbooks. The individual company
financial workbooks are created from templates, so each
worksheet copied into the summary workbook from the
individual company financial workbooks has the same named
ranges and in each, Columns A,B,C and D are used for
labels and sub-labels. If a company's financial
spreadsheet is not available, a worksheet is inserted into
the summary workbook (via VBA) with notification to the
viewer that the information is not yet available. Sample
formula used on summary sheet (column C contains company
names which matches company sheet names):

=INDIRECT("'"&$C7&"'"!TotalIncome")

Final workbook example:

Sheet Name Contents

Summary -Summary of certain line item values from
each company's spreadsheet referenced by
a named range (which is usually a cell)

Company A-is a copy of "Company A"'s worksheet

Company B-is a copy of "Company B"'s worksheet

Company C-is a worksheet with "Company C" as a
heading and Text to notify viewer that
financial information is not yet available

This works unless a company's financial workbook was not
available to copy and a new worksheet was added to the
summary workbook indicating so. There are no named ranges
associated with the added worksheet. The information
which appears on the summary sheet for the referenced
company is pulled from the first spreadsheet with matching
named ranges, and therefore, is incorrect. I changed the
formula to include a condition:

=IF(SUM(INDIRECT("'"&$C7&"'!E9:I223"))<>0,INDIRECT
("'"&$C7"'! TotalIncome"),"Not Posted")

I can use this cell as a reference for the remaining cells
in the row of information, i.e.:

=IF($G7="Not Posted","",INDIRECT("'"&$C7&"'!TotalOperExp"))

which seems to work ... I was just wondering if there was
a more effective way to handle this so that the correct
information is returned in this instance.

Thank you.

Diane
Windows2000
Excel2000

 

[Diane]

Sometimes its good to walk away from something and come
back to it later. Figured it out. Seems I had
inadvertently copied ranges into my template. Sorry for
the post.

 

[dvt]

Sometimes its good to walk away from something and come
back to it later. Figured it out. Seems I had
inadvertently copied ranges into my template. Sorry for
the post.

I'd say there's no reason to apologize. I may have learned something. Did
your original sheet work just fine once you had corrected the error? Did
the reference to a nonexistent named range return #NAME?

Dave
dvt at psu dot edu