Hi Excel Gurus,
The Objective
My objective is to use VBA to automatically input information into this database from a VBA entry form. Upon entry of the data, a unique Index is generated as well as a “Tracking Add-on” is created to track the adjustments to the indexed item.
The Problem
When new data is entered the "Countif" formula changes itself to accommodate to the new data which messes up “Tracking Add-on”. Instead of the “Tracking Add-on” adding a sequential number (i.e. 1,2,3) it adds the latest version of the number (i.e. 3) to all entry with the same Index root. Additionally, the font reverts back to the original table style and not my altered style: Arial in black. The “Cell & Countif” function is the formula that is causing the error, the ID and Index are work fine.
Attempted Solutions
I’ve rebuild the sheet twice. I’ve tinkered with all the formulas. I’ve reviewed my formulas and VBA code against other excel sheets that I’ve created to see if I’ve made any significant changes, but I haven’t. Thus, I haven’t been able to solve this issue! Any help would be appreciated!
Possible Solutions
Is there another formula that I could use instead of the “Cell & Countif”? Or, a formula locking setting that I could change?
Table Details
Sincerely,
KP
Preview attachment Database Issue.PNG
Database Issue.PNG
14 KB
The Objective
My objective is to use VBA to automatically input information into this database from a VBA entry form. Upon entry of the data, a unique Index is generated as well as a “Tracking Add-on” is created to track the adjustments to the indexed item.
The Problem
When new data is entered the "Countif" formula changes itself to accommodate to the new data which messes up “Tracking Add-on”. Instead of the “Tracking Add-on” adding a sequential number (i.e. 1,2,3) it adds the latest version of the number (i.e. 3) to all entry with the same Index root. Additionally, the font reverts back to the original table style and not my altered style: Arial in black. The “Cell & Countif” function is the formula that is causing the error, the ID and Index are work fine.
Attempted Solutions
I’ve rebuild the sheet twice. I’ve tinkered with all the formulas. I’ve reviewed my formulas and VBA code against other excel sheets that I’ve created to see if I’ve made any significant changes, but I haven’t. Thus, I haven’t been able to solve this issue! Any help would be appreciated!
Possible Solutions
Is there another formula that I could use instead of the “Cell & Countif”? Or, a formula locking setting that I could change?
Table Details
ID | Index | Index w/ Add-on |
159 | B03-P01-R03-A03-19 | B03-P01-R03-A03-191 |
=ROW(A161)-2 | =CONCATENATE([Branch ID],$A$1,[Program ID],$A$1,[Recom. ID],$A$1,[Action Item ID],$A$1,[@[Year ID]]) | =B161&COUNTIF($B$3:B161,B161) |
Sincerely,
KP
Preview attachment Database Issue.PNG
Database Issue.PNG
14 KB
