Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 07-06-2017, 11:48 PM
ehmo ehmo is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Jul 2017
Posts: 3
ehmo is on a distinguished road
Question Pasting new data and formulas stop working

I am having excel file with 2 sheets. One of them is full of formulas that are linked to one, lets say that the name of sheet is sheet1 (just data numbers and text). Formulas are working ok and doing their job but when I am trying to insert (paste) new data to sheet1 formulas on the another sheet are not working at all they are showing #REF. I am using IF statement like this =IF('sheet1'!G14=0;0;'sheet1'!G14).

Thanks in advance.
Reply With Quote
  #2  
Old 07-07-2017, 12:24 AM
Kubi Kubi is offline Windows XP Office XP
Advanced Beginner
 
Join Date: May 2017
Posts: 32
Kubi is on a distinguished road
Default

are you just wanting to return a value from sheet 1 without looking through it? perhaps try using a VLOOKUP for example

=VLOOKUP(A6,Sheet1!A110,4,0)
Reply With Quote
  #3  
Old 07-07-2017, 12:31 AM
ehmo ehmo is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Jul 2017
Posts: 3
ehmo is on a distinguished road
Default

I delete the data on sheet1 and then add the data with different numbers again. After pasting new data it just show #REF on the formula sheet.
Reply With Quote
  #4  
Old 07-07-2017, 12:33 AM
Kubi Kubi is offline Windows XP Office XP
Advanced Beginner
 
Join Date: May 2017
Posts: 32
Kubi is on a distinguished road
Default

can you provide a snap shot of what you are trying to do?

does the data on sheet 2 change or is it always looking at the same thing but returning a different result once you adjust sheet 1??

are you using more than one formula on sheet 2 to refer to the result of that IF formula?

having more than one work off the outcome of a single outcome might be you issue.

i.e

cell A2 =IF(sheet1,G14,0,0)

Cell G2 =IF(sheet2,A2,0,0) - error as it is refering to a specific outcome of sheet 2 A2
Reply With Quote
  #5  
Old 07-10-2017, 01:58 AM
ehmo ehmo is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Jul 2017
Posts: 3
ehmo is on a distinguished road
Default

Thanks for answers I managed my problem with removing if statement. Now formulas are working when I am adding new data over old.
Reply With Quote
Reply

Tags
formula, if-statement, paste

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Workbook Stop Working When Run VBA GeoKoro13 Excel Programming 1 12-01-2016 04:03 AM
VBA to gather data from one workbook and pasting it in a data set lwbarnes Excel Programming 1 11-02-2016 07:05 PM
All Office products stop working lethlean Office 1 05-18-2013 07:07 AM
Save As cause app to stop working rafterman Word 1 03-04-2013 11:20 PM
GIF's stop working Kwarior PowerPoint 3 03-17-2010 09:59 PM


All times are GMT -7. The time now is 08:39 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft