LinkBack URL
About LinkBacks
how to fix the cell reference in excel when column insert or delete it needs to refer same cell when we apply formula
how to fix the cell reference in excel when column insert or delete it needs to refer same cell when we apply formula
What's the formula that you are using? Explain what you mean by "when column insert or delete it needs to refer same cell".
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
I applied formula that=if(A2="F","ESCORT","") but after applying this formula if delete or insert Row it is not referring same cell.
OK - so A2 will change if you insert or delete cells. Change it to this:
=IF($A$2="F","ESCORT","")
If I insert one A2 row before formula needs to refer inserted cell number A2 because if I do with above formula it is jumping to locked cell I think u got my question
Have you tried my suggestion in post #4? If so, why does it not work for you? Please attach a sample workbook.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Pictures are of no use. Attach the workbook itself.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
I can't follow what you are doing. Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).
1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired solution is also shown (mock up the results manually).
3. Make sure that all confidential information is removed first!!
4. Try to avoid using merged cells. They cause lots of problems!
Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
DATA1.xlsx DATA1.xlsx
i have attached excel and i have applied formula in cell # C17
That still doesn't make it clear. What do you expect the formula to be able to do??
There is no formula in C17. In fact, I can't see any formulae anywhere in that workbook.
Last edited by AliGW; 01-01-2018 at 06:20 AM.
F17... referring back to C17.
I don't understand what you're trying to do. I see you have this formula in every blue-shaded row in column F:
=IF($C$17="F","ESCORT","")
What result is it that you want, and where? Please attach another file showing the results you want to get - enter them manually if necessary. Please also be careful with all the information on the other sheets - there seems to be a lot there which may be confidential. If the data isn't needed to get the results you want, then remove it.
Regards,
Aardigspook
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
You don't need to give me rep if I helped, but a thank-you is nice.
Ah, yes - I see. No idea what the OP is trying to do!
there is a formula in Cell F2 referring C17, when i insert copied rows before C17 it is not referring added row and formula going down u can see in the screen shot above attached in previous post.
The problem is, you aren't giving enough detail. Explain WHY cell F2 needs to refer to C17 as opposed to any other cell - what is it meant to be doing? No pictures, please - explain in words what you expect to happen.
You will get help faster if you respond quickly to the questions asked.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks