burnedfish
New Member
- Joined
- Jan 28, 2017
- Messages
- 2
Greetings!
I have a table linked to Access, with a column I inserted into the Excel table, so I could use a formula. However, when I refresh the table, the range in the formula changes. Nothing else in the formula changes, just the range. Here is the formula as it should be:
Cell T3: =IFERROR(MAX(MIN(P3,VLOOKUP(K3,'On-Hand'!$A:$P,8,FALSE)-SUMIF(backlog!$K$2:$K2,K3,$P$2:P2)),0),0)
Cell T4: =IFERROR(MAX(MIN(P4,VLOOKUP(K4,'On-Hand'!$A:$P,8,FALSE)-SUMIF(backlog!$K$2:$K3,K4,$P$2:P3)),0),0)
Cell T5: =IFERROR(MAX(MIN(P5,VLOOKUP(K5,'On-Hand'!$A:$P,8,FALSE)-SUMIF(backlog!$K$2:$K4,K5,$P$2:P4)),0),0)
etc...
Once the table is refreshed, the range changes:
Cell T3: =IFERROR(MAX(MIN(P3,VLOOKUP(K3,'On-Hand'!$A:$P,8,FALSE)-SUMIF(backlog!$K$2:$K63,K3,$P$2:P2)),0),0)
Cell T4: =IFERROR(MAX(MIN(P4,VLOOKUP(K4,'On-Hand'!$A:$P,8,FALSE)-SUMIF(backlog!$K$2:$K220,K4,$P$2:P3)),0),0)
Cell T5: =IFERROR(MAX(MIN(P5,VLOOKUP(K5,'On-Hand'!$A:$P,8,FALSE)-SUMIF(backlog!$K$2:$K229,K5,$P$2:P4)),0),0)
My external data properties are as follows:
Preserve column sort/filter/layout
Insert cells for new data, delete unused cells
1. Does anyone have any ideas how to stop the formula ranges from changing when the table is refreshed?
2. Similarly, if I insert a column into the table, for typing meeting notes for each row, those also get rearranged when the table is refreshed. Is this the same issue, and/or how can I preserve notes in their original row?
This is my first posting here, but have been visiting this site for years, and I can testify that I could not have advanced my career as far as I have, without the tremendous help of this forum and the experts here. So THANK YOU ALL!
I have a table linked to Access, with a column I inserted into the Excel table, so I could use a formula. However, when I refresh the table, the range in the formula changes. Nothing else in the formula changes, just the range. Here is the formula as it should be:
Cell T3: =IFERROR(MAX(MIN(P3,VLOOKUP(K3,'On-Hand'!$A:$P,8,FALSE)-SUMIF(backlog!$K$2:$K2,K3,$P$2:P2)),0),0)
Cell T4: =IFERROR(MAX(MIN(P4,VLOOKUP(K4,'On-Hand'!$A:$P,8,FALSE)-SUMIF(backlog!$K$2:$K3,K4,$P$2:P3)),0),0)
Cell T5: =IFERROR(MAX(MIN(P5,VLOOKUP(K5,'On-Hand'!$A:$P,8,FALSE)-SUMIF(backlog!$K$2:$K4,K5,$P$2:P4)),0),0)
etc...
Once the table is refreshed, the range changes:
Cell T3: =IFERROR(MAX(MIN(P3,VLOOKUP(K3,'On-Hand'!$A:$P,8,FALSE)-SUMIF(backlog!$K$2:$K63,K3,$P$2:P2)),0),0)
Cell T4: =IFERROR(MAX(MIN(P4,VLOOKUP(K4,'On-Hand'!$A:$P,8,FALSE)-SUMIF(backlog!$K$2:$K220,K4,$P$2:P3)),0),0)
Cell T5: =IFERROR(MAX(MIN(P5,VLOOKUP(K5,'On-Hand'!$A:$P,8,FALSE)-SUMIF(backlog!$K$2:$K229,K5,$P$2:P4)),0),0)
My external data properties are as follows:
Preserve column sort/filter/layout
Insert cells for new data, delete unused cells
1. Does anyone have any ideas how to stop the formula ranges from changing when the table is refreshed?
2. Similarly, if I insert a column into the table, for typing meeting notes for each row, those also get rearranged when the table is refreshed. Is this the same issue, and/or how can I preserve notes in their original row?
This is my first posting here, but have been visiting this site for years, and I can testify that I could not have advanced my career as far as I have, without the tremendous help of this forum and the experts here. So THANK YOU ALL!