| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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), ) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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), ) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)), ) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I refresh cells after changing the format? | Excel Discussion (Misc queries) | |||
| Error on Refresh Data Import from Access | Excel Discussion (Misc queries) | |||
| How do i refresh external data import in excel in seconds | New Users to Excel | |||
| Some formulas need file refresh in same worksheet | Excel Worksheet Functions | |||
| How do I refresh the formulas in Excel? | Excel Worksheet Functions | |||
Linear Mode
