Question description:
This user has given permission to use the problem statement for this
blog.
I need a range of numbers to become the number in the next column
Solved by D. F. in 47 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
14/08/2018 - 05:30
Hello
Excelchat Expert
14/08/2018 - 05:30
Welcome
User
14/08/2018 - 05:30
Thank you
Excelchat Expert
14/08/2018 - 05:31
Please show me an example of what you need on the blank spreadsheet.
User
14/08/2018 - 05:31
Well I need a formula to create a range of numbers into one number
User
14/08/2018 - 05:32
for example the weights for 145-160 is size 125
User
14/08/2018 - 05:32
so if i put in 146 i want 125 to pop up in the next row
Excelchat Expert
14/08/2018 - 05:33
I'm sorry, but that doesn't explain much yet.
User
14/08/2018 - 05:33
What other information do you need?
Excelchat Expert
14/08/2018 - 05:33
Do you have a file?
User
14/08/2018 - 05:34
well i have a table that i already have a macro for to put in alphabetical order but i need a formula to show what size each weight is
Excelchat Expert
14/08/2018 - 05:34
Actually I don't need anything else if the problem is just turning numbers from 145-160 in a cell to 125 in another cell. But I presume that is not all of the problem.
User
14/08/2018 - 05:34
and because it has a macro it wouldn't let me upload it
User
14/08/2018 - 05:35
well i have more than one range that goes to a size
Excelchat Expert
14/08/2018 - 05:36
Please show me the table, you can just copy/paste from your file to the blank sheet on the right.
Excelchat Expert
14/08/2018 - 05:36
You can also remove the macro temporarily and then upload the xlsx file.
User
14/08/2018 - 05:38
that is the ranges
[Uploaded an Excel file]
Excelchat Expert
14/08/2018 - 05:38
Thank you, now I have clear idea of what you need.
User
14/08/2018 - 05:38
that excel that got posted is not correct
User
14/08/2018 - 05:38
what i typed on the google one is correct
Excelchat Expert
14/08/2018 - 05:38
Understood.
Excelchat Expert
14/08/2018 - 05:40
Now I need to know what would you have as input.
Excelchat Expert
14/08/2018 - 05:41
Is it something like what I wrote on column F and G?
User
14/08/2018 - 05:41
yes, this is what i have minus the macro
[Uploaded an Excel file]
Excelchat Expert
14/08/2018 - 05:42
Sorry, I don't see anything other than column headers in the file.
User
14/08/2018 - 05:43
it's a table, with the weight being put into column a and i want the size going into column b
Excelchat Expert
14/08/2018 - 05:43
Based on the table you have showed me on the preview window?
User
14/08/2018 - 05:43
correct
User
14/08/2018 - 05:44
the weight ranges are in column a and the corresponding sizes are in column b
User
14/08/2018 - 05:44
on the google sheets here
Excelchat Expert
14/08/2018 - 05:44
Weight is a single number or a range as like in the google sheets?
User
14/08/2018 - 05:45
weight will be inputted as a single number. the range is what i have posted here tho.
Excelchat Expert
14/08/2018 - 05:45
Alright, I feel like I have all info I need.
Excelchat Expert
14/08/2018 - 05:45
Thank you for the help.
User
14/08/2018 - 05:46
Sorry I had a hard time relaying the information
Excelchat Expert
14/08/2018 - 05:46
Not a problem. Everything is alright.
Excelchat Expert
14/08/2018 - 05:47
Now there are two formulas of showing the size in column B. But both will require the table. You can either choose to put the table on the same sheet or another sheet.
Excelchat Expert
14/08/2018 - 05:49
One is a very long IF formula, that can get real confusing to comprehend. The other one is a very short formula but to apply this formula we need to restructure the weight-size table.
Excelchat Expert
14/08/2018 - 05:49
Do you find me hard to understand? If so please let me know.
User
14/08/2018 - 05:49
I'm following just fine
User
14/08/2018 - 05:50
What kind of restructure would it be
User
14/08/2018 - 05:50
?
Excelchat Expert
14/08/2018 - 05:50
Let me show you..
Excelchat Expert
14/08/2018 - 05:52
Are you able to see what I wrote on column D:F?
User
14/08/2018 - 05:52
Yes
Excelchat Expert
14/08/2018 - 05:54
That is what I was talking about. Thank you for completing it.
User
14/08/2018 - 05:54
Your welcome
Excelchat Expert
14/08/2018 - 05:54
Now, let's assume weight would be inputted in column K and you want size to appear in column L automatically.
Excelchat Expert
14/08/2018 - 05:55
So, I will write a formula for that. Please give me a few minutes.
User
14/08/2018 - 05:55
Okay, thank you
Excelchat Expert
14/08/2018 - 05:58
So this is the formula in column L =VLOOKUP(K2,$D$2:$F$9,3,TRUE)
Excelchat Expert
14/08/2018 - 05:58
Now input a number in K2 and you will see size automatically changes.
Excelchat Expert
14/08/2018 - 06:01
Let me know if you like it or not and if you approve, I'll explain how it works.
User
14/08/2018 - 06:01
That's perfect
Excelchat Expert
14/08/2018 - 06:02
Great.
Excelchat Expert
14/08/2018 - 06:02
So as I have mentioned earlier, we need to setup a table of weight/size first as showed in the google sheets.
Excelchat Expert
14/08/2018 - 06:03
Once it is there, we then write the formula VLOOKUP(K2,$D$2:$F$9,3,TRUE) assuming size would be inputted in cell K2.
Excelchat Expert
14/08/2018 - 06:04
Breakdown of the formula---
Excelchat Expert
14/08/2018 - 06:04
K2 is the cell where size would be inputted.
Excelchat Expert
14/08/2018 - 06:05
D2:F9 is the range where we have weight/size table.
Excelchat Expert
14/08/2018 - 06:06
We wrote $D$2:$F$9 (wrapped the range with dollar sign) because we don't want the range to change if the formula is copied down.
Excelchat Expert
14/08/2018 - 06:06
Am I making sense?
User
14/08/2018 - 06:07
So far
Excelchat Expert
14/08/2018 - 06:08
Great to hear that. Now we wrote 3 in the formula cause there are 3 columns in weight/size table and we want the size which is in 3rd column.
Excelchat Expert
14/08/2018 - 06:09
If the size were in 2nd column of weight/size table, then we would have wrote 2.
Excelchat Expert
14/08/2018 - 06:09
Makes sense?
User
14/08/2018 - 06:09
Yes
Excelchat Expert
14/08/2018 - 06:10
And lastly we wrote TRUE because our weight/column table is sorted.
Excelchat Expert
14/08/2018 - 06:11
That would be all, please let me know if you have any question.
User
14/08/2018 - 06:11
It comes up with a circular reference warning
Excelchat Expert
14/08/2018 - 06:11
may I see what have you wrote?
User
14/08/2018 - 06:12
VLOOKUP(A3,A52:C59,3,TRUE)
Excelchat Expert
14/08/2018 - 06:12
In which cell you put this formula?
User
14/08/2018 - 06:12
A3
Excelchat Expert
14/08/2018 - 06:13
That's why.
User
14/08/2018 - 06:13
Oh okay, where do i input the formula then?
Excelchat Expert
14/08/2018 - 06:13
If A3 is the weight then you write the formula in another cell other than A3.
Excelchat Expert
14/08/2018 - 06:14
Please look at the reference google sheet. K2 contains the weight, and L2 contains the formula.
User
14/08/2018 - 06:14
Ohhh
User
14/08/2018 - 06:14
Thank you very much
User
14/08/2018 - 06:14
It's all working now
User
14/08/2018 - 06:14
That's beautiful
Excelchat Expert
14/08/2018 - 06:15
I'm glad to be able to help.
Excelchat Expert
14/08/2018 - 06:15
Thank you for you patience.
Excelchat Expert
14/08/2018 - 06:17
If you have any question, you can ask me. Otherwise please close the chat. And also consider rating me 5 star after closing the chat if you think you were benefited from my service.
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.
