• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Transfer with Excel Formula from 1 sheet to another automatic as entered

X

xcruc1at3r

Member
i am generating #ref error the transfer of data in the required sheet is perfect

The automatically generated sheet from main sheet...formula as :

in A2 : =IF(Database!I2="usm",MAX(A$1:A1)+1,"")

in B2 : =IF(ROW()>MAX($A:$A)+1,"",OFFSET(Database!$A$1,MATCH(ROW()-1,$A:$A,0)-1,COLUMN()-2))

and stretch still required data....but the MAIN ISSUE is that when I Delete the data from the main source sheet it shows #ref Error in reflected sheet....untill I drag A2 till Required ROW of data limit...


is there any way to come across this problem due to which i have to refresh all the populated sheets after deleting data from soruce sheet and updating the with new data...
 
Is it possible to overwrite the data without deleting it, #REF error is usually a result of the loss of the original reference .
 
Have you tried Clearing the Contents of the cells rather than Deleteing the Rows/Columns?
 
well thats not possible! cause the entered data is already exists in the data but based on previous date based activity... so we entry current date data that we receive from one of the department...so we delete the previous data and keep the current and the previous could be any where... in the data... its a Large No. of Data!!...

so for that what we do it run delete duplicates macro otherwise to find and replace of delete will take alot of time....
 
One possible solution is to convert your formulas to text by placing ' in front the formula before you refresh your data and then after you refresh your data revert the formulas back to their original
 
well i have to work in both cases right ! whether refresh the A column with =IF(Database!I2="usm",MAX(A$1:A1)+1,"") or revert back the formula at its original place!
 
"...so we delete the previous data and keep the current and the previous could be any where... in the data... "


There's the problem!

After clearing the area

Inserting new records, you are Deleting records.


Don't worry about the data being large.

Setup your retrieval queries/formulas to pickout the date and data from the fields you want.


Doing this will negate the issue of having specific data in a specific location as long as your columns are consistant month to month
 
Hui well this query is basically related to the same database that you have worked on !

the important field are the remarks....Like USM should move to usm enroute, Com to commercial unt to untraceable & etc etc...

so can u come up with sum formula to handle this issue !!
 
The formula's I used scale to any amount of data automatically

They use named formulas for Date and Remarks

So Clear the Contents of the data area Row 2 downwards

(Not Delete but Clear Contents)

Copy your data in

and change the values on Summery-Report!B10:B15

Everything should be ok
 
hmmmmm....I see !!..well Hui am running a macro to delete the duplicates rows from Up to down.... because if i'd go towards clearing the rows 1 by 1 that will take Hours!!...


what if i use a Macro ! to clear the rows rather then deleting !! so that will not create the ref# error and then Sort it Date wise so that will automatically bring empty cells down...!! right!...

that could solve the problem
 
Provided you don't delete the First (Row 2) or Last row that shouldn't be a problem


Clearing will be a problem, as the Named Formulas count the number of rows
 
hui if i used below mentioned formula's: when i add new data & clear the rows old duplicate data...NOT DELETE THEM so it does not give ref# error because the original reference cells are not deleted....and when i sort the data Date wise so the empties that i cleared are moved down....

in A2 : =IF(Database!I2="usm",MAX(A$1:A1)+1,"")

in B2 : =IF(ROW()>MAX($A:$A)+1,"",OFFSET(Database!$A$1,MATCH(ROW()-1,$A:$A,0)-1,COLUMN()-2))


so i guess the Macro that i am running is based on deleting the Duplicates so is there any possibility that it should only clear the cells and sort the data date wise !!...

will solve the problem
 
You must log in or register to reply here.
Back
Top