Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with "blank" in formula
Hi,
I'm doing a formula that takes info from a worksheet in one workbook and copies it to another worksheet in another workbook. My formula looks like this: =('[9-07 Chart Audit Compliance CC.xls]CVT'!$C$19) and it works fine except for one thing. If the cell I'm taking the info from is empty, it puts 0.00% in the worksheet I'm copying to. Each cell is added and an average is taken and the 0.00% changes the average! What can I add to the formula to make the cell blank if the cell I'm copying is blank? Boy I hope this makes sense :) The workbook goes for a month at a time but we want to see the averages each week and the 0%'s make that number incorrect until you get to the end of the month and all the cells are filled. ALSO is there a way to make the formulas fill in correctly instead of having to enter each one one at a time? (I click in the cell, type =( then go to the cell I'm copying and click it, hit enter, and the formula for THAT cell goes in. If I drag it, it doesn't change like it does in a regular workbook where the info is in the same worksheet... (I know, that's two questions for the price of one :D ) thanks, Meenie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with "blank" in formula
=IF(A2="","",A2)
replace A2 with your data -- Regards, Peo Sjoblom "Meenie" wrote in message ... Hi, I'm doing a formula that takes info from a worksheet in one workbook and copies it to another worksheet in another workbook. My formula looks like this: =('[9-07 Chart Audit Compliance CC.xls]CVT'!$C$19) and it works fine except for one thing. If the cell I'm taking the info from is empty, it puts 0.00% in the worksheet I'm copying to. Each cell is added and an average is taken and the 0.00% changes the average! What can I add to the formula to make the cell blank if the cell I'm copying is blank? Boy I hope this makes sense :) The workbook goes for a month at a time but we want to see the averages each week and the 0%'s make that number incorrect until you get to the end of the month and all the cells are filled. ALSO is there a way to make the formulas fill in correctly instead of having to enter each one one at a time? (I click in the cell, type =( then go to the cell I'm copying and click it, hit enter, and the formula for THAT cell goes in. If I drag it, it doesn't change like it does in a regular workbook where the info is in the same worksheet... (I know, that's two questions for the price of one :D ) thanks, Meenie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with "blank" in formula
Oh I have one more comment on the forumla... The cells in the sheet that I/m
copying from are blank but on the sheet I'm copying to show up as 0.00%. Both are formated as percentage with 2 decimal places. Why don't they show up as a blank as they do on the cell they come from? hmmm "Meenie" wrote: Hi, I'm doing a formula that takes info from a worksheet in one workbook and copies it to another worksheet in another workbook. My formula looks like this: =('[9-07 Chart Audit Compliance CC.xls]CVT'!$C$19) and it works fine except for one thing. If the cell I'm taking the info from is empty, it puts 0.00% in the worksheet I'm copying to. Each cell is added and an average is taken and the 0.00% changes the average! What can I add to the formula to make the cell blank if the cell I'm copying is blank? Boy I hope this makes sense :) The workbook goes for a month at a time but we want to see the averages each week and the 0%'s make that number incorrect until you get to the end of the month and all the cells are filled. ALSO is there a way to make the formulas fill in correctly instead of having to enter each one one at a time? (I click in the cell, type =( then go to the cell I'm copying and click it, hit enter, and the formula for THAT cell goes in. If I drag it, it doesn't change like it does in a regular workbook where the info is in the same worksheet... (I know, that's two questions for the price of one :D ) thanks, Meenie |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with "blank" in formula
So are you saying my formula should be =IF('[9-07 Chart Audit Compliance
CC.xls]CVT'!$C$19=",",='[9-07 Chart Audit Compliance CC.xls]CVT'!$C$19)? Wow, how do you put all that in? Do you type =IF( then click the cell you're copying from, then type the ",", then click the cell you're copying from again?? yikes. that isn't right is it... gads. "Peo Sjoblom" wrote: =IF(A2="","",A2) replace A2 with your data -- Regards, Peo Sjoblom "Meenie" wrote in message ... Hi, I'm doing a formula that takes info from a worksheet in one workbook and copies it to another worksheet in another workbook. My formula looks like this: =('[9-07 Chart Audit Compliance CC.xls]CVT'!$C$19) and it works fine except for one thing. If the cell I'm taking the info from is empty, it puts 0.00% in the worksheet I'm copying to. Each cell is added and an average is taken and the 0.00% changes the average! What can I add to the formula to make the cell blank if the cell I'm copying is blank? Boy I hope this makes sense :) The workbook goes for a month at a time but we want to see the averages each week and the 0%'s make that number incorrect until you get to the end of the month and all the cells are filled. ALSO is there a way to make the formulas fill in correctly instead of having to enter each one one at a time? (I click in the cell, type =( then go to the cell I'm copying and click it, hit enter, and the formula for THAT cell goes in. If I drag it, it doesn't change like it does in a regular workbook where the info is in the same worksheet... (I know, that's two questions for the price of one :D ) thanks, Meenie |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with "blank" in formula
Read Peo's post again. You need a *pair* of quote marks "" to surround your
blank text string. One " will not do. If you've already got your formula saying =('[9-07 Chart Audit Compliance CC.xls]CVT'!$C$19) you can add the extra bits around it, and copy and paste the bits you need twice, or (as you say) you can just click in the cell to get the reference into your formula. -- David Biddulph "Meenie" wrote in message ... So are you saying my formula should be =IF('[9-07 Chart Audit Compliance CC.xls]CVT'!$C$19=",",='[9-07 Chart Audit Compliance CC.xls]CVT'!$C$19)? Wow, how do you put all that in? Do you type =IF( then click the cell you're copying from, then type the ",", then click the cell you're copying from again?? yikes. that isn't right is it... gads. "Peo Sjoblom" wrote: =IF(A2="","",A2) replace A2 with your data -- Regards, Peo Sjoblom "Meenie" wrote in message ... Hi, I'm doing a formula that takes info from a worksheet in one workbook and copies it to another worksheet in another workbook. My formula looks like this: =('[9-07 Chart Audit Compliance CC.xls]CVT'!$C$19) and it works fine except for one thing. If the cell I'm taking the info from is empty, it puts 0.00% in the worksheet I'm copying to. Each cell is added and an average is taken and the 0.00% changes the average! What can I add to the formula to make the cell blank if the cell I'm copying is blank? Boy I hope this makes sense :) The workbook goes for a month at a time but we want to see the averages each week and the 0%'s make that number incorrect until you get to the end of the month and all the cells are filled. ALSO is there a way to make the formulas fill in correctly instead of having to enter each one one at a time? (I click in the cell, type =( then go to the cell I'm copying and click it, hit enter, and the formula for THAT cell goes in. If I drag it, it doesn't change like it does in a regular workbook where the info is in the same worksheet... (I know, that's two questions for the price of one :D ) thanks, Meenie |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with "blank" in formula
Thanks David,
I have the formula is just a few cells, but this is a HUGE workbook with several sheets. How do I get all this on the all the pages without going one cell at a time? You know when you have a formula on one worksheet, you can drag it and the ref. change accordingly, but that doesn't seem to work when I copy things from a different workbook. As you can see from my questions.. I'm no Excel MVP :D.. there's probably a much easier way to do this than what I'm doing (at least that's what I'm hoping) I tried to follow Help but when I choose a range of cells rather than one cell, it only copies the first cell. thanks :) "David Biddulph" wrote: Read Peo's post again. You need a *pair* of quote marks "" to surround your blank text string. One " will not do. If you've already got your formula saying =('[9-07 Chart Audit Compliance CC.xls]CVT'!$C$19) you can add the extra bits around it, and copy and paste the bits you need twice, or (as you say) you can just click in the cell to get the reference into your formula. -- David Biddulph "Meenie" wrote in message ... So are you saying my formula should be =IF('[9-07 Chart Audit Compliance CC.xls]CVT'!$C$19=",",='[9-07 Chart Audit Compliance CC.xls]CVT'!$C$19)? Wow, how do you put all that in? Do you type =IF( then click the cell you're copying from, then type the ",", then click the cell you're copying from again?? yikes. that isn't right is it... gads. "Peo Sjoblom" wrote: =IF(A2="","",A2) replace A2 with your data -- Regards, Peo Sjoblom "Meenie" wrote in message ... Hi, I'm doing a formula that takes info from a worksheet in one workbook and copies it to another worksheet in another workbook. My formula looks like this: =('[9-07 Chart Audit Compliance CC.xls]CVT'!$C$19) and it works fine except for one thing. If the cell I'm taking the info from is empty, it puts 0.00% in the worksheet I'm copying to. Each cell is added and an average is taken and the 0.00% changes the average! What can I add to the formula to make the cell blank if the cell I'm copying is blank? Boy I hope this makes sense :) The workbook goes for a month at a time but we want to see the averages each week and the 0%'s make that number incorrect until you get to the end of the month and all the cells are filled. ALSO is there a way to make the formulas fill in correctly instead of having to enter each one one at a time? (I click in the cell, type =( then go to the cell I'm copying and click it, hit enter, and the formula for THAT cell goes in. If I drag it, it doesn't change like it does in a regular workbook where the info is in the same worksheet... (I know, that's two questions for the price of one :D ) thanks, Meenie |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with "blank" in formula
Oh and Sorry David, I do see what you mean about the Pair of quote marks.. I
only put 2 instead of 4!! :) Thank you for spotting that! "David Biddulph" wrote: Read Peo's post again. You need a *pair* of quote marks "" to surround your blank text string. One " will not do. If you've already got your formula saying =('[9-07 Chart Audit Compliance CC.xls]CVT'!$C$19) you can add the extra bits around it, and copy and paste the bits you need twice, or (as you say) you can just click in the cell to get the reference into your formula. -- David Biddulph "Meenie" wrote in message ... So are you saying my formula should be =IF('[9-07 Chart Audit Compliance CC.xls]CVT'!$C$19=",",='[9-07 Chart Audit Compliance CC.xls]CVT'!$C$19)? Wow, how do you put all that in? Do you type =IF( then click the cell you're copying from, then type the ",", then click the cell you're copying from again?? yikes. that isn't right is it... gads. "Peo Sjoblom" wrote: =IF(A2="","",A2) replace A2 with your data -- Regards, Peo Sjoblom "Meenie" wrote in message ... Hi, I'm doing a formula that takes info from a worksheet in one workbook and copies it to another worksheet in another workbook. My formula looks like this: =('[9-07 Chart Audit Compliance CC.xls]CVT'!$C$19) and it works fine except for one thing. If the cell I'm taking the info from is empty, it puts 0.00% in the worksheet I'm copying to. Each cell is added and an average is taken and the 0.00% changes the average! What can I add to the formula to make the cell blank if the cell I'm copying is blank? Boy I hope this makes sense :) The workbook goes for a month at a time but we want to see the averages each week and the 0%'s make that number incorrect until you get to the end of the month and all the cells are filled. ALSO is there a way to make the formulas fill in correctly instead of having to enter each one one at a time? (I click in the cell, type =( then go to the cell I'm copying and click it, hit enter, and the formula for THAT cell goes in. If I drag it, it doesn't change like it does in a regular workbook where the info is in the same worksheet... (I know, that's two questions for the price of one :D ) thanks, Meenie |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with "blank" in formula
I must be doing something wrong... I entered this formula and it did make the
cell blank instead of showing 0.00%, but when I went to the 1st sheet and entered a number, on the 2nd sheet it still remained blank. <sigh "Peo Sjoblom" wrote: =IF(A2="","",A2) replace A2 with your data -- Regards, Peo Sjoblom "Meenie" wrote in message ... Hi, I'm doing a formula that takes info from a worksheet in one workbook and copies it to another worksheet in another workbook. My formula looks like this: =('[9-07 Chart Audit Compliance CC.xls]CVT'!$C$19) and it works fine except for one thing. If the cell I'm taking the info from is empty, it puts 0.00% in the worksheet I'm copying to. Each cell is added and an average is taken and the 0.00% changes the average! What can I add to the formula to make the cell blank if the cell I'm copying is blank? Boy I hope this makes sense :) The workbook goes for a month at a time but we want to see the averages each week and the 0%'s make that number incorrect until you get to the end of the month and all the cells are filled. ALSO is there a way to make the formulas fill in correctly instead of having to enter each one one at a time? (I click in the cell, type =( then go to the cell I'm copying and click it, hit enter, and the formula for THAT cell goes in. If I drag it, it doesn't change like it does in a regular workbook where the info is in the same worksheet... (I know, that's two questions for the price of one :D ) thanks, Meenie |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with "blank" in formula
Firstly check that your formula on the one sheet is referring to the correct
cell on the correct sheet in the other book. Are the two books both open while you are doing these changes? Secondly make sure that under Tools/ Options/ Calculation you've got it set to Automatic, nor Manual. -- David Biddulph "Meenie" wrote in message ... I must be doing something wrong... I entered this formula and it did make the cell blank instead of showing 0.00%, but when I went to the 1st sheet and entered a number, on the 2nd sheet it still remained blank. <sigh "Peo Sjoblom" wrote: =IF(A2="","",A2) replace A2 with your data -- Regards, Peo Sjoblom "Meenie" wrote in message ... Hi, I'm doing a formula that takes info from a worksheet in one workbook and copies it to another worksheet in another workbook. My formula looks like this: =('[9-07 Chart Audit Compliance CC.xls]CVT'!$C$19) and it works fine except for one thing. If the cell I'm taking the info from is empty, it puts 0.00% in the worksheet I'm copying to. Each cell is added and an average is taken and the 0.00% changes the average! What can I add to the formula to make the cell blank if the cell I'm copying is blank? Boy I hope this makes sense :) The workbook goes for a month at a time but we want to see the averages each week and the 0%'s make that number incorrect until you get to the end of the month and all the cells are filled. ALSO is there a way to make the formulas fill in correctly instead of having to enter each one one at a time? (I click in the cell, type =( then go to the cell I'm copying and click it, hit enter, and the formula for THAT cell goes in. If I drag it, it doesn't change like it does in a regular workbook where the info is in the same worksheet... (I know, that's two questions for the price of one :D ) thanks, Meenie |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with "blank" in formula
Hi David and Thanks for your reply :)
Yes, the formula is pointing to the right sheet. When the first formula (the one that's putting in the zeros that I don't want) if I enter a number into sheet 1 it sshows up on sheet 2. But if I change the formula to remove the zeros, then if I enter a number on sheet 1, sheet 2 stays blank. Yes, both sheets are open, Yes its automatic, not manual :) I have Excel 2003 btw... did I say that before? :) "David Biddulph" wrote: Firstly check that your formula on the one sheet is referring to the correct cell on the correct sheet in the other book. Are the two books both open while you are doing these changes? Secondly make sure that under Tools/ Options/ Calculation you've got it set to Automatic, nor Manual. -- David Biddulph "Meenie" wrote in message ... I must be doing something wrong... I entered this formula and it did make the cell blank instead of showing 0.00%, but when I went to the 1st sheet and entered a number, on the 2nd sheet it still remained blank. <sigh "Peo Sjoblom" wrote: =IF(A2="","",A2) replace A2 with your data -- Regards, Peo Sjoblom "Meenie" wrote in message ... Hi, I'm doing a formula that takes info from a worksheet in one workbook and copies it to another worksheet in another workbook. My formula looks like this: =('[9-07 Chart Audit Compliance CC.xls]CVT'!$C$19) and it works fine except for one thing. If the cell I'm taking the info from is empty, it puts 0.00% in the worksheet I'm copying to. Each cell is added and an average is taken and the 0.00% changes the average! What can I add to the formula to make the cell blank if the cell I'm copying is blank? Boy I hope this makes sense :) The workbook goes for a month at a time but we want to see the averages each week and the 0%'s make that number incorrect until you get to the end of the month and all the cells are filled. ALSO is there a way to make the formulas fill in correctly instead of having to enter each one one at a time? (I click in the cell, type =( then go to the cell I'm copying and click it, hit enter, and the formula for THAT cell goes in. If I drag it, it doesn't change like it does in a regular workbook where the info is in the same worksheet... (I know, that's two questions for the price of one :D ) thanks, Meenie |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with "blank" in formula
Could you post here the two formulae, i.e. the one where it does respond to
the other sheet changing and the one that doesn't? Don't try to retype them in the group, just copy from the formula bar and paste here. It might be worth having the two formulae in parallel in different cells in your destination cell so that you can see the effects side by side while you change the source sheet (and if need be while we do some further diagnostic work). -- David Biddulph "Meenie" wrote in message ... Hi David and Thanks for your reply :) Yes, the formula is pointing to the right sheet. When the first formula (the one that's putting in the zeros that I don't want) if I enter a number into sheet 1 it sshows up on sheet 2. But if I change the formula to remove the zeros, then if I enter a number on sheet 1, sheet 2 stays blank. Yes, both sheets are open, Yes its automatic, not manual :) I have Excel 2003 btw... did I say that before? :) "David Biddulph" wrote: Firstly check that your formula on the one sheet is referring to the correct cell on the correct sheet in the other book. Are the two books both open while you are doing these changes? Secondly make sure that under Tools/ Options/ Calculation you've got it set to Automatic, nor Manual. -- David Biddulph "Meenie" wrote in message ... I must be doing something wrong... I entered this formula and it did make the cell blank instead of showing 0.00%, but when I went to the 1st sheet and entered a number, on the 2nd sheet it still remained blank. <sigh "Peo Sjoblom" wrote: =IF(A2="","",A2) replace A2 with your data -- Regards, Peo Sjoblom "Meenie" wrote in message ... Hi, I'm doing a formula that takes info from a worksheet in one workbook and copies it to another worksheet in another workbook. My formula looks like this: =('[9-07 Chart Audit Compliance CC.xls]CVT'!$C$19) and it works fine except for one thing. If the cell I'm taking the info from is empty, it puts 0.00% in the worksheet I'm copying to. Each cell is added and an average is taken and the 0.00% changes the average! What can I add to the formula to make the cell blank if the cell I'm copying is blank? Boy I hope this makes sense :) The workbook goes for a month at a time but we want to see the averages each week and the 0%'s make that number incorrect until you get to the end of the month and all the cells are filled. ALSO is there a way to make the formulas fill in correctly instead of having to enter each one one at a time? (I click in the cell, type =( then go to the cell I'm copying and click it, hit enter, and the formula for THAT cell goes in. If I drag it, it doesn't change like it does in a regular workbook where the info is in the same worksheet... (I know, that's two questions for the price of one :D ) thanks, Meenie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"If" statement returning blank gives downstream formula errors. | Excel Discussion (Misc queries) | |||
If A3=alpha numeric,"X", if A3=text,"Y", Blank | Excel Worksheet Functions | |||
How do I replace a "#N/A" formula result with a blank in excel? | Excel Discussion (Misc queries) | |||
excel formula to enter "0" if cell blank | Excel Worksheet Functions | |||
Changing "returned" values from "0" to "blank" | Excel Worksheet Functions |