G
Guest
Hi
I've got a big workbook with one sheet for every crane we make, on these sheets are the parts and assemblies used to make the crane. Because some cranes share the same parts, I've created another worksheet entitled "Shared" that lists all the shared parts, and has a series of columns (one for each crane) that I can enter a number in to tell me how many are on which cranes. I've written a macro that puts the formula =Shared!A1, or =Shared!H47 into the right columns just after that last row on the crane sheets, so that the shared parts all appear to be on the appropriate sheets, but I can update the costing information on all of them just by changing the info and the one shared sheet
My problem is that the list is not complete, and that some cells in the shared sheet may be empty at at times but not at others (like, "Last Orderd", or "Special Info"). Some parts have special info, but most don't. The annoying thing is that if the referenced cell is empty, the cell that references it evalutes to 0. This means I've got a ton of cells that tell my boss we last ordered Part X on 0-Jan-0 (because of the date formatting), and millions of unsightly 0's in the special info column which should really only be text. I could change the macro I wrote to only enter the =Shared!X# formula if X# <> "", and in fact I already did, and it works, but this means now have to run the macro every time we update special info, or fill in a blank in any cell on the shared worksheet just to be sure I've got the info in the right spot. It takes 5 minutes to run and isn't very efficent to do this
Does anyone have any suggestions? Is it possible to stop it displaying the 0's, and just have the formula evaluate to a blank cell
Thanks for all your help.
I've got a big workbook with one sheet for every crane we make, on these sheets are the parts and assemblies used to make the crane. Because some cranes share the same parts, I've created another worksheet entitled "Shared" that lists all the shared parts, and has a series of columns (one for each crane) that I can enter a number in to tell me how many are on which cranes. I've written a macro that puts the formula =Shared!A1, or =Shared!H47 into the right columns just after that last row on the crane sheets, so that the shared parts all appear to be on the appropriate sheets, but I can update the costing information on all of them just by changing the info and the one shared sheet
My problem is that the list is not complete, and that some cells in the shared sheet may be empty at at times but not at others (like, "Last Orderd", or "Special Info"). Some parts have special info, but most don't. The annoying thing is that if the referenced cell is empty, the cell that references it evalutes to 0. This means I've got a ton of cells that tell my boss we last ordered Part X on 0-Jan-0 (because of the date formatting), and millions of unsightly 0's in the special info column which should really only be text. I could change the macro I wrote to only enter the =Shared!X# formula if X# <> "", and in fact I already did, and it works, but this means now have to run the macro every time we update special info, or fill in a blank in any cell on the shared worksheet just to be sure I've got the info in the right spot. It takes 5 minutes to run and isn't very efficent to do this
Does anyone have any suggestions? Is it possible to stop it displaying the 0's, and just have the formula evaluate to a blank cell
Thanks for all your help.