MS Excel cell names crossed-referred in workbooks

  Nosmas 12:58 09 Apr 03
Locked

In workbook A I have a number of worksheets dealing with calculation of interest. I have named certain cells AnnRate1, AnnRate2 etc. and refer to these in formulae in other cells. In workbook B (also multiple sheets) formulae I also refer to the cell names in workbook A. The problem I have is that a formula in one cell only needs the workbook name to retrieve the (other workbook) cell value in order to evaluate the formula

e.g. =ROUND(20000*'Workbook A.xls'!AnnRate9,2)/12

whereas using the same syntax in a different worksheet of workbook B results in #REF! By experiment I have found I need to include the sheet name in the formula

e.g. =ROUND(20000*'[Workbook A.xls]Worksheet Tab'!AnnRate2,2)/12

Can anyone suggest why the first syntax doesn't always work? In both workbooks each sheet is for a different year, and when I need to open a sheet for a new year I copy the latest sheet, rename it and edit the data contained in it. Could the copying method have any impact upon the syntax required?

  johnem 13:53 09 Apr 03

Nosmas, not sure if this is of help but, if as I understand you have copied one worksheet into a new location and renamed, then any inbuilt cell references are trying to look for the now non existant cells hence the #REF. You may have to utilise the paste special option to either "paste link" or "paste formula".
I have had a similar problem and that's how I managed to effect a result. This may not be the most efficient way.

  Nosmas 14:22 09 Apr 03

Thanks for your suggestion. I had thought of that, but having gone to menu Insert > Name > Paste > Paste List the resulting list of all cell names in the workbook correctly identifies the sheet and cell co-ordinates for each of the named cells.

  Megatyte 14:59 09 Apr 03

In the referred book if the Worksheet is not directly referenced then it defaults to the Active sheet.

AH

This thread is now locked and can not be replied to.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

The Pantone Colour of the Year 2017 is Green

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…