Import & Refresh- Changing Formulas

[SMH]

There are two tabs within one workbook. Sheet A has multiple formulas
reading data from Sheet B; Sheet B is an imported text file that can have a
range of results. I.E. Populated up to row 5 or row 60. I have created a
connection to that text file that allows me to "refresh" the results at any
time.

Once refreshed, the some of the formulas I created in Sheet A are then
altered. Why is this happening? My thoughts: when I "set up" the formula's,
my example data is only including data up to row 30, however, when I refresh
with real data, the data can alter to either be 60 rows or 5 rows, so the
formulas are jumping.

Is there any way to "hard code" the formulas so that no matter what changes
to the data, they will not change? Any assistance is greatly appreciated.

Example:
Sheet A formulas:
A2= Data!H3
A3= Data!H15
A4= Data!H27
A5= Data!H39

Once I "Refresh" the data in Sheet B, Sheet A's formulas say:
A2= Data!H3
A3= Data!H15
A4= Data!H27
A5= Data!H64

 

[GTVT06]

There are two tabs within one workbook.  Sheet A has multiple formulas
reading data from Sheet B; Sheet B is an imported text file that can have a
range of results.  I.E. Populated up to row 5 or row 60.  I have created a
connection to that text file that allows me to "refresh" the results at any
time.

Once refreshed, the some of the formulas I created in Sheet A are then
altered.  Why is this happening?  My thoughts: when I "set up" the formula's,
my example data is only including data up to row 30, however, when I refresh
with real data, the data can alter to either be 60 rows or 5 rows, so the
formulas are jumping.

Is there any way to "hard code" the formulas so that no matter what changes
to the data, they will not change?  Any assistance is greatly appreciated.

Example:
Sheet A formulas:
A2= Data!H3
A3= Data!H15
A4= Data!H27
A5= Data!H39

Once I "Refresh" the data in Sheet B, Sheet A's formulas say:
A2= Data!H3
A3= Data!H15
A4= Data!H27
A5= Data!H64

Try entering them as follows
A2= Data!$H$3
A3= Data!$H$15
A4= Data!$H$27
A5= Data!$H$64
the $'s should lock them in.

 

[SMH]

Unfortunately that doesn't work. When I select "refresh", it still updates
the formula even with using the $. It returns:

A5= Data!$H$64 instead of A5= Data!$H$39

Other suggestions?

 

[GTVT06]

Unfortunately that doesn't work.  When I select "refresh", it still updates
the formula even with using the $.  It returns:

A5= Data!$H$64 instead of A5= Data!$H$39

Other suggestions?






- Show quoted text -

what about if you reference to the cell's value by using the offset
formula?

A2= OFFSET(Data!$H$1,2,0)
A3= OFFSET(Data!$H$1,14,0)
A4= OFFSET(Data!$H$1,26,0)
A5= OFFSET(Data!$H$1,38,0)

 

[SMH]

That worked! But what about my other formulas that I have referenced to
Sheet B, how do I include the "offset" to them?

=IFERROR(SUM((Data!$I$39)/Data!$G$39), )

and

=IFERROR(SUM(Data!$J$3:$J$39)/SUM(Data!$I$3:$I$39), )

Thank you!!

 

[GTVT06]

That worked!  But what about my other formulas that I have referenced to
Sheet B, how do I include the "offset" to them?

=IFERROR(SUM((Data!$I$39)/Data!$G$39), )

and

=IFERROR(SUM(Data!$J$3:$J$39)/SUM(Data!$I$3:$I$39), )

Thank you!!

No problem, try

=IFERROR(SUM((OFFSET(Data!$I$1,38,0))/OFFSET(Data!$G$1,38,0), )

and

=IFERROR(SUM(OFFSET(Data!$J$1,2,0):OFFSET($J$1,38,0)/SUM(OFFSET(Data!$I
$1,2,0):OFFSET($I$1,38,0), )

 

[SMH]

Unfortunately I'm not getting the correct result. I don't know if there are
enough parentheses, so I tried:

=IFERROR(SUM(OFFSET(Data!$J$1,2,0):OFFSET($J$1,38,0))/SUM(OFFSET(Data!$I$1,2,0):OFFSET(Data!$I$1,38,0)),)

And it still isn't working!

 

[GTVT06]

do either of these work?

=IFERROR(SUM((OFFSET(Data!$I$1,38,0))/OFFSET(Data!$G$1,38,0)), )


and


=IFERROR(SUM(OFFSET(Data!$J$1,2,0):OFFSET($J$1,38,0))/SUM(OFFSET(Data!
$I$1,2,0):OFFSET($I$1,38,0)), )