Excel questions

[Toolin Around]

Hi all

Hoping someone can help me. I've looked through about 1500 pages of excel text books but can't find the answers I want.

I've been trying to put together a workbook of information that consists of four work sheets. The first work sheet is the source sheet and the rest are dependent. Each of the other sheets only displays certain data making it easier to analyze the information.

Problem is, in the dependent sheets all the empty dependent cells have a 0 in them. Is there anyway I can make excel not enter a 0 in a empty cell and just leave it blank.

Also It would be a better there wasn't a source sheet and a bunch of dependent sheets. Is there a way to make it so I can enter information in any of the sheets and have it display on all the other sheets that are linked to that cell.

TIA Mat

[Ron Dunn]

*lol* ... I never expected to be answering computer questions in a woodwork forum ... but it sounds like you've got the cells formatted as numbers ... and to show them as blank, change the cell format to something else (ie, text or general); or use the custom format with a "#" symbol.

[Gra]

what you need is an if statement, that looks at the source cell and if it is blank returns "" (blank) else returns the value. post the formula that puts the 0 in the cell and I will rewrite it to suit

[munruben]

Play around with this and it may give you the idea how its done.type this formula below in A1 =IF(A5=0," ",A5) and then type in a number in A5. Any number other than zero will return the true value of A5 in A1
If the value in A5 is "0" then it will return a blank cell in A1

Hope this might help you figure it out.

[powderpost]

Try this...
Highlight cells to not show a zero
Go to 'tools', 'options', 'view', then remove the check mark in the 'zero value' box. That works for me and removes a zero if the product of a formula is zero.
Jim

[Toolin Around]

*lol* ... I never expected to be answering computer questions in a woodwork forum ... but it sounds like you've got the cells formatted as numbers ... and to show them as blank, change the cell format to something else (ie, text or general); or use the custom format with a "#" symbol.


Well it's sort of for woodwork and a bit of uni. I'm in a business degree program and I'm playing with developing an inventory workbook for tracking product as it moves along the assembly line using the cabinet shop where I work as a test site.

The more I dig into excel the more I'm amazed at what you can do in it.


Thank you all for your input - it was much appreciated. I was able to 'un' display 0 with Jim's suggestion.

I'm really hoping someone will know how to make changes in any of the worksheets and have those changes show up on all the other linked work sheets. It's one of those things where you don't know the proper terminology and it makes searching for the answer a problem

[TermiMonster]

Mat,
I can't answer your question, but if you are familiar with the use of newsgroups, you can go to the alt.comp.microsoft.excel
group, and get all your questions answered by experts. Like this forum, the question has probably been answered before, so do a search first, or you may cop a roasting. Good luck
TM

[powderpost]

Can't believe I am actually helping someone with a computer problem, I am NOT an expert computer driver. If I read the problem right, try this. Enter thr data on a source sheet. Go to the dependant sheet and click on the cell where the data is to go. In that cell press the '=' key', then go to the data sheet source cell and press enter. It may be a good idea to precede the source cell address in the receiving cell with a dollar sign eg $a$12. When the source cell is altered the data will automatically go to the dependant cell. Geez that sounds complicated, but I have twelve sheets for monthly household expenses that are connected this way, and the current month will feed through to the next. Hope it helps...
Jim

[Wongo]

Matt, you need to do this

=IF(TRIM(A1)<>"",A1,"")

if A1 is blank then show blank, otherwise show its value (number or text)

Remember the size of the file will increase because of the formulas.

[horse]

‘The more I dig into excel the more I'm amazed at what you can do in it.’

Yeah, me too...

"We all learned how to multiply with pencil and paper, even great big numbers and decimals. But when it comes to something important like a blueprint or a scientific formula we reach for a calculator - or a spreadsheet. That's much more reliable, right? Well, not if the spreadsheet is Excel 2007. Over the weekend a member of the microsoft.public.excel newsgroup revealed that Excel 2007 thinks that 850*77.1 is 100,000," Neil Rubenking reports for AppScout.

http://www.appscout.com/2007/09/excel_cant_multiply.php

[Toolin Around]

Thx for the help guys. I've realised that you can't have one work sheet updating another and vise versa otherwise you get into a circular problem

[toecutter]

This is a excellent forum for excel (http://www.ozgrid.com/forum/forumdisplay.php?f=8) help.
Owned/run by a WA guy called Dave Hawley.

tell them toecutter sent you:)

[Wongo]

Yeah but can these people cut a dovetail? :p

[Rookie]

"We all learned how to multiply with pencil and paper, even great big numbers and decimals. But when it comes to something important like a blueprint or a scientific formula we reach for a calculator - or a spreadsheet. That's much more reliable, right? Well, not if the spreadsheet is Excel 2007. Over the weekend a member of the microsoft.public.excel newsgroup revealed that Excel 2007 thinks that 850*77.1 is 100,000," Neil Rubenking reports for AppScout.

http://www.appscout.com/2007/09/excel_cant_multiply.php

Many years ago, more than I care to remember, Lotus 123 which was (or still is???) also a spreadsheet program, found a bug in the floating point calculator of the Pentium chip when Intel released it. Up until that point the Intel chips were all x86 chips. 286, 386, 486. This bug led to the rather tongue in cheek comment that the reason Intel called it's new chip the Pentium, and not the 586, was because when they added 100 to 486 they got 587.87695.

[The Big O]

Trying to put links in worksheets. Just copy the source, click on the square that you want it to go to, go to Edit, paste special. paste link. Every time you change the source, the link changes. Simple.

[toecutter]

Heres a rafter calculator i made in excel

You will need to have your excel macro securities set to minimum to use it

[X-Man]

A very good resource for excel [and MS Acess as well] is www.mrexcel.com
click on the message boards link which takes you to the forums. All sorts of rocket scientists and mathematic guru's keep an eye on this site and if you ask a question they try and beat each other to answer first ...like their own little game of maths sport! so you get your answers pretty quick. They even have a ability to create a view of how an excel page will look when what your problem is solved and you can just click on a button and import it into your spread sheet.
honestly these guys are excel freaks check it out.

[Pops]

Hi Tooling Around,

Thanks for posting this question and thanks to all those that have answered. I use Excel a bit and always have to spend hours reading useless Help pages before giving up.

So thanks for the links chaps. It will make my life easier and reduce the amount of hair I tear out when using Excel. (almost none left now anyway).

Cheers
Pops

[toecutter]

A very good resource for excel [and MS Acess as well] is www.mrexcel.com (http://www.mrexcel.com)

honestly these guys are excel freaks check it out.

And here www.ozgrid.com (http://www.ozgrid.com/forum/forumdisplay.php?f=8)
I pefrer this one cause its run by a Ausie