I've taken a (single sheet) workbook from my boss and created several others to track status of quotes 24hr/7days.
I did this by creating detailed sheets that use the EXTERNAL DATA pull ( to pull from the tab called "MASTER" within the same workbook )....
Then, I created the "Overview Sheets" to pull "linked" data from functions entered at the base of each of the detailed tabs....
( My functions/formulas on the detailed sheets are highlighted in YELLOW and are stored on either Row 500 or Row 1000.)
My goal was to create all the reporting sheets, protect them all EXCEPT for the one called "MASTER" which will be the universal "entry" page for many people to use and update while the workbook is loaded on a company server. All of my development has been done while the file is on my hard drive though. ( as I know servers can do weird things sometimes ).....
Initially, I was going to build a web-site and load it there -- but soon discovered that you can not PROTECT and SHARE a workbook that is loaded onto a web-site ( without defeating the purpose of having it accessible (updateable) to many people at any time )..... Putting it on a web-site limits it to one person being able to update the status of a line item - if I understood the research correctly.
Meanwhile, I thought I was just about done - to give it to my boss-- -- and realized that something's going wrong with the REFRESH process.
I have it set to 1 minute ( to keep it constantly updating data from the MASTER sheet )....
It worked great for a day or 2 - but now the workbook seems to be confused.
The LOC ( location) tabs have a formula that looks at all the line items at the top of the sheet and reports the LONGEST NUMBER OF DAYS that a quote has been open. ( for THAT particular location code )....
I found that a lot of cells on the MASTER had ghost blanks at the end of the data and wasn't picking up the proper calculation for HOW MANY ARE CLOSED "C" and HOW MANY ARE OPEN "O" on each of the STATUS sheets.... ( Stat O, Stat PC, Stat C, Stat X ) SO --- I went through the master and performed a TRIM function on all the important columns to clean out blanks that would keep the COUNTA functions from reporting accurately.....
Once I did this ----- I found that my LONGEST # of DAYS formulas ( on each LOC sheet ) no longer worked.... which made no sense!
This morning I went down the STATUS and the DAYS OPEN columns of the MASTER sheet and clicked at the end of each cell - then hit ENTER.... When I did this --- the data jumped to the left and "fed properly" to the LOC tabs....
I hit SAVE --- BUT as soon as the "refresh occurred" -- the # of DAYS OPEN formulas quit working....
I don't understand WHY this is happening -- or how to fix it permanently?
SOME HOW -- I was able to fix the LOC C tab to work properly --- but LOC E, R,S,T etc. are still not working properly.
I can send the workbook if you like!?! .... ChrisOK
I did this by creating detailed sheets that use the EXTERNAL DATA pull ( to pull from the tab called "MASTER" within the same workbook )....
Then, I created the "Overview Sheets" to pull "linked" data from functions entered at the base of each of the detailed tabs....
( My functions/formulas on the detailed sheets are highlighted in YELLOW and are stored on either Row 500 or Row 1000.)
My goal was to create all the reporting sheets, protect them all EXCEPT for the one called "MASTER" which will be the universal "entry" page for many people to use and update while the workbook is loaded on a company server. All of my development has been done while the file is on my hard drive though. ( as I know servers can do weird things sometimes ).....
Initially, I was going to build a web-site and load it there -- but soon discovered that you can not PROTECT and SHARE a workbook that is loaded onto a web-site ( without defeating the purpose of having it accessible (updateable) to many people at any time )..... Putting it on a web-site limits it to one person being able to update the status of a line item - if I understood the research correctly.
Meanwhile, I thought I was just about done - to give it to my boss-- -- and realized that something's going wrong with the REFRESH process.
I have it set to 1 minute ( to keep it constantly updating data from the MASTER sheet )....
It worked great for a day or 2 - but now the workbook seems to be confused.
The LOC ( location) tabs have a formula that looks at all the line items at the top of the sheet and reports the LONGEST NUMBER OF DAYS that a quote has been open. ( for THAT particular location code )....
I found that a lot of cells on the MASTER had ghost blanks at the end of the data and wasn't picking up the proper calculation for HOW MANY ARE CLOSED "C" and HOW MANY ARE OPEN "O" on each of the STATUS sheets.... ( Stat O, Stat PC, Stat C, Stat X ) SO --- I went through the master and performed a TRIM function on all the important columns to clean out blanks that would keep the COUNTA functions from reporting accurately.....
Once I did this ----- I found that my LONGEST # of DAYS formulas ( on each LOC sheet ) no longer worked.... which made no sense!
This morning I went down the STATUS and the DAYS OPEN columns of the MASTER sheet and clicked at the end of each cell - then hit ENTER.... When I did this --- the data jumped to the left and "fed properly" to the LOC tabs....
I hit SAVE --- BUT as soon as the "refresh occurred" -- the # of DAYS OPEN formulas quit working....
I don't understand WHY this is happening -- or how to fix it permanently?
SOME HOW -- I was able to fix the LOC C tab to work properly --- but LOC E, R,S,T etc. are still not working properly.
I can send the workbook if you like!?! .... ChrisOK
