Hello everyone!
I was hoping to ask this problem I'm encountering...
I have a list of names in Sheet1 and I referenced each name in column B in Sheet2.
But the problem is, if I delete a name of that sheet name in Sheet2, I encounter #Ref error in Sheet2. Example,
In C12, the name is "Abad, Jose". This is in sheet1.
In Sheet2, E12, I used to reference it to sheet1 c12. It works well until the data in C12 is present. But once I delete the row of C12, I get #ref error in E12.
Last edited by wedzmer; 07-17-2014 at 03:37 PM.
That's perfectly normal, If you don't want to show these erros, do:
Please Login or Register to view this content.
"The quieter you become, the more you are able to hear"
Any reputation (*) points appreciated.
"If you know yourself but not the enemy, for every victory gained, you will suffer defeat."
Hi,
Try by deleting row in Sheet1 and the check the data in Sheet2 how does it reflects.
Punnam
Unfortunately, IFERROR will only hide errors and it will not retain cell references. Using INDIRECT function may help
=INDIRECT("Sheet2!A2")
Even if row 2 on sheet 2 is deleted, the formula will remain unchanged.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
In sheet2, use the formula:
in cell A1. Copy across and down. This will allow you to delete rows in sheet1, as opposed to deleting the contents, and reflect the changes.Formula:
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Hello everyone!!!
This actually worked. The one you attached.. but it won't work when I use the formula in my workbook. There's this symbol in the formula bar { } that shows up when I click on the cell but when I press F2 on the keyboard to copy it, it's gone. And when I drag the cell downwards to copy the formulas, it wouldn't show up.. You see, there is a possibility that the names are more than 50 so I need to use it on all cells.Originally Posted by Punnam
Hello TMS, I'm not sure how to use this code.. I tried it but when I delete a row on Sheet 1, it still returns #Ref error.In sheet2, use the formula:
in cell A1. Copy across and down. This will allow you to delete rows in sheet1, as opposed to deleting the contents, and reflect the changes.Formula:Please Login or Register to view this content.
Regards, TMS
The {} is an indication that the formula is an ARRAY FORMULA to be entered with Ctrl + Shift + Enter
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
I see.. So that means I can't just drag the cell down and I have to manually encode each cell with that formula?
Is there any other way to do it? I mean, there are 60 different cells all together.
Enter the formula in A1 with Ctrl + Shift + Enter. Then after that, copy down and across.
It would probably help if you shared a (sample) copy of your workbook instead of working with hypothetical formulae and guesses.
Regards, TMS
[QUOTE=wedzmer;3770045]Hello everyone!!!
This actually worked. The one you attached.. but it won't work when I use the formula in my workbook. There's this symbol in the formula bar { } that shows up when I click on the cell but when I press F2 on the keyboard to copy it, it's gone. And when I drag the cell downwards to copy the formulas, it wouldn't show up.. You see, there is a possibility that the names are more than 50 so I need to use it on all cells.
1) As my solution is based on array function it is necessary to activate it by using Ctrl+Shift+Enter on after every small change done for first row and the rest is simply copied .
2)Range : it should be manually entered once based on data in u r workbook .
Punnam
Hello TMS!!!
Thank you for your response... I attached the filea I was working on.. Winon was the one who helped me with much of that is needed with the macros on the other workbooks. And this would be the last problem I can see for this work I'm trying to put together.
The formula you gave me actually worked on three workbooks I was working on found on the Master List folder; workbooks: June, July and August.
The formula however did not work on the workbook Winon designed with Macros. I was trying to use the same formula you gave on the workbook named "SF 2F" -sheetname SF2 July, but when I used it, the data displayed is the 12th name instead of the first name on the list.
This workbooks will be used for the teaching volunteers of the education program here in our small city. Hoping that it will generate funding so that it could be used for the materials for the teaching program for out-of-school youth.
Thank you very much!
Wedzmer
School Forms 2014.xlsm School Form 2F.xlsm MY SF1.xls (1) June - Master List.xlsx (2) July - Master List.xlsx
[QUOTE=Punnam;3770618]I tried it... But didn't work...
I'm late into this. I found that the formulae were in merged cells (the curse of the spreadsheet) and this prevented the formulae to be correctly entered with Ctrl + Shift + Enter. I unmerged the cells, properly entered the formulae and then formatted the cells "Centered Across Selection".
Thanks a lot everyone!
Much Appreciated!
You're welcome.
Thanks for the rep
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks