Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hidden formulas
About a month ago I wrote a short set of formulas to show a table of
win/losses and % won per session and running totals. Table below: A B C D E F G H I J Running Total Session Wins to Reach Losses Wins % Total % 70% 71% 75% 8/18/2007 575 1329 69.80% 1342 1408 1725 9/1/2007 615 1415 69.70% 126 68.25% 1435 1506 1845 9/2/2007 627 1439 69.65% 36 66.67% 1463 1535 1881 9/3/2007 630 1451 69.73% 15 80.00% 1470 1542 1890 9/16/2007 642 1493 69.93% 54 77.78% 1498 1572 1926 I though I had done it with formulas in cells. The idea was to enter data in Columns A B C then the formulas would figure out the other columns for that row. When I went back to update the formulas, I can't find them in the cells or as a macro for a sheet change. I didn't remember them being set up with a change sheet macro and or hidden some way. When yo look at the sheet, the cells and formulas bars are blank. But when you enter the data in columns A-C the formulas appear in the cells for the new row of data. When I went to VB there are no macros that I can find that are entering these formulas. How can I find these formulas to make changes? Regards Mck |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hidden formulas
It sounds like you have a Worksheet_Change macro in the sheet code module.
Right click the sheet tab at the bottom of the screen, then click View Code on the pop up menu. It should take you right to the code that is reacting to your entries. "Mick" wrote: About a month ago I wrote a short set of formulas to show a table of win/losses and % won per session and running totals. Table below: A B C D E F G H I J Running Total Session Wins to Reach Losses Wins % Total % 70% 71% 75% 8/18/2007 575 1329 69.80% 1342 1408 1725 9/1/2007 615 1415 69.70% 126 68.25% 1435 1506 1845 9/2/2007 627 1439 69.65% 36 66.67% 1463 1535 1881 9/3/2007 630 1451 69.73% 15 80.00% 1470 1542 1890 9/16/2007 642 1493 69.93% 54 77.78% 1498 1572 1926 I though I had done it with formulas in cells. The idea was to enter data in Columns A B C then the formulas would figure out the other columns for that row. When I went back to update the formulas, I can't find them in the cells or as a macro for a sheet change. I didn't remember them being set up with a change sheet macro and or hidden some way. When yo look at the sheet, the cells and formulas bars are blank. But when you enter the data in columns A-C the formulas appear in the cells for the new row of data. When I went to VB there are no macros that I can find that are entering these formulas. How can I find these formulas to make changes? Regards Mck |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hidden formulas
My thoughts also. There is not request to activate macros when I open the
workbook either. So I checked the code anyway and there is no code that I can find in the worksheet or the workbook producing these formulas. I was able to copy rows 3-10 to another workbook and it exhibts the same responses. Again there was no worksheet macros that I could find. If I turn off the automatic calculation then when I enter the data nothing happens untill I recalculate with F9. This was written Excel 2000 and a copy of the workbook exhibited the same responses in Excel 2003. If you want a copy of the workbook I would be glad to send it. Regards Mck "JLGWhiz" wrote: It sounds like you have a Worksheet_Change macro in the sheet code module. Right click the sheet tab at the bottom of the screen, then click View Code on the pop up menu. It should take you right to the code that is reacting to your entries. "Mick" wrote: About a month ago I wrote a short set of formulas to show a table of win/losses and % won per session and running totals. Table below: A B C D E F G H I J Running Total Session Wins to Reach Losses Wins % Total % 70% 71% 75% 8/18/2007 575 1329 69.80% 1342 1408 1725 9/1/2007 615 1415 69.70% 126 68.25% 1435 1506 1845 9/2/2007 627 1439 69.65% 36 66.67% 1463 1535 1881 9/3/2007 630 1451 69.73% 15 80.00% 1470 1542 1890 9/16/2007 642 1493 69.93% 54 77.78% 1498 1572 1926 I though I had done it with formulas in cells. The idea was to enter data in Columns A B C then the formulas would figure out the other columns for that row. When I went back to update the formulas, I can't find them in the cells or as a macro for a sheet change. I didn't remember them being set up with a change sheet macro and or hidden some way. When yo look at the sheet, the cells and formulas bars are blank. But when you enter the data in columns A-C the formulas appear in the cells for the new row of data. When I went to VB there are no macros that I can find that are entering these formulas. How can I find these formulas to make changes? Regards Mck |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hidden formulas
No, I don't really want a copy of the workbook. Have you tried checking for
precedents and dependents to see if there is a link? "Mick" wrote: My thoughts also. There is not request to activate macros when I open the workbook either. So I checked the code anyway and there is no code that I can find in the worksheet or the workbook producing these formulas. I was able to copy rows 3-10 to another workbook and it exhibts the same responses. Again there was no worksheet macros that I could find. If I turn off the automatic calculation then when I enter the data nothing happens untill I recalculate with F9. This was written Excel 2000 and a copy of the workbook exhibited the same responses in Excel 2003. If you want a copy of the workbook I would be glad to send it. Regards Mck "JLGWhiz" wrote: It sounds like you have a Worksheet_Change macro in the sheet code module. Right click the sheet tab at the bottom of the screen, then click View Code on the pop up menu. It should take you right to the code that is reacting to your entries. "Mick" wrote: About a month ago I wrote a short set of formulas to show a table of win/losses and % won per session and running totals. Table below: A B C D E F G H I J Running Total Session Wins to Reach Losses Wins % Total % 70% 71% 75% 8/18/2007 575 1329 69.80% 1342 1408 1725 9/1/2007 615 1415 69.70% 126 68.25% 1435 1506 1845 9/2/2007 627 1439 69.65% 36 66.67% 1463 1535 1881 9/3/2007 630 1451 69.73% 15 80.00% 1470 1542 1890 9/16/2007 642 1493 69.93% 54 77.78% 1498 1572 1926 I though I had done it with formulas in cells. The idea was to enter data in Columns A B C then the formulas would figure out the other columns for that row. When I went back to update the formulas, I can't find them in the cells or as a macro for a sheet change. I didn't remember them being set up with a change sheet macro and or hidden some way. When yo look at the sheet, the cells and formulas bars are blank. But when you enter the data in columns A-C the formulas appear in the cells for the new row of data. When I went to VB there are no macros that I can find that are entering these formulas. How can I find these formulas to make changes? Regards Mck |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hidden formulas
But when you enter the data in
columns A-C the formulas appear in the cells for the new row of data. The formulas do not have to be in A-C for there to be a reaction when you put something in those columns. I think you answered your own question with the statement above. The formulas are in all the cells to the right of Col A-C. Something like IF(a0 And b0 And c0, c/(b+c), "") which would give the percentage of wins to games played. "Mick" wrote: About a month ago I wrote a short set of formulas to show a table of win/losses and % won per session and running totals. Table below: A B C D E F G H I J Running Total Session Wins to Reach Losses Wins % Total % 70% 71% 75% 8/18/2007 575 1329 69.80% 1342 1408 1725 9/1/2007 615 1415 69.70% 126 68.25% 1435 1506 1845 9/2/2007 627 1439 69.65% 36 66.67% 1463 1535 1881 9/3/2007 630 1451 69.73% 15 80.00% 1470 1542 1890 9/16/2007 642 1493 69.93% 54 77.78% 1498 1572 1926 I though I had done it with formulas in cells. The idea was to enter data in Columns A B C then the formulas would figure out the other columns for that row. When I went back to update the formulas, I can't find them in the cells or as a macro for a sheet change. I didn't remember them being set up with a change sheet macro and or hidden some way. When yo look at the sheet, the cells and formulas bars are blank. But when you enter the data in columns A-C the formulas appear in the cells for the new row of data. When I went to VB there are no macros that I can find that are entering these formulas. How can I find these formulas to make changes? Regards Mck |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hidden formulas
I went to options and turned on formulas. All cells are blank to the right
of columns A-C in the blank row until data is entered in colums A-C of that row. Once the data is entered the formulas appear. -- Regards Mck "JLGWhiz" wrote: But when you enter the data in columns A-C the formulas appear in the cells for the new row of data. The formulas do not have to be in A-C for there to be a reaction when you put something in those columns. I think you answered your own question with the statement above. The formulas are in all the cells to the right of Col A-C. Something like IF(a0 And b0 And c0, c/(b+c), "") which would give the percentage of wins to games played. "Mick" wrote: About a month ago I wrote a short set of formulas to show a table of win/losses and % won per session and running totals. Table below: A B C D E F G H I J Running Total Session Wins to Reach Losses Wins % Total % 70% 71% 75% 8/18/2007 575 1329 69.80% 1342 1408 1725 9/1/2007 615 1415 69.70% 126 68.25% 1435 1506 1845 9/2/2007 627 1439 69.65% 36 66.67% 1463 1535 1881 9/3/2007 630 1451 69.73% 15 80.00% 1470 1542 1890 9/16/2007 642 1493 69.93% 54 77.78% 1498 1572 1926 I though I had done it with formulas in cells. The idea was to enter data in Columns A B C then the formulas would figure out the other columns for that row. When I went back to update the formulas, I can't find them in the cells or as a macro for a sheet change. I didn't remember them being set up with a change sheet macro and or hidden some way. When yo look at the sheet, the cells and formulas bars are blank. But when you enter the data in columns A-C the formulas appear in the cells for the new row of data. When I went to VB there are no macros that I can find that are entering these formulas. How can I find these formulas to make changes? Regards Mck |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hidden formulas
One more thing to try, and it is really a long shot. Click ToolsMacromacros
and see if there are any macros listed in the Macro drop down dialog box. If not, then I am out of guesses. "Mick" wrote: I went to options and turned on formulas. All cells are blank to the right of columns A-C in the blank row until data is entered in colums A-C of that row. Once the data is entered the formulas appear. -- Regards Mck "JLGWhiz" wrote: But when you enter the data in columns A-C the formulas appear in the cells for the new row of data. The formulas do not have to be in A-C for there to be a reaction when you put something in those columns. I think you answered your own question with the statement above. The formulas are in all the cells to the right of Col A-C. Something like IF(a0 And b0 And c0, c/(b+c), "") which would give the percentage of wins to games played. "Mick" wrote: About a month ago I wrote a short set of formulas to show a table of win/losses and % won per session and running totals. Table below: A B C D E F G H I J Running Total Session Wins to Reach Losses Wins % Total % 70% 71% 75% 8/18/2007 575 1329 69.80% 1342 1408 1725 9/1/2007 615 1415 69.70% 126 68.25% 1435 1506 1845 9/2/2007 627 1439 69.65% 36 66.67% 1463 1535 1881 9/3/2007 630 1451 69.73% 15 80.00% 1470 1542 1890 9/16/2007 642 1493 69.93% 54 77.78% 1498 1572 1926 I though I had done it with formulas in cells. The idea was to enter data in Columns A B C then the formulas would figure out the other columns for that row. When I went back to update the formulas, I can't find them in the cells or as a macro for a sheet change. I didn't remember them being set up with a change sheet macro and or hidden some way. When yo look at the sheet, the cells and formulas bars are blank. But when you enter the data in columns A-C the formulas appear in the cells for the new row of data. When I went to VB there are no macros that I can find that are entering these formulas. How can I find these formulas to make changes? Regards Mck |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hidden formulas
Just thought of one more. Select one of the cells where the formulas show up
after you make an entry. Then click FormatCellsProtection and see if the Hidden box is checked. If it is, then uncheck it and see if the formula then shows in the formula bar for that cell. If so, that's the answer. "Mick" wrote: I went to options and turned on formulas. All cells are blank to the right of columns A-C in the blank row until data is entered in colums A-C of that row. Once the data is entered the formulas appear. -- Regards Mck "JLGWhiz" wrote: But when you enter the data in columns A-C the formulas appear in the cells for the new row of data. The formulas do not have to be in A-C for there to be a reaction when you put something in those columns. I think you answered your own question with the statement above. The formulas are in all the cells to the right of Col A-C. Something like IF(a0 And b0 And c0, c/(b+c), "") which would give the percentage of wins to games played. "Mick" wrote: About a month ago I wrote a short set of formulas to show a table of win/losses and % won per session and running totals. Table below: A B C D E F G H I J Running Total Session Wins to Reach Losses Wins % Total % 70% 71% 75% 8/18/2007 575 1329 69.80% 1342 1408 1725 9/1/2007 615 1415 69.70% 126 68.25% 1435 1506 1845 9/2/2007 627 1439 69.65% 36 66.67% 1463 1535 1881 9/3/2007 630 1451 69.73% 15 80.00% 1470 1542 1890 9/16/2007 642 1493 69.93% 54 77.78% 1498 1572 1926 I though I had done it with formulas in cells. The idea was to enter data in Columns A B C then the formulas would figure out the other columns for that row. When I went back to update the formulas, I can't find them in the cells or as a macro for a sheet change. I didn't remember them being set up with a change sheet macro and or hidden some way. When yo look at the sheet, the cells and formulas bars are blank. But when you enter the data in columns A-C the formulas appear in the cells for the new row of data. When I went to VB there are no macros that I can find that are entering these formulas. How can I find these formulas to make changes? Regards Mck |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hidden formulas
I have a personal.xls with macros. I closed it and then checked to see if
the formulas would show up after the data is entered, and they did. I checked the code for the sheet and no macros were present. I also set macro security high and the workbook would open without any question or indication of macros. You can see the formulas after data is entered on a blank row. If you skip a blank row no response when the data is entered. Delete the entered data and fill in the previous blank row entry data and the formulas show up. -- Regards Mck "JLGWhiz" wrote: Just thought of one more. Select one of the cells where the formulas show up after you make an entry. Then click FormatCellsProtection and see if the Hidden box is checked. If it is, then uncheck it and see if the formula then shows in the formula bar for that cell. If so, that's the answer. "Mick" wrote: I went to options and turned on formulas. All cells are blank to the right of columns A-C in the blank row until data is entered in colums A-C of that row. Once the data is entered the formulas appear. -- Regards Mck "JLGWhiz" wrote: But when you enter the data in columns A-C the formulas appear in the cells for the new row of data. The formulas do not have to be in A-C for there to be a reaction when you put something in those columns. I think you answered your own question with the statement above. The formulas are in all the cells to the right of Col A-C. Something like IF(a0 And b0 And c0, c/(b+c), "") which would give the percentage of wins to games played. "Mick" wrote: About a month ago I wrote a short set of formulas to show a table of win/losses and % won per session and running totals. Table below: A B C D E F G H I J Running Total Session Wins to Reach Losses Wins % Total % 70% 71% 75% 8/18/2007 575 1329 69.80% 1342 1408 1725 9/1/2007 615 1415 69.70% 126 68.25% 1435 1506 1845 9/2/2007 627 1439 69.65% 36 66.67% 1463 1535 1881 9/3/2007 630 1451 69.73% 15 80.00% 1470 1542 1890 9/16/2007 642 1493 69.93% 54 77.78% 1498 1572 1926 I though I had done it with formulas in cells. The idea was to enter data in Columns A B C then the formulas would figure out the other columns for that row. When I went back to update the formulas, I can't find them in the cells or as a macro for a sheet change. I didn't remember them being set up with a change sheet macro and or hidden some way. When yo look at the sheet, the cells and formulas bars are blank. But when you enter the data in columns A-C the formulas appear in the cells for the new row of data. When I went to VB there are no macros that I can find that are entering these formulas. How can I find these formulas to make changes? Regards Mck |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying worksheets with formulas in hidden columns | Excel Worksheet Functions | |||
Excluding formulas in hidden cells? | Excel Worksheet Functions | |||
How do I avoid referencing hidden values in formulas like OFFSET? | Excel Worksheet Functions | |||
Hidden rows unhide themselves when formulas are written | Excel Discussion (Misc queries) | |||
Protection UserInterfaceOnly reading Hidden Formulas | Excel Programming |