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.
