Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL03 - Cell Text value not saved
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL03 - Cell Text value not saved
Are you sure the users are saving the workbook before they close it?
I am not aware of any problem in excel that causes data to be lost as you describe. -- Regards, Tom Ogilvy "Robin" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL03 - Cell Text value not saved
Yes
I selected some workbooks, open them and the cell was blank; I entered a text value, saved and closed. reopened and either the cell is blank or "attendance" from the adjacent cell has slipped into it. "Tom Ogilvy" wrote: Are you sure the users are saving the workbook before they close it? I am not aware of any problem in excel that causes data to be lost as you ... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL03 - Cell Text value not saved
Someone on the EXCEL-L list suggest that "SHIFT," the NAME reference of the
cell in question, may be an undocumentated reserve word given that the cell blanks or shifts the contents of the cell on the left into it. I have not had an opportunity to check this out. Is that plausable hypothesis? "Robin" wrote: Yes I selected some workbooks, open them and the cell was blank; I entered a text value, saved and closed. reopened and either the cell is blank or "attendance" from the adjacent cell has slipped into it. "Tom Ogilvy" wrote: Are you sure the users are saving the workbook before they close it? I am not aware of any problem in excel that causes data to be lost as you ... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation drop-down width, with named range source (XL03 and | Excel Worksheet Functions | |||
Sub to extract uniques from 200k data in xl03 | Excel Programming | |||
IF function in Data Validation- XL03 | Excel Discussion (Misc queries) | |||
How do I auto-alphabatize in XL03? | Excel Discussion (Misc queries) | |||
Multi-line cell text content saved in XML | Excel Programming |