Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula Changes when data entered in referenced range
I have 5 formulas in a worksheet that refer to a range. When data is entered
in the range, the formulas adjust to exclude the cells with data. Range B5:B211 When data is entered in B5, range in the formulas becomes B6:B212. The workbook has nearly 200 worksheets with the same formulas. I'm working in Excel 2003. Does anybody have any idea what's going on????? |
#2
|
|||
|
|||
Might help if you include the formulae that you are using.
Regards Trevor "mac849" wrote in message ... I have 5 formulas in a worksheet that refer to a range. When data is entered in the range, the formulas adjust to exclude the cells with data. Range B5:B211 When data is entered in B5, range in the formulas becomes B6:B212. The workbook has nearly 200 worksheets with the same formulas. I'm working in Excel 2003. Does anybody have any idea what's going on????? |
#3
|
|||
|
|||
Sorry, Thats =Sum(B5:B211)
"Trevor Shuttleworth" wrote: Might help if you include the formulae that you are using. Regards Trevor "mac849" wrote in message ... I have 5 formulas in a worksheet that refer to a range. When data is entered in the range, the formulas adjust to exclude the cells with data. Range B5:B211 When data is entered in B5, range in the formulas becomes B6:B212. The workbook has nearly 200 worksheets with the same formulas. I'm working in Excel 2003. Does anybody have any idea what's going on????? |
#4
|
|||
|
|||
Sure =Sum(B5:B212). By the way, this is a shared workbook.
"Trevor Shuttleworth" wrote: Might help if you include the formulae that you are using. Regards Trevor "mac849" wrote in message ... I have 5 formulas in a worksheet that refer to a range. When data is entered in the range, the formulas adjust to exclude the cells with data. Range B5:B211 When data is entered in B5, range in the formulas becomes B6:B212. The workbook has nearly 200 worksheets with the same formulas. I'm working in Excel 2003. Does anybody have any idea what's going on????? |
#5
|
|||
|
|||
I'm on an XL97 machine right now, and I can't remember exactly which
settings might be causing this. From my XP machine at the plant, I remember something in <Tools <Options Maybe <Calculations or maybe <Edit, Where you *uncheck* a setting that mentions extending formulas and formats, or something similar to that. If you can find what I'm talking about, it might be worth a try. Nothing ventured, nothing gained.<g -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "mac849" wrote in message ... Sure =Sum(B5:B212). By the way, this is a shared workbook. "Trevor Shuttleworth" wrote: Might help if you include the formulae that you are using. Regards Trevor "mac849" wrote in message ... I have 5 formulas in a worksheet that refer to a range. When data is entered in the range, the formulas adjust to exclude the cells with data. Range B5:B211 When data is entered in B5, range in the formulas becomes B6:B212. The workbook has nearly 200 worksheets with the same formulas. I'm working in Excel 2003. Does anybody have any idea what's going on????? |
#6
|
|||
|
|||
The option I think you're referring to is on Options <Edit. It was ON, but
I don't think that is the problem. Entering data didn't affect the formulas when Sharing was turned off. I found a work around by selecting the entire column (B:B) in my formula, but it would be nice to find out if this is a bug of some kind? Microsoft??? "Ragdyer" wrote: I'm on an XL97 machine right now, and I can't remember exactly which settings might be causing this. From my XP machine at the plant, I remember something in <Tools <Options Maybe <Calculations or maybe <Edit, Where you *uncheck* a setting that mentions extending formulas and formats, or something similar to that. If you can find what I'm talking about, it might be worth a try. Nothing ventured, nothing gained.<g -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "mac849" wrote in message ... Sure =Sum(B5:B212). By the way, this is a shared workbook. "Trevor Shuttleworth" wrote: Might help if you include the formulae that you are using. Regards Trevor "mac849" wrote in message ... I have 5 formulas in a worksheet that refer to a range. When data is entered in the range, the formulas adjust to exclude the cells with data. Range B5:B211 When data is entered in B5, range in the formulas becomes B6:B212. The workbook has nearly 200 worksheets with the same formulas. I'm working in Excel 2003. Does anybody have any idea what's going on????? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a way to toggle cells between display of referenced data. | Excel Discussion (Misc queries) | |||
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) | |||
Need formula to check values of data in several cells as criteria | Excel Worksheet Functions | |||
What function or formula do I use to calculate ROI with this data? | Excel Worksheet Functions | |||
Importing Data Into Formula Based Cells | Excel Worksheet Functions |