Link to home
Start Free TrialLog in
Avatar of leezac
leezac

asked on

Formulas not retaining cell information

I have created formulas on a sheet where I use code to poplulate a form.  I noticed when I delete the forms where I paste the data to I get #Ref! in the formula that is to be used to paste into the sheet.  Is there a way to not lose the cell references in the formulat when data is deleted?


=INDEX(Cpuimport!#REF!,MATCH(CPU!#REF!,Cpuimport!#REF!,0))
Avatar of SmittyPro
SmittyPro
Flag of United States of America image

You can use INDIRECT:

=INDEX(INDIRECT("Cpuimport!Address"),MATCH(INDIRECT("CPU!Address"),INDIRECT("Cpuimport!Address"),0))

Where "Address" equals the original range addresses.

HTH,
Avatar of leezac
leezac

ASKER

Thanks but don't understand.....

This is how it should look

=INDEX(Cpuimport!$C$4:$C$5000,MATCH(CPU!D4,Cpuimport!$A$4:$A$5000,0))

I have a formula page and was going to use vba to add to columns on CPU sheet, but when  I delete the CPU sheet I the the REF! error on the formula tab
SOLUTION
Avatar of SmittyPro
SmittyPro
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rory Archibald
Don't delete the cells - clear them instead.
Avatar of leezac

ASKER

Rorya,  I tried clear and go the #ref! error....
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial