| Home |
| Search |
| Today's Posts |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
stop excel automatically changing formulas
Thanks guys works a treat
"JLatham" wrote: I recommend using John McGimpsey's "non-array entered" method. You may be getting the #REF! error if you've entered the formula in a workbook that doesn't have a worksheet named Engineers. As for the braces, they are put into a formula like that by Excel when you enter the formula as an array formula. You do that by terminating the formula with [ctrl]+[Shift]+[Enter] instead of just plain [Enter]. Also, if you ever edit an array formula, you must again use [ctrl]+[Shift]+[Enter] to keep it as an array formula when you finish the edit. "sollidamra" wrote: i have a summary worksheet which looks up stock from other worksheets and totals them for me. The problem is if i insert or delete a row into my stock excel automatically adjusts the formula range up or down. my formula:- {=SUM((Engineers!B2:B500="Cisco AP")*(Engineers!E2:E500))} this looks in "Engineers" worksheet, finds all occureneces of Cisco AP's in column B and adds up the quantities which are in column E. If I use a piece of stock and delete the row excel changes the ranges to B2:B499 - E2:E499, what i want is for it to stay permanently at B2:B500 - E2:E500 I was told i may need to use the INDIRECT function but it did not work although i may have applied it incorrectly, this is what i did {=SUM((INDIRECT(Engineers!B2:B500="Cisco AP")*(Engineers!E2:E500)))} to be honest not sure if brackets are in right place, this returns a REF! error. please help |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel - stop automatically changing mixture of uppercase and lowercase to propercase | Excel Worksheet Functions | |||
| stop excel changing formulas | Excel Discussion (Misc queries) | |||
| How do I stop Excel from automatically changing font size? | Excel Discussion (Misc queries) | |||
| Need to stop formuls from automatically changing | Excel Discussion (Misc queries) | |||
| How do I stop excel automatically changing my date to 2005? | Excel Discussion (Misc queries) | |||
Threaded Mode
