Excel - IF Function Problem - Expert Solution

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.

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.