Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need to insert new rows in an sheet and keep the reference to one cell constant. For example, I type =b2 in cell a2. I always want a2 to take value from b2 even if i enter rows above b2. So when b2 becomes b3 when I insert row above b2, I want a2 to take its new value from b2 only and not from b3. How can I do this?
Solved by T. W. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 15/03/2018 - 02:32
Hi! Welcome to Got it!
Excelchat Expert 15/03/2018 - 02:33
So you want to insert new rows and take a constant reference?
User 15/03/2018 - 02:33
yes
User 15/03/2018 - 02:33
so a1 should now go blank
User 15/03/2018 - 02:33
not remain 64
User 15/03/2018 - 02:34
do u get me?
Excelchat Expert 15/03/2018 - 02:34
ahhh
Excelchat Expert 15/03/2018 - 02:34
Not quite sir, wait
Excelchat Expert 15/03/2018 - 02:34
Can i restate your question?
User 15/03/2018 - 02:34
so a1 should now change to 32
User 15/03/2018 - 02:35
again when i insert a new row, a1 should go blank
Excelchat Expert 15/03/2018 - 02:35
ahhh, and A2 should go 32?
User 15/03/2018 - 02:36
a1 should only look at b1 for its value
User 15/03/2018 - 02:36
no i m not bothered about a2
Excelchat Expert 15/03/2018 - 02:36
Ahh ok sir
User 15/03/2018 - 02:36
so no matter if b1 gets shifted to b2
Excelchat Expert 15/03/2018 - 02:37
ahh
Excelchat Expert 15/03/2018 - 02:37
You should erase the $ signs in the formula
User 15/03/2018 - 02:37
now if i type 16 in b1, a1 should become 16 as well
User 15/03/2018 - 02:38
you see what is happening
User 15/03/2018 - 02:38
??
Excelchat Expert 15/03/2018 - 02:38
ah yes
Excelchat Expert 15/03/2018 - 02:39
I think
Excelchat Expert 15/03/2018 - 02:39
that when you insert new cells/ rows, it goes completely clean in terms of its content and format
User 15/03/2018 - 02:40
i just want a1 to only look at b1 for its value
User 15/03/2018 - 02:40
no matter you shift cells to right or left
User 15/03/2018 - 02:40
or up or down
Excelchat Expert 15/03/2018 - 02:41
it should go blank now?
Excelchat Expert 15/03/2018 - 02:42
ahhhh
User 15/03/2018 - 02:42
no..it should become ok now
User 15/03/2018 - 02:42
now it should go blank
User 15/03/2018 - 02:42
now not ok
User 15/03/2018 - 02:43
now blank again
Excelchat Expert 15/03/2018 - 02:43
ahhh ok
User 15/03/2018 - 02:43
get me?
Excelchat Expert 15/03/2018 - 02:43
i get you now sir
User 15/03/2018 - 02:43
are u from india mate?
Excelchat Expert 15/03/2018 - 02:43
yes :)
User 15/03/2018 - 02:43
ok..i am an indian too
User 15/03/2018 - 02:43
pls dont call me sir
Excelchat Expert 15/03/2018 - 02:43
sorry
User 15/03/2018 - 02:43
help me quickly
Excelchat Expert 15/03/2018 - 02:44
You should place $ at the before B
User 15/03/2018 - 02:45
still doesn't work
User 15/03/2018 - 02:45
it should have gone blank
User 15/03/2018 - 02:45
and now ok
Excelchat Expert 15/03/2018 - 02:46
ahh ok
Excelchat Expert 15/03/2018 - 02:46
sorry im quite confused now
User 15/03/2018 - 02:46
The value in a1 should always match the value in b1
Excelchat Expert 15/03/2018 - 02:47
Yes, but i cant make it work right now, can u give a minute? sory
User 15/03/2018 - 02:47
if i insert rows above or below or left or right of b1, a1 should still be same as b1
User 15/03/2018 - 02:47
ok
User 15/03/2018 - 02:48
time is running out mate
Excelchat Expert 15/03/2018 - 02:48
Yep mate, wait
Excelchat Expert 15/03/2018 - 02:49
thank you, im close to it mate
User 15/03/2018 - 02:50
brilliant
Excelchat Expert 15/03/2018 - 02:53
mate
Excelchat Expert 15/03/2018 - 02:53
i think i found it
User 15/03/2018 - 02:53
go on
Excelchat Expert 15/03/2018 - 02:53
sorry for the long wait
User 15/03/2018 - 02:53
its ok
User 15/03/2018 - 02:53
worth it if i get the solution
Excelchat Expert 15/03/2018 - 02:53
we should try using the indirect function
Excelchat Expert 15/03/2018 - 02:53
is it okay?
User 15/03/2018 - 02:53
yes
User 15/03/2018 - 02:53
ing is ok
User 15/03/2018 - 02:54
anything
Excelchat Expert 15/03/2018 - 02:54
is that it mate?
User 15/03/2018 - 02:54
yes..thats what i want
User 15/03/2018 - 02:55
its simple
User 15/03/2018 - 02:55
eers
User 15/03/2018 - 02:55
cheers
User 15/03/2018 - 02:55
u r a star mate
Excelchat Expert 15/03/2018 - 02:55
CHeers!
Excelchat Expert 15/03/2018 - 02:55
Thanks mate! :)
User 15/03/2018 - 02:55
where u from in india?
User 15/03/2018 - 02:56
I m from mumbai
Excelchat Expert 15/03/2018 - 02:56
Sorry mate, cant tell based on the policy
User 15/03/2018 - 02:56
ok thats fine
User 15/03/2018 - 02:56
appreciate the help
Excelchat Expert 15/03/2018 - 02:56
But thanks for the consideration mate
User 15/03/2018 - 02:56
take care..cheers
Excelchat Expert 15/03/2018 - 02:56
Is ther anything i can help u with?
User 15/03/2018 - 02:56
not at the moment..cheers
Excelchat Expert 15/03/2018 - 02:56
cheers mate, thanks
Excelchat Expert 15/03/2018 - 02:57
Have a good day mate,
Excelchat Expert 15/03/2018 - 02:57
I'm gonna get going now, thanks!
Excelchat Expert 15/03/2018 - 03:06
mate?
Excelchat Expert 15/03/2018 - 03:06
u there?

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.