Excel Questions

[Maiden1]

If you take a copy of the file then delete 1 tab save it and see the size then delete a second tab until you see which tab is causing most of the memory.  Then try to narrow it down further, if need be create a new worksheet and copy the information from the worksheet which is causing the huge memory then delete the original.  Something may be happening behind the scenes you are not aware off.  It may be easiest just to copy all the data to a new spreadsheet and save as sometimes as over time memory can build up if you make changes to the sheet a lot.

[Declan]

Jaysus Billy - Sure there are no hidden columns or rows in there?

[Billys Boots]

I have a feeling it's been 'converted' between Excel 2007 and Excel 2003 a few times - I might copy everything to a blank sheet and start again. 

[Maiden1]

Also if you highlight and format a column the whole way to the bottom (all 65000)  e.g.  make all the cells date format dd-mmm-yyyy even if you only put a date in the first 5 rows each row the whole way to the bottom will take up memory.

If you go to the data below the cells you are using then highlight the rows right to the bottom then right click and delete this will delete all the formatting in the blank cells and you will see your memory coming away down.

[Declan]

If you go to the data below the cells you are using then highlight the rows right to the bottom then right click and delete this will delete all the formatting in the blank cells and you will see your memory coming away down.

Didn't know that one Maiden - Nice one!

[Billys Boots]

Nah, no dates in this data - but thanks, I didn't know that.

[Billys Boots]

For those small few who might be interested, I started a new sheet and cut and pasted everything (within the print area)in the 'big' sheet (without formatting) and re-formatted everything.  The file size reduced from 8.4MB to 46KB.  I tried to work out by gradually doing everything and saving etc. and what seemed to have been the problem was that the background colour in one of the blank sheets had been changed from white to grey (on the whole page).  Mad.

[Never beat the deeler]

I've got a fairly complex spreadsheet and trying to count certain things....
If column B contains certain txt, I want to count the no of values in Col X minus the no of cells containing certain words.

I've tried.....

=IF($B:$B="Sample text",(COUNTA(QM$14:QM$213)-COUNTIF(QM$14:QM$213,"FOA")-COUNTIF(QM$14:QM$213,"FOP")-COUNTIF(QM$14:QM$213,"AL")-COUNTIF(QM$14:QM$213,"CL")-COUNTIF(QM$14:QM$213,"S")-COUNTIF(QM$14:QM$213,"T")),"")

[DirtyDozen12]

Im making a guest list and in each cell im putting the name of the person to be invited.  I will have different columns with different peoples names.  Is there any way I can keep the name of the person but add a value?  Reason is so when ive all the guests entered on the spreadsheet, Excel will calculate how many people ive asked so each person will only need a value of 1?

Im an Excel buffon and have no idea how to do this 

[DirtyDozen12]

The formula you need is =counta(RANGE) - this will give you the number of non-empty cells.

Thanks!!

[Orior]

=IF(O24<100,"10",IF(O24<200,"20",IF(O24<300,"30",IF(O24<400,"40", "50"))))

[Bensars]

=IF(O24<100,"10",IF(O24<200,"20",IF(O24<300,"30",IF(O24<400,"40", "50"))))

Good man orior.  Saved me a headache

[gerrykeegan]

I want to give some a spreadshhet to input values into A1 and B1. C1 = A1 x B1
i want to protect C1 so no one can alter it. I have tried to use cell protect but it then restricts input to A1 and B1 which defeats the purpose of my exercise. Any suggestions?

[Mario]

I want to give some a spreadshhet to input values into A1 and B1. C1 = A1 x B1
i want to protect C1 so no one can alter it. I have tried to use cell protect but it then restricts input to A1 and B1 which defeats the purpose of my exercise. Any suggestions?

1. Protect the workbook in tools > protection
2. Highlight the cells you want people to be able to change in your case A1 and B1, click format > cells > go to protection tab > make sure the locked cell box isnt ticked, do the same for cell c1, this time make sure it is ticked
3. now protect the sheet in tools >protection

[gerrykeegan]

gentleman, thanks