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????? |
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????? |
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????? |
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????? |
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????? |
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????? |
All times are GMT +1. The time now is 07:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com