Formulas loosing cell reference

[hmjjohnson]

The spreadsheet works in Excel 2000 when you open it in any newer version it is corrupted. Which is fine so we are rewriting it in Excel 2010/2013.

We have data in columns A:E which comes from the refreshable query. Data in F:I are different formulas based on the data from the query. The number of rows we get will vary from query to query. The headers never change just the data. How can we make it so that when no matter the data length the formulas will always autofill up or down depending on the data length?

 

[ca.moazzam]

The simplest solution could be to select formulae in first line of F:I and double click the fill handle (the lower right corner of the highlighted cursor). The formulae would be automatically copied till the last row wherever data is found.

 

[hmjjohnson]

That doesnt work either. We get a #REF. When we join the formula in the table like I have read online we get errors as well. For example: if we have 5 rows of data and the next query had 20 rows. The first 4 had formulas and the very last one had a formula. Everything in the middle had nothing. The difference we have found in excel 2000 and 2013 is in this picture.

excel_zps6d4bc497.jpg Photo by James_Johnson_II | Photobucket

 

[arthurbr]

Cross post Formulas loosing cell reference and at Formulas loosing cell reference