Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Dragging a Formula & Basic Setting Changes
Hi, I have two issues that I've run into with Excel that have me stumped.
1) I am trying to mirror a row of values (via formulas) from one sheet to another. I do not want to simply cut and paste b/c I am udating the sheets. Typically I copy one cell and then drag it across the row to fill in the remaining cells. Right now, however, the worksheet is simply repeating the same value instead of inputting 'value + 1 (column)'. Ex. I want to have a mirror of cells B2-B6 so I input on the new sheet, C5 = 'Orig. Sheet'B2. I then drag the corner of C5 across the row to try to fill in with B3, B4, B5, B6 from the original sheet....Instead what I'm getting is B2, B2, B2, B2..... Any idea what setting might have changed or how to get it back to the way I would prefer? That leads me to my second question which is more of a general Excel question... 2) Excel seems to sometimes change settings when I open and close it. For instance, I always prefer to "edit directly in cell", however, many times I will have to click this check box each time I open a file (even the same one that's been saved the other way). I am confused as to why it keeps defaulting back to a setting and forces me to manually change it. Is it possible that there is a bug in my version of the program? Thanks for any ideas. |
#2
|
|||
|
|||
I dont really understand what is happening in your first issue.
is the results of the equations which are wrong? are equations themselves wrong? in the second issue it is probably that the first progeam you open each day has different defaults. excel uses as its defaults the settings of the first file opened. You can tell Excel to open an initial file with the defaults you want if you just open Excel. see help on "customise how excel starts" "chande00" wrote: Hi, I have two issues that I've run into with Excel that have me stumped. 1) I am trying to mirror a row of values (via formulas) from one sheet to another. I do not want to simply cut and paste b/c I am udating the sheets. Typically I copy one cell and then drag it across the row to fill in the remaining cells. Right now, however, the worksheet is simply repeating the same value instead of inputting 'value + 1 (column)'. Ex. I want to have a mirror of cells B2-B6 so I input on the new sheet, C5 = 'Orig. Sheet'B2. I then drag the corner of C5 across the row to try to fill in with B3, B4, B5, B6 from the original sheet....Instead what I'm getting is B2, B2, B2, B2..... Any idea what setting might have changed or how to get it back to the way I would prefer? That leads me to my second question which is more of a general Excel question... 2) Excel seems to sometimes change settings when I open and close it. For instance, I always prefer to "edit directly in cell", however, many times I will have to click this check box each time I open a file (even the same one that's been saved the other way). I am confused as to why it keeps defaulting back to a setting and forces me to manually change it. Is it possible that there is a bug in my version of the program? Thanks for any ideas. |
#3
|
|||
|
|||
In the first scenario, dragging the lower right hand corner of the box just
takes the exact info that I have in that cell and copies it to all of the cells that I drag it to. What I am trying to do is have the cells correspond with the info I am trying to replicate. I have listed a more detailed description below. Say I have cells A1 A2 A3 A4 A5 A6 on what we'll call sheet 1 I want the values of A1-A6 on a new sheet in row 'C'. So in C1 I input ='sheet1'!A1 .. Then typically I would drag the bottom corner of the cell and it would fill in the values as listed: C2=A2 C3=A3 C4=A4 C5=A5 C6=A6 . Now, however, it is just filling like this: C2=A1 C3=A1 C4=A1 C5=A1 C6=A1 . I hope that this helps explin the issue a little better. Thanks "bj" wrote: I dont really understand what is happening in your first issue. is the results of the equations which are wrong? are equations themselves wrong? in the second issue it is probably that the first progeam you open each day has different defaults. excel uses as its defaults the settings of the first file opened. You can tell Excel to open an initial file with the defaults you want if you just open Excel. see help on "customise how excel starts" "chande00" wrote: Hi, I have two issues that I've run into with Excel that have me stumped. 1) I am trying to mirror a row of values (via formulas) from one sheet to another. I do not want to simply cut and paste b/c I am udating the sheets. Typically I copy one cell and then drag it across the row to fill in the remaining cells. Right now, however, the worksheet is simply repeating the same value instead of inputting 'value + 1 (column)'. Ex. I want to have a mirror of cells B2-B6 so I input on the new sheet, C5 = 'Orig. Sheet'B2. I then drag the corner of C5 across the row to try to fill in with B3, B4, B5, B6 from the original sheet....Instead what I'm getting is B2, B2, B2, B2..... Any idea what setting might have changed or how to get it back to the way I would prefer? That leads me to my second question which is more of a general Excel question... 2) Excel seems to sometimes change settings when I open and close it. For instance, I always prefer to "edit directly in cell", however, many times I will have to click this check box each time I open a file (even the same one that's been saved the other way). I am confused as to why it keeps defaulting back to a setting and forces me to manually change it. Is it possible that there is a bug in my version of the program? Thanks for any ideas. |
#4
|
|||
|
|||
II sent a response earlier but don't see it now.
what version of Excel do you have? in 2002 when you drag copy there is a little clipboard looking icon which lets you select some of the character for what you are copying. Also are you getting values or just the equations. the second sheet might be formated for text only and converting the equation to text "chande00" wrote: In the first scenario, dragging the lower right hand corner of the box just takes the exact info that I have in that cell and copies it to all of the cells that I drag it to. What I am trying to do is have the cells correspond with the info I am trying to replicate. I have listed a more detailed description below. Say I have cells A1 A2 A3 A4 A5 A6 on what we'll call sheet 1 I want the values of A1-A6 on a new sheet in row 'C'. So in C1 I input ='sheet1'!A1 . Then typically I would drag the bottom corner of the cell and it would fill in the values as listed: C2=A2 C3=A3 C4=A4 C5=A5 C6=A6 . Now, however, it is just filling like this: C2=A1 C3=A1 C4=A1 C5=A1 C6=A1 . I hope that this helps explin the issue a little better. Thanks "bj" wrote: I dont really understand what is happening in your first issue. is the results of the equations which are wrong? are equations themselves wrong? in the second issue it is probably that the first progeam you open each day has different defaults. excel uses as its defaults the settings of the first file opened. You can tell Excel to open an initial file with the defaults you want if you just open Excel. see help on "customise how excel starts" "chande00" wrote: Hi, I have two issues that I've run into with Excel that have me stumped. 1) I am trying to mirror a row of values (via formulas) from one sheet to another. I do not want to simply cut and paste b/c I am udating the sheets. Typically I copy one cell and then drag it across the row to fill in the remaining cells. Right now, however, the worksheet is simply repeating the same value instead of inputting 'value + 1 (column)'. Ex. I want to have a mirror of cells B2-B6 so I input on the new sheet, C5 = 'Orig. Sheet'B2. I then drag the corner of C5 across the row to try to fill in with B3, B4, B5, B6 from the original sheet....Instead what I'm getting is B2, B2, B2, B2..... Any idea what setting might have changed or how to get it back to the way I would prefer? That leads me to my second question which is more of a general Excel question... 2) Excel seems to sometimes change settings when I open and close it. For instance, I always prefer to "edit directly in cell", however, many times I will have to click this check box each time I open a file (even the same one that's been saved the other way). I am confused as to why it keeps defaulting back to a setting and forces me to manually change it. Is it possible that there is a bug in my version of the program? Thanks for any ideas. |
#5
|
|||
|
|||
Easy one first...
I think that this is one of those settings that's stored in the windows registry. If your changes aren't being saved, it may be worthwhile to talk to your IT folks to find out why you can't save to the registry. Maybe you're not an administrator for your pc (or you're logged on as a user who doesn't have those rights). But until you get it fixed, you could put a workbook in your XLStart folder. That means the workbook will open each time you start excel. The code could turn the setting off and close the workbook. Option Explicit Sub auto_open() Application.EditDirectlyInCell = True ThisWorkbook.Close savechanges:=False End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm The more difficult question... #1. Are you sure the formulas all look the same? Maybe the formulas are different, but the formulas have recalculated (yet). Tools|Options|Calculation tab. (Make sure Automatic is checked.) chande00 wrote: Hi, I have two issues that I've run into with Excel that have me stumped. 1) I am trying to mirror a row of values (via formulas) from one sheet to another. I do not want to simply cut and paste b/c I am udating the sheets. Typically I copy one cell and then drag it across the row to fill in the remaining cells. Right now, however, the worksheet is simply repeating the same value instead of inputting 'value + 1 (column)'. Ex. I want to have a mirror of cells B2-B6 so I input on the new sheet, C5 = 'Orig. Sheet'B2. I then drag the corner of C5 across the row to try to fill in with B3, B4, B5, B6 from the original sheet....Instead what I'm getting is B2, B2, B2, B2..... Any idea what setting might have changed or how to get it back to the way I would prefer? That leads me to my second question which is more of a general Excel question... 2) Excel seems to sometimes change settings when I open and close it. For instance, I always prefer to "edit directly in cell", however, many times I will have to click this check box each time I open a file (even the same one that's been saved the other way). I am confused as to why it keeps defaulting back to a setting and forces me to manually change it. Is it possible that there is a bug in my version of the program? Thanks for any ideas. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula shown as a formula rather than it's result | Excel Worksheet Functions | |||
Need help setting up a >< IF formula in Excel, | Excel Worksheet Functions | |||
Need help setting up a >< IF formula in Excel, | Excel Worksheet Functions | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions | |||
Formula Result Correct but value in the cell is wrong | Excel Worksheet Functions |