Question description:
This user has given permission to use the problem statement for this
blog.
I need to to copy and paste cells form column A based on what values are in column B and B, for example I have I have a list of exercises in column a and then criteria for each exercise in B and C. Then I have cells that make up named ranges of exercises based on criteria in column B and C, so if you added another value to the list and entered the criteria it would appear in the corresponding named range
Solved by G. B. in 57 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
15/08/2018 - 07:23
Hello welcome to Got it Pro.
Excelchat Expert
15/08/2018 - 07:24
How may I help you today?
Excelchat Expert
15/08/2018 - 07:24
I have seen your problem statement but cant see any data in excel.
Excelchat Expert
15/08/2018 - 07:26
are you there?
User
15/08/2018 - 07:26
hi there
Excelchat Expert
15/08/2018 - 07:26
Yeah
Excelchat Expert
15/08/2018 - 07:27
Please explain your problem so that I can go ahead and solve it.
User
15/08/2018 - 07:27
ill quickly add some info into the sheet
Excelchat Expert
15/08/2018 - 07:27
ohk
Excelchat Expert
15/08/2018 - 07:32
Let me know once you are done with the data and please write the problem statement as well.
User
15/08/2018 - 07:33
okay I've added all the data
Excelchat Expert
15/08/2018 - 07:33
Now what is the problem?
Excelchat Expert
15/08/2018 - 07:33
what result you want based on what?
User
15/08/2018 - 07:34
the problem I have is when I add a new exercise to the list I need to to be copied into the correct list on the named ranges sheet based on the criteria in B and C
Excelchat Expert
15/08/2018 - 07:34
I cant get it.
Excelchat Expert
15/08/2018 - 07:34
Please explain with an example
User
15/08/2018 - 07:35
ie if I added exercise 11 that was a lunge and eccentric it would appear in F6 in the named range column
Excelchat Expert
15/08/2018 - 07:35
based on what?
Excelchat Expert
15/08/2018 - 07:35
where are you adding the excercise- in sheet 1?
User
15/08/2018 - 07:36
based on the criteria in columns b and on the exercise list sheet
Excelchat Expert
15/08/2018 - 07:36
ohk, SO you added Exercise 11 in sheet 01 with associated data in column B and C.
User
15/08/2018 - 07:36
yeah
Excelchat Expert
15/08/2018 - 07:36
so it should automatically fall in name range sheet under the defined list from colmn B
Excelchat Expert
15/08/2018 - 07:37
what is column C in sheet 1?
Excelchat Expert
15/08/2018 - 07:37
ok, got it
Excelchat Expert
15/08/2018 - 07:37
you have defined that also in sheet 2
User
15/08/2018 - 07:37
B and C are both criteria
Excelchat Expert
15/08/2018 - 07:37
let me help it.
User
15/08/2018 - 07:37
for the exercises
Excelchat Expert
15/08/2018 - 07:39
Do you have any other values as your criteria as I entered in Col E and Col F
User
15/08/2018 - 07:39
nope
Excelchat Expert
15/08/2018 - 07:39
ok
User
15/08/2018 - 07:41
will this be able to be scaled up down the line?
Excelchat Expert
15/08/2018 - 07:41
yeah
User
15/08/2018 - 07:41
eg adding more unique values
User
15/08/2018 - 07:42
alright
Excelchat Expert
15/08/2018 - 07:42
Yeah you can always expand
Excelchat Expert
15/08/2018 - 07:43
You typed all data
User
15/08/2018 - 07:44
could I attach the main excel sheet
User
15/08/2018 - 07:44
with every category and exercise?
Excelchat Expert
15/08/2018 - 07:44
will be better if you send me one
User
15/08/2018 - 07:45
how can I do that?
Excelchat Expert
15/08/2018 - 07:45
There is an pin sign you can see here where you are typing
Excelchat Expert
15/08/2018 - 07:45
click on that and attach the file
Excelchat Expert
15/08/2018 - 07:45
and send
User
15/08/2018 - 07:46
ahh never mind I can't attach it as the file is too large
Excelchat Expert
15/08/2018 - 07:47
then may be you can copy sample data and send or otherwise I will create logic for you here and you can copy to your own sheet
User
15/08/2018 - 07:47
the data here is similar
Excelchat Expert
15/08/2018 - 07:47
then it will work fine
User
15/08/2018 - 07:48
brilliant
Excelchat Expert
15/08/2018 - 07:48
wait, let me solve this .. there is data discrepancies here.. you put spaces at some places and some are without spaces.
User
15/08/2018 - 07:49
alright
Excelchat Expert
15/08/2018 - 07:55
would you mind if I create it in excel and send you
User
15/08/2018 - 07:55
yeah not a problem
Excelchat Expert
15/08/2018 - 07:55
google sheets really behaves awksword sometime
Excelchat Expert
15/08/2018 - 07:55
let me do it at my local excel
Excelchat Expert
15/08/2018 - 07:55
and you need it in two sheets or single sheet will work fine for you?
User
15/08/2018 - 07:56
on my version I have all of the lists and named ranges in a master sheet and then the exercise categories are in another sheet
Excelchat Expert
15/08/2018 - 07:56
ok
User
15/08/2018 - 07:57
how can you send to me? via email?
Excelchat Expert
15/08/2018 - 07:57
na I will attach here.
Excelchat Expert
15/08/2018 - 07:58
Dont worry that I will do.
User
15/08/2018 - 07:58
you can do in excel in 5 minutes?
User
15/08/2018 - 07:58
this has taken me week s
Excelchat Expert
15/08/2018 - 07:58
:)
Excelchat Expert
15/08/2018 - 07:58
Logic if you create then excel handles rest
User
15/08/2018 - 07:59
what if the time run out
Excelchat Expert
15/08/2018 - 08:02
It wont
Excelchat Expert
15/08/2018 - 08:02
Dont worry
Excelchat Expert
15/08/2018 - 08:03
Hardly few mins more
Excelchat Expert
15/08/2018 - 08:05
Ok, so I am done
Excelchat Expert
15/08/2018 - 08:05
It is working fine
Excelchat Expert
15/08/2018 - 08:05
[Uploaded an Excel file]
Excelchat Expert
15/08/2018 - 08:05
Download the sheet and follow me.
User
15/08/2018 - 08:05
okay
Excelchat Expert
15/08/2018 - 08:05
Let me know once you open this sheet up
User
15/08/2018 - 08:06
okay ive opened
Excelchat Expert
15/08/2018 - 08:07
ok
User
15/08/2018 - 08:07
wow
Excelchat Expert
15/08/2018 - 08:07
So sheet 1 is having all the values and sheet 2 is having the solution
Excelchat Expert
15/08/2018 - 08:07
I am a genius, I know ;)
Excelchat Expert
15/08/2018 - 08:07
Follow me now
Excelchat Expert
15/08/2018 - 08:08
Open Sheet 1
Excelchat Expert
15/08/2018 - 08:08
Col B C and D you will add up manually
User
15/08/2018 - 08:08
yep
Excelchat Expert
15/08/2018 - 08:09
for Column A you just need to drag and drop below and it will populate all the values
Excelchat Expert
15/08/2018 - 08:09
COl E no need to worry about but dont delete it.
Excelchat Expert
15/08/2018 - 08:10
Now move to sheet 2
User
15/08/2018 - 08:10
ok
Excelchat Expert
15/08/2018 - 08:10
All the values under result will populate automatically based on your entry in the sheet 1
Excelchat Expert
15/08/2018 - 08:10
Now ask your doubts
User
15/08/2018 - 08:11
is there a maximum number of exercises that it will work for
Excelchat Expert
15/08/2018 - 08:11
i have considered till 25 rows but if you want you can drag more
User
15/08/2018 - 08:12
thats unreal
User
15/08/2018 - 08:12
thank you so much
Excelchat Expert
15/08/2018 - 08:12
u can drag it to any number of rows you want.
Excelchat Expert
15/08/2018 - 08:12
Did it solve your problem ?
User
15/08/2018 - 08:12
yeah I think so
Excelchat Expert
15/08/2018 - 08:12
Thank you so much for joining me today, you were talking with Jayesh.
Excelchat Expert
15/08/2018 - 08:13
you can end the session.
Excelchat Expert
15/08/2018 - 08:13
:)
User
15/08/2018 - 08:13
brilliant your a star
User
15/08/2018 - 08:13
thank you
Excelchat Expert
15/08/2018 - 08:13
Thank you.
Excelchat Expert
15/08/2018 - 08:14
Please end the session.
User
15/08/2018 - 08:15
sorry I had one more question
Excelchat Expert
15/08/2018 - 08:15
?
User
15/08/2018 - 08:16
in the excel sheet I will be creating named ranges for each
User
15/08/2018 - 08:16
lookup value,
User
15/08/2018 - 08:16
is it possible for the cells the named ranges refer to automatically update
Excelchat Expert
15/08/2018 - 08:17
if you have more name ranges... add criteria 1 and criteria 2 as I mentioned in sheet 2
Excelchat Expert
15/08/2018 - 08:17
drag the row 3 and it will populate the look up values
Excelchat Expert
15/08/2018 - 08:18
and drag the result cells as well for n number of columns (as many as you want) and it will work fine
Excelchat Expert
15/08/2018 - 08:18
and if you want to populate it automatically may be that is also possible.. but you have to raise another question for that
Excelchat Expert
15/08/2018 - 08:18
Please end the session now.
User
15/08/2018 - 08:21
ok
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.
