Question description:
This user has given permission to use the problem statement for this
blog.
Hi, i have a question,
if i want to link sheet 2 cell A2 to sheet 1 A2, it will be =sheet 1!A2
then i want to link sheet 2 cell A3 to sheet 1 A23, can i use a formula or some way to make it increase in a format?
then link sheet 2 A 4 to sheet 1 A44, with a consistence of increment 21 cells
Solved by M. J. in 34 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
14/03/2018 - 09:53
Hi, welcome
User
14/03/2018 - 09:53
hi good day
Excelchat Expert
14/03/2018 - 09:53
thank you
User
14/03/2018 - 09:53
my problem has stated in the space i key in just now
User
14/03/2018 - 09:53
have any idea?
Excelchat Expert
14/03/2018 - 09:54
allow me a moment to review it
User
14/03/2018 - 09:57
i have show you the situation in the sheet there
User
14/03/2018 - 09:57
please refer to the sheet
Excelchat Expert
14/03/2018 - 09:57
ok
User
14/03/2018 - 09:57
there is sheet 1 and sheet 2
Excelchat Expert
14/03/2018 - 09:57
yes i can see the data
Excelchat Expert
14/03/2018 - 09:58
so you want to pick data for A,B,C and D from Sheet 1 and for date 2 March
Excelchat Expert
14/03/2018 - 09:58
right?
User
14/03/2018 - 09:58
yes
User
14/03/2018 - 09:58
directly link by any formula?
Excelchat Expert
14/03/2018 - 09:59
ok, let me write the formula for you
User
14/03/2018 - 09:59
since the difference of cell of A in sheet 1 is a consistence of 7 cell
User
14/03/2018 - 09:59
okay thank you
User
14/03/2018 - 09:59
appreciate it so much
User
14/03/2018 - 10:00
do you mind if i ask, where can i refer to? looking for the excel formula
Excelchat Expert
14/03/2018 - 10:00
do you need this in excel?
User
14/03/2018 - 10:00
yes
Excelchat Expert
14/03/2018 - 10:00
you are using google sheet
Excelchat Expert
14/03/2018 - 10:01
formula structures are different
User
14/03/2018 - 10:01
but then i will use the formula in excel
Excelchat Expert
14/03/2018 - 10:01
let me do this in excel, please allow me some moments
User
14/03/2018 - 10:01
in excel, there is sheet 1 and sheet 2
User
14/03/2018 - 10:01
yes
User
14/03/2018 - 10:01
thank you
Excelchat Expert
14/03/2018 - 10:01
do have an excel file
Excelchat Expert
14/03/2018 - 10:01
if not i can copy data
User
14/03/2018 - 10:02
can you please show me the formula in excel and in google sheet, do you think is there the same?
User
14/03/2018 - 10:02
u just copy the data i provided in the google drive
Excelchat Expert
14/03/2018 - 10:02
ok
User
14/03/2018 - 10:02
due to the excel that i have was in mandarin language
User
14/03/2018 - 10:02
sorry...
User
14/03/2018 - 10:03
but then the data and the structure is almost the same that i provided in the google drive beside our chat
User
14/03/2018 - 10:03
google sheet*
Excelchat Expert
14/03/2018 - 10:03
I can do it in either excel or google, whatever you like, please decide
User
14/03/2018 - 10:03
excel
User
14/03/2018 - 10:03
thank you
Excelchat Expert
14/03/2018 - 10:03
ok
User
14/03/2018 - 10:04
i will ask again, when i need a formula for me to use it in google sheet in the future, thank yo
User
14/03/2018 - 10:04
you*
Excelchat Expert
14/03/2018 - 10:04
ok
Excelchat Expert
14/03/2018 - 10:09
[Uploaded an Excel file]
Excelchat Expert
14/03/2018 - 10:09
see the example in the excel sheet
User
14/03/2018 - 10:13
sorry, i am looking into it
Excelchat Expert
14/03/2018 - 10:14
ok
User
14/03/2018 - 10:14
laptop suddenly hang half way
Excelchat Expert
14/03/2018 - 10:14
no problem
Excelchat Expert
14/03/2018 - 10:14
see sheet 2
Excelchat Expert
14/03/2018 - 10:14
and it picks values from sheet 1 through formula
User
14/03/2018 - 10:16
then?
User
14/03/2018 - 10:16
it
User
14/03/2018 - 10:16
i saw it
User
14/03/2018 - 10:17
=INDEX(Sheet1!$A$12:$D$15,MATCH(Sheet2!B2,Sheet1!$A$12:$A$15,0),2)
User
14/03/2018 - 10:17
can i drag it down
Excelchat Expert
14/03/2018 - 10:17
yes this is the formula
Excelchat Expert
14/03/2018 - 10:17
no
User
14/03/2018 - 10:17
every cell in B5 to B13 can get the formula?
Excelchat Expert
14/03/2018 - 10:17
you have to just edit range
User
14/03/2018 - 10:17
how?
Excelchat Expert
14/03/2018 - 10:18
just change this portion in the formula
Excelchat Expert
14/03/2018 - 10:18
$A$12:$D$15
User
14/03/2018 - 10:18
what if
User
14/03/2018 - 10:18
i change something...
User
14/03/2018 - 10:18
sorry
Excelchat Expert
14/03/2018 - 10:19
Sir there can be whatifs, but the situation that you presented is what I am assuming as a given
Excelchat Expert
14/03/2018 - 10:20
what you are thinking, will require a reformatting of sheet 1
User
14/03/2018 - 10:20
oppss
User
14/03/2018 - 10:20
i am so sorry...
User
14/03/2018 - 10:20
i believe, i have given lack of data..
User
14/03/2018 - 10:21
what if the situation has changed into like what i present to you in the google sheet now
User
14/03/2018 - 10:21
then the data will be duplicated for AREA A, isnt it?
User
14/03/2018 - 10:21
i am so sorry for that
Excelchat Expert
14/03/2018 - 10:22
Dear friend, my system admin allows me to answer only 1 question in a session, that is a system limitation
Excelchat Expert
14/03/2018 - 10:22
no problem
Excelchat Expert
14/03/2018 - 10:22
you can post again
Excelchat Expert
14/03/2018 - 10:22
yes
User
14/03/2018 - 10:22
okay, then we just stick to the first question
Excelchat Expert
14/03/2018 - 10:22
if you can just change the range as i mentioned
User
14/03/2018 - 10:22
i am so sorry for the question 2 then i meant to ask
Excelchat Expert
14/03/2018 - 10:22
you can use this data
User
14/03/2018 - 10:23
you meant =INDEX(Sheet1!$A$12:$D$15,MATCH(Sheet2!B2,Sheet1!$A$12:$A$15,0),2)
User
14/03/2018 - 10:23
?
Excelchat Expert
14/03/2018 - 10:23
this formula, i mean
Excelchat Expert
14/03/2018 - 10:23
yes
Excelchat Expert
14/03/2018 - 10:23
for example
Excelchat Expert
14/03/2018 - 10:23
let me do it here
User
14/03/2018 - 10:24
but then, it is not good enough for me to link them faster
Excelchat Expert
14/03/2018 - 10:24
to link them faster, you need reorganization
Excelchat Expert
14/03/2018 - 10:25
of sheet 1 data
Excelchat Expert
14/03/2018 - 10:25
I hope you original question has been answered
User
14/03/2018 - 10:25
what do you meant reorganization?
Excelchat Expert
14/03/2018 - 10:25
rearranging data in sheet1
User
14/03/2018 - 10:26
okay, i will try
User
14/03/2018 - 10:26
hank you for your help
User
14/03/2018 - 10:26
thank you*
Excelchat Expert
14/03/2018 - 10:26
so it can be pulled through formula
Excelchat Expert
14/03/2018 - 10:26
you are welcome.
Excelchat Expert
14/03/2018 - 10:26
happy to help you
User
14/03/2018 - 10:26
okay
User
14/03/2018 - 10:27
i am so sorry for being troublesome
User
14/03/2018 - 10:27
thank you
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.
