Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas referencing a spaces instead of number ?
Ive imported my Trial Balance into one tab. I want to analyse this information on other tabs in various ways and apply some simple sums to the values. But when I link these cells to my Trial Balance and there is no actual number I get an error if I try to apply a formula to it. It sees a blank as a space, not the absence of an actual value. How do I sum a range of these cells when some are spaces. Is there a formula that I can use to copy the entire TB and only copy the values, ignoring the spaces ? Then this can be the TB I analyse. -- Ainsley ------------------------------------------------------------------------ Ainsley's Profile: http://www.excelforum.com/member.php...o&userid=31960 View this thread: http://www.excelforum.com/showthread...hreadid=538042 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas referencing a spaces instead of number ?
You can use a formula like:
=sum(a1:b9) This will ignore cells that are empty and cells that have text in them. If you want to retrieve a value from a cell, you can use a formula like: =sheet2!a1 But this will show 0 if that cell is empty. =if(sheet2!a1="","",sheet2!a1) will make it look like the cell is blank if that other cell is empty. Ainsley wrote: Ive imported my Trial Balance into one tab. I want to analyse this information on other tabs in various ways and apply some simple sums to the values. But when I link these cells to my Trial Balance and there is no actual number I get an error if I try to apply a formula to it. It sees a blank as a space, not the absence of an actual value. How do I sum a range of these cells when some are spaces. Is there a formula that I can use to copy the entire TB and only copy the values, ignoring the spaces ? Then this can be the TB I analyse. -- Ainsley ------------------------------------------------------------------------ Ainsley's Profile: http://www.excelforum.com/member.php...o&userid=31960 View this thread: http://www.excelforum.com/showthread...hreadid=538042 -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas referencing a spaces instead of number ?
Ive tried those formulas which unfortunatly dont work. My Trial Balance has some numbers and some spaces in it. The spaces are causing problems as whatever formulas I use I get errors as I cant add up numbers and spaces. The spaces are caused by my accounting package displaying an abscence of a value as a space, as if you used your space bar to clear out a cell. How do I remove the "spaces" so there is nothing in the cell ? Regards, Ainsley -- Ainsley ------------------------------------------------------------------------ Ainsley's Profile: http://www.excelforum.com/member.php...o&userid=31960 View this thread: http://www.excelforum.com/showthread...hreadid=538042 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas referencing a spaces instead of number ?
=sum() will ignore the text values in that range.
But it sounds like your "numbers" aren't numbers. They are text masquerading as numbers. David McRitchie has a macro that cleans up this kind of stuff: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()" If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ainsley wrote: Ive tried those formulas which unfortunatly dont work. My Trial Balance has some numbers and some spaces in it. The spaces are causing problems as whatever formulas I use I get errors as I cant add up numbers and spaces. The spaces are caused by my accounting package displaying an abscence of a value as a space, as if you used your space bar to clear out a cell. How do I remove the "spaces" so there is nothing in the cell ? Regards, Ainsley -- Ainsley ------------------------------------------------------------------------ Ainsley's Profile: http://www.excelforum.com/member.php...o&userid=31960 View this thread: http://www.excelforum.com/showthread...hreadid=538042 -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas referencing a spaces instead of number ?
Ah, thats great. Its solved my problem by just using =sum(). Made my day, cheers. -- Ainsley ------------------------------------------------------------------------ Ainsley's Profile: http://www.excelforum.com/member.php...o&userid=31960 View this thread: http://www.excelforum.com/showthread...hreadid=538042 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
why the date convert to number when i choose formulas from options | Excel Discussion (Misc queries) | |||
Allow relative referencing for imbedded sheetnames in formulas | Excel Worksheet Functions | |||
COUNT THE NUMBER OF LETTERS INCLUDING SPACES IN A CELL? | Excel Worksheet Functions | |||
Changing a formulas result to be a static number | Excel Discussion (Misc queries) | |||
Equation Referencing Row Number Stored in Cell | Excel Discussion (Misc queries) |