D
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
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