| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Columns - Formula Results turn to #REF!
I am trying to pull information from a schedule and am running into a
problem. The person updating the schedule deletes the previous week and my formula result changes to #REF!. Is there an easy way to fix this. I had fixed this using a Macro but he added rows and my Macro got messed up. The formula I am using is =SUM(MANPOWER!F49:I49). Thank you, Havenstar |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Deleting Columns - Formula Results turn to #REF!
Hi Havenstar. Try using Edit-Replace and replace #REF with MANPOWER. I use
this method to update a P & L report based on a download from QuickBooks every month and it works great. HTH -- Sincerely, Michael Colvin "Havenstar" wrote: I am trying to pull information from a schedule and am running into a problem. The person updating the schedule deletes the previous week and my formula result changes to #REF!. Is there an easy way to fix this. I had fixed this using a Macro but he added rows and my Macro got messed up. The formula I am using is =SUM(MANPOWER!F49:I49). Thank you, Havenstar |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Deleting Columns - Formula Results turn to #REF!
Hi Michael,
I tried that and I got #NAME?, which gave me a formula of =SUM(MANPOWER!MANPOWER). Any other ideas? Thanks Sandi "Michael" wrote: Hi Havenstar. Try using Edit-Replace and replace #REF with MANPOWER. I use this method to update a P & L report based on a download from QuickBooks every month and it works great. HTH -- Sincerely, Michael Colvin "Havenstar" wrote: I am trying to pull information from a schedule and am running into a problem. The person updating the schedule deletes the previous week and my formula result changes to #REF!. Is there an easy way to fix this. I had fixed this using a Macro but he added rows and my Macro got messed up. The formula I am using is =SUM(MANPOWER!F49:I49). Thank you, Havenstar |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Deleting Columns - Formula Results turn to #REF!
Sandi, could you post back what the formula looks like when the #REF! is
included? -- Sincerely, Michael Colvin "Havenstar" wrote: Hi Michael, I tried that and I got #NAME?, which gave me a formula of =SUM(MANPOWER!MANPOWER). Any other ideas? Thanks Sandi "Michael" wrote: Hi Havenstar. Try using Edit-Replace and replace #REF with MANPOWER. I use this method to update a P & L report based on a download from QuickBooks every month and it works great. HTH -- Sincerely, Michael Colvin "Havenstar" wrote: I am trying to pull information from a schedule and am running into a problem. The person updating the schedule deletes the previous week and my formula result changes to #REF!. Is there an easy way to fix this. I had fixed this using a Macro but he added rows and my Macro got messed up. The formula I am using is =SUM(MANPOWER!F49:I49). Thank you, Havenstar |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Deleting Columns - Formula Results turn to #REF!
Sure can Michael - it looks like this: =SUM(MANPOWER!#REF!)
Thanks Sandi "Michael" wrote: Sandi, could you post back what the formula looks like when the #REF! is included? -- Sincerely, Michael Colvin "Havenstar" wrote: Hi Michael, I tried that and I got #NAME?, which gave me a formula of =SUM(MANPOWER!MANPOWER). Any other ideas? Thanks Sandi "Michael" wrote: Hi Havenstar. Try using Edit-Replace and replace #REF with MANPOWER. I use this method to update a P & L report based on a download from QuickBooks every month and it works great. HTH -- Sincerely, Michael Colvin "Havenstar" wrote: I am trying to pull information from a schedule and am running into a problem. The person updating the schedule deletes the previous week and my formula result changes to #REF!. Is there an easy way to fix this. I had fixed this using a Macro but he added rows and my Macro got messed up. The formula I am using is =SUM(MANPOWER!F49:I49). Thank you, Havenstar |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Deleting Columns - Formula Results turn to #REF!
Which means you'd do a replace of #REF! with F49:I49
Not sure what exactly is messign up the formula in the first place though. "Havenstar" wrote: Sure can Michael - it looks like this: =SUM(MANPOWER!#REF!) Thanks Sandi "Michael" wrote: Sandi, could you post back what the formula looks like when the #REF! is included? -- Sincerely, Michael Colvin "Havenstar" wrote: Hi Michael, I tried that and I got #NAME?, which gave me a formula of =SUM(MANPOWER!MANPOWER). Any other ideas? Thanks Sandi "Michael" wrote: Hi Havenstar. Try using Edit-Replace and replace #REF with MANPOWER. I use this method to update a P & L report based on a download from QuickBooks every month and it works great. HTH -- Sincerely, Michael Colvin "Havenstar" wrote: I am trying to pull information from a schedule and am running into a problem. The person updating the schedule deletes the previous week and my formula result changes to #REF!. Is there an easy way to fix this. I had fixed this using a Macro but he added rows and my Macro got messed up. The formula I am using is =SUM(MANPOWER!F49:I49). Thank you, Havenstar |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Deleting Columns - Formula Results turn to #REF!
Sandi, did Sean's suggestion work?
-- Sincerely, Michael Colvin "Havenstar" wrote: Sure can Michael - it looks like this: =SUM(MANPOWER!#REF!) Thanks Sandi "Michael" wrote: Sandi, could you post back what the formula looks like when the #REF! is included? -- Sincerely, Michael Colvin "Havenstar" wrote: Hi Michael, I tried that and I got #NAME?, which gave me a formula of =SUM(MANPOWER!MANPOWER). Any other ideas? Thanks Sandi "Michael" wrote: Hi Havenstar. Try using Edit-Replace and replace #REF with MANPOWER. I use this method to update a P & L report based on a download from QuickBooks every month and it works great. HTH -- Sincerely, Michael Colvin "Havenstar" wrote: I am trying to pull information from a schedule and am running into a problem. The person updating the schedule deletes the previous week and my formula result changes to #REF!. Is there an easy way to fix this. I had fixed this using a Macro but he added rows and my Macro got messed up. The formula I am using is =SUM(MANPOWER!F49:I49). Thank you, Havenstar |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Deleting Columns - Formula Results turn to #REF!
We have a spreadsheet for 26 weeks that I am charting information on I need
to sum each week/4 and get an average. Each week the previous week gets deleted, while doing this it makes my formulas turn into #REF! taking out the F49:I49 formula part and I have to repeat that 26 times for different columns. I had a macro set up to overwrite after the delete but it is very time consuming and they also want me to add another chart with different data (not to mention when they add rows it messed it all up). Here is a snap shot of 3 of my columns pulling data from the spreadsheet. All of this data turns to #REF and i would like to know if there is an easier way to make it stick, after the columns are deleted? =E51 =SUM($F$47:$I$47) =ROUND((B101/4),0) =SUM(A101) + 7 =SUM(M47:P47) =ROUND((B102/4),0) =SUM(A102) + 7 =SUM(T47:W47) =ROUND((B103/4),0) =SUM(A103) + 7 =SUM(AA47:AD47) =ROUND((B104/4),0) =SUM(A104) + 7 =SUM(AH47:AK47) =ROUND((B105/4),0) =SUM(A105) + 7 =SUM(AO47:AR47) =ROUND((B106/4),0) =SUM(A106) + 7 =SUM(AV47:AY47) =ROUND((B107/4),0) Hope this makes sense? Sandi "Sean Timmons" wrote: Which means you'd do a replace of #REF! with F49:I49 Not sure what exactly is messign up the formula in the first place though. "Havenstar" wrote: Sure can Michael - it looks like this: =SUM(MANPOWER!#REF!) Thanks Sandi "Michael" wrote: Sandi, could you post back what the formula looks like when the #REF! is included? -- Sincerely, Michael Colvin "Havenstar" wrote: Hi Michael, I tried that and I got #NAME?, which gave me a formula of =SUM(MANPOWER!MANPOWER). Any other ideas? Thanks Sandi "Michael" wrote: Hi Havenstar. Try using Edit-Replace and replace #REF with MANPOWER. I use this method to update a P & L report based on a download from QuickBooks every month and it works great. HTH -- Sincerely, Michael Colvin "Havenstar" wrote: I am trying to pull information from a schedule and am running into a problem. The person updating the schedule deletes the previous week and my formula result changes to #REF!. Is there an easy way to fix this. I had fixed this using a Macro but he added rows and my Macro got messed up. The formula I am using is =SUM(MANPOWER!F49:I49). Thank you, Havenstar |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Deleting Columns - Formula Results turn to #REF!
Well it does, but not what I am looking for I have multiple formulas and
wanted to know if there were an easier solution. See my previous post to Sean, we must have been sending at the same time. Thanks Sandi "Michael" wrote: Sandi, did Sean's suggestion work? -- Sincerely, Michael Colvin "Havenstar" wrote: Sure can Michael - it looks like this: =SUM(MANPOWER!#REF!) Thanks Sandi "Michael" wrote: Sandi, could you post back what the formula looks like when the #REF! is included? -- Sincerely, Michael Colvin "Havenstar" wrote: Hi Michael, I tried that and I got #NAME?, which gave me a formula of =SUM(MANPOWER!MANPOWER). Any other ideas? Thanks Sandi "Michael" wrote: Hi Havenstar. Try using Edit-Replace and replace #REF with MANPOWER. I use this method to update a P & L report based on a download from QuickBooks every month and it works great. HTH -- Sincerely, Michael Colvin "Havenstar" wrote: I am trying to pull information from a schedule and am running into a problem. The person updating the schedule deletes the previous week and my formula result changes to #REF!. Is there an easy way to fix this. I had fixed this using a Macro but he added rows and my Macro got messed up. The formula I am using is =SUM(MANPOWER!F49:I49). Thank you, Havenstar |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Deleting Columns - Formula Results turn to #REF!
It does. Any chance, instead of deletng the row, you can just highlight the
row and hit the delete key? That'll remove the contents but retain the formula... "Havenstar" wrote: We have a spreadsheet for 26 weeks that I am charting information on I need to sum each week/4 and get an average. Each week the previous week gets deleted, while doing this it makes my formulas turn into #REF! taking out the F49:I49 formula part and I have to repeat that 26 times for different columns. I had a macro set up to overwrite after the delete but it is very time consuming and they also want me to add another chart with different data (not to mention when they add rows it messed it all up). Here is a snap shot of 3 of my columns pulling data from the spreadsheet. All of this data turns to #REF and i would like to know if there is an easier way to make it stick, after the columns are deleted? =E51 =SUM($F$47:$I$47) =ROUND((B101/4),0) =SUM(A101) + 7 =SUM(M47:P47) =ROUND((B102/4),0) =SUM(A102) + 7 =SUM(T47:W47) =ROUND((B103/4),0) =SUM(A103) + 7 =SUM(AA47:AD47) =ROUND((B104/4),0) =SUM(A104) + 7 =SUM(AH47:AK47) =ROUND((B105/4),0) =SUM(A105) + 7 =SUM(AO47:AR47) =ROUND((B106/4),0) =SUM(A106) + 7 =SUM(AV47:AY47) =ROUND((B107/4),0) Hope this makes sense? Sandi "Sean Timmons" wrote: Which means you'd do a replace of #REF! with F49:I49 Not sure what exactly is messign up the formula in the first place though. "Havenstar" wrote: Sure can Michael - it looks like this: =SUM(MANPOWER!#REF!) Thanks Sandi "Michael" wrote: Sandi, could you post back what the formula looks like when the #REF! is included? -- Sincerely, Michael Colvin "Havenstar" wrote: Hi Michael, I tried that and I got #NAME?, which gave me a formula of =SUM(MANPOWER!MANPOWER). Any other ideas? Thanks Sandi "Michael" wrote: Hi Havenstar. Try using Edit-Replace and replace #REF with MANPOWER. I use this method to update a P & L report based on a download from QuickBooks every month and it works great. HTH -- Sincerely, Michael Colvin "Havenstar" wrote: I am trying to pull information from a schedule and am running into a problem. The person updating the schedule deletes the previous week and my formula result changes to #REF!. Is there an easy way to fix this. I had fixed this using a Macro but he added rows and my Macro got messed up. The formula I am using is =SUM(MANPOWER!F49:I49). Thank you, Havenstar |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Deleting Columns - Formula Results turn to #REF!
Unfortunately not but that did work good
Thanks Sandi "Sean Timmons" wrote: It does. Any chance, instead of deletng the row, you can just highlight the row and hit the delete key? That'll remove the contents but retain the formula... "Havenstar" wrote: We have a spreadsheet for 26 weeks that I am charting information on I need to sum each week/4 and get an average. Each week the previous week gets deleted, while doing this it makes my formulas turn into #REF! taking out the F49:I49 formula part and I have to repeat that 26 times for different columns. I had a macro set up to overwrite after the delete but it is very time consuming and they also want me to add another chart with different data (not to mention when they add rows it messed it all up). Here is a snap shot of 3 of my columns pulling data from the spreadsheet. All of this data turns to #REF and i would like to know if there is an easier way to make it stick, after the columns are deleted? =E51 =SUM($F$47:$I$47) =ROUND((B101/4),0) =SUM(A101) + 7 =SUM(M47:P47) =ROUND((B102/4),0) =SUM(A102) + 7 =SUM(T47:W47) =ROUND((B103/4),0) =SUM(A103) + 7 =SUM(AA47:AD47) =ROUND((B104/4),0) =SUM(A104) + 7 =SUM(AH47:AK47) =ROUND((B105/4),0) =SUM(A105) + 7 =SUM(AO47:AR47) =ROUND((B106/4),0) =SUM(A106) + 7 =SUM(AV47:AY47) =ROUND((B107/4),0) Hope this makes sense? Sandi "Sean Timmons" wrote: Which means you'd do a replace of #REF! with F49:I49 Not sure what exactly is messign up the formula in the first place though. "Havenstar" wrote: Sure can Michael - it looks like this: =SUM(MANPOWER!#REF!) Thanks Sandi "Michael" wrote: Sandi, could you post back what the formula looks like when the #REF! is included? -- Sincerely, Michael Colvin "Havenstar" wrote: Hi Michael, I tried that and I got #NAME?, which gave me a formula of =SUM(MANPOWER!MANPOWER). Any other ideas? Thanks Sandi "Michael" wrote: Hi Havenstar. Try using Edit-Replace and replace #REF with MANPOWER. I use this method to update a P & L report based on a download from QuickBooks every month and it works great. HTH -- Sincerely, Michael Colvin "Havenstar" wrote: I am trying to pull information from a schedule and am running into a problem. The person updating the schedule deletes the previous week and my formula result changes to #REF!. Is there an easy way to fix this. I had fixed this using a Macro but he added rows and my Macro got messed up. The formula I am using is =SUM(MANPOWER!F49:I49). Thank you, Havenstar |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find value in array | Excel Worksheet Functions | |||
| Reusing formula | Excel Discussion (Misc queries) | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Array formula returning wrong results | Excel Discussion (Misc queries) | |||
| how prevent formula in cell from deleting when deleting value???? | New Users to Excel | |||
Linear Mode
