R
Robin
I have this strange problem with a single text defined cell value changes
when XL03 worksheet form is opened each day. Each day, the users have to
re-enter the value in order for the formulas to work correctly and then they
transfer the answers to another monthly "summary" workbook.
-------------------------------------------------------------------------
A B C D
1 Attendance Sheet [ ] [ ]
2
A1 = Title of Form`
C1 = (1 alpha character between A and E) referenced as SHIFT
D2 = (4 to 5 alpha character) referenced as TEAM
The workbook extensively uses the NAME variables, SHIFT and TEAM, as
parameters and concatenated parameters for functions such as VLOOKUP, INDEX,
IF, and so forth in calculating various productivity measures.
During the development and initial user testing, C1, "SHIFT" retained the
value selected by the user from the drop-down Data Validation list when the
worksheet is opened, saved, closed, or copied. Because each of the SHIFT/TEAM
combinations have a different employees, I originally worked with a Master
tab and 16 SHIFT/TEAM copies so I could do GROUP editing as we tweaked the
last little changes and suggestions as we previewed the new form to the team
leaders and managers.
Once I split the tabbed sheets into their own individual workbooks I started
having a mysterious problem that I cannot find a solution from the EXCEL-G
and EXCEL-L archives.
The "SHIFT" cell would lose its previously saved value; sometimes opening
with a blank cell or the contents of B1, "attendance sheet."
I have tried several different strategies:
Deleting the SHIFT data validation;
• Removing the data validation and drop-down list and then entering the
single-character value of each form then locking and hiding the cell;
• Copying a particular SHIFT-TEAM form that retained the C1 value between
open and close;
• Adding an apostrophe, ', before the alpha character thinking XL was seeing
a NAME variable instead of text value in a text defined cell
• Creating a single worksheet master and creating copies for each SHIFT/TEAM
combination
• Originally I used just a range of cells, not NAME reference if it makes a
difference.
• I tried putting an apostrophe in the cells of the text vale that formed
the range list. Finally, I just deleted the data validation still when you
enter a value in that particular CELL is not saved when you reopen or copy
the file.
• I even tried changing the value length from a single character to a
five-character string such as “Ateam†instead of “A†and, of course, this
required using LEFT function to create single-character parameter.
Since this is a user supported/maintained application, I am constrained to
the menu defined commands; no VBA. Although the application is better suited
to ACCESS, this financial department uses XL exclusively with a range of
individual user XL skills but mostly basic XL data entry.
Additional Information: WinXP networked environment; workbooks reside on a
shared drive accessible from any desktop on site.
The finance staff has been using these forms for several week as part of the
final testing but I will not be there when they it roll-out to even more
unsophisticated users, the team leaders and supervisors. I would like to fix
this before I move on without using VBA programming.
In advance, thank you for any help or suggestions.
when XL03 worksheet form is opened each day. Each day, the users have to
re-enter the value in order for the formulas to work correctly and then they
transfer the answers to another monthly "summary" workbook.
-------------------------------------------------------------------------
A B C D
1 Attendance Sheet [ ] [ ]
2
A1 = Title of Form`
C1 = (1 alpha character between A and E) referenced as SHIFT
D2 = (4 to 5 alpha character) referenced as TEAM
The workbook extensively uses the NAME variables, SHIFT and TEAM, as
parameters and concatenated parameters for functions such as VLOOKUP, INDEX,
IF, and so forth in calculating various productivity measures.
During the development and initial user testing, C1, "SHIFT" retained the
value selected by the user from the drop-down Data Validation list when the
worksheet is opened, saved, closed, or copied. Because each of the SHIFT/TEAM
combinations have a different employees, I originally worked with a Master
tab and 16 SHIFT/TEAM copies so I could do GROUP editing as we tweaked the
last little changes and suggestions as we previewed the new form to the team
leaders and managers.
Once I split the tabbed sheets into their own individual workbooks I started
having a mysterious problem that I cannot find a solution from the EXCEL-G
and EXCEL-L archives.
The "SHIFT" cell would lose its previously saved value; sometimes opening
with a blank cell or the contents of B1, "attendance sheet."
I have tried several different strategies:
Deleting the SHIFT data validation;
• Removing the data validation and drop-down list and then entering the
single-character value of each form then locking and hiding the cell;
• Copying a particular SHIFT-TEAM form that retained the C1 value between
open and close;
• Adding an apostrophe, ', before the alpha character thinking XL was seeing
a NAME variable instead of text value in a text defined cell
• Creating a single worksheet master and creating copies for each SHIFT/TEAM
combination
• Originally I used just a range of cells, not NAME reference if it makes a
difference.
• I tried putting an apostrophe in the cells of the text vale that formed
the range list. Finally, I just deleted the data validation still when you
enter a value in that particular CELL is not saved when you reopen or copy
the file.
• I even tried changing the value length from a single character to a
five-character string such as “Ateam†instead of “A†and, of course, this
required using LEFT function to create single-character parameter.
Since this is a user supported/maintained application, I am constrained to
the menu defined commands; no VBA. Although the application is better suited
to ACCESS, this financial department uses XL exclusively with a range of
individual user XL skills but mostly basic XL data entry.
Additional Information: WinXP networked environment; workbooks reside on a
shared drive accessible from any desktop on site.
The finance staff has been using these forms for several week as part of the
final testing but I will not be there when they it roll-out to even more
unsophisticated users, the team leaders and supervisors. I would like to fix
this before I move on without using VBA programming.
In advance, thank you for any help or suggestions.