Import & Refresh- Changing Formulas
 

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

Import & Refresh- Changing Formulas
 

On Mar 24, 2:46*pm, SMH wrote:
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.

Import & Refresh- Changing Formulas
 

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" wrote:

On Mar 24, 2:46 pm, SMH wrote:
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.

Import & Refresh- Changing Formulas
 

On Mar 24, 3:37*pm, SMH wrote:
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" wrote:
On Mar 24, 2:46 pm, SMH wrote:
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.- Hide quoted text -

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

Import & Refresh- Changing Formulas
 

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" wrote:

On Mar 24, 3:37 pm, SMH wrote:
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" wrote:
On Mar 24, 2:46 pm, SMH wrote:
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.- Hide quoted text -

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

Import & Refresh- Changing Formulas
 

On Mar 24, 4:08*pm, SMH wrote:
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), )

Import & Refresh- Changing Formulas
 

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" wrote:

On Mar 24, 4:08 pm, SMH wrote:
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), )

Import & Refresh- Changing Formulas
 

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)), )