Question description:
This user has given permission to use the problem statement for this
blog.
I want to format a rule in which if the amount in a cell exceeds a number, it will transwer over the rest to another row cell.
Solved by V. U. in 49 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
02/02/2018 - 01:14
Hi
Excelchat Expert
02/02/2018 - 01:14
Good day! Welcome and thank you for choosing Got It Pro-Excel.
User
02/02/2018 - 01:14
HI i need some help
Excelchat Expert
02/02/2018 - 01:14
I got your query, however I would like to clear a few points in it.
Excelchat Expert
02/02/2018 - 01:15
Yes.
User
02/02/2018 - 01:15
I want to consolidate the names
Excelchat Expert
02/02/2018 - 01:16
Okay. Kindly explain clearly your requirements.
User
02/02/2018 - 01:16
then make a rule in which if the amount in amount goes over 29,200, it will put the rest into another colum
Excelchat Expert
02/02/2018 - 01:17
Okay. In what particular cells do want that formula to be placed?
User
02/02/2018 - 01:18
for example, If i consolidate this list by names, it will put alice wang together. it would add up to 56,200. I need the amount row to not exceed 29,200 then it will put the rest, 27,200 in another row called amount 2
Excelchat Expert
02/02/2018 - 01:19
Okay. Please give me time to look into it.\
User
02/02/2018 - 01:19
thank you
Excelchat Expert
02/02/2018 - 01:20
Do we need to make another worksheet for the consolidation?
User
02/02/2018 - 01:20
no
User
02/02/2018 - 01:20
I know how to consolidate the names
User
02/02/2018 - 01:21
i do not know how to make a rule in amount that if exceeds 29,200, the rest of the amount would be put into another cell
User
02/02/2018 - 01:21
for example, when i consolidate 2 names, it will equal to 58,400
User
02/02/2018 - 01:22
then after i do this i want to make a rule saying that anything after the limit will be put into another colom cell
Excelchat Expert
02/02/2018 - 01:22
Okay. Its just simple IF statement.
User
02/02/2018 - 01:22
how do i do that?
Excelchat Expert
02/02/2018 - 01:22
Please wait. I'll write it for you.
User
02/02/2018 - 01:23
thank you
Excelchat Expert
02/02/2018 - 01:23
Please hold on.
User
02/02/2018 - 01:24
i want to limit the amount collum to 29,200 and if anything is exceeded, it will be transfered to the following row
User
02/02/2018 - 01:24
Thank you!
Excelchat Expert
02/02/2018 - 01:28
I'll send you a file.
User
02/02/2018 - 01:28
ok
Excelchat Expert
02/02/2018 - 01:30
[Uploaded an Excel file]
Excelchat Expert
02/02/2018 - 01:30
Please open it and let me explain to you how it works.
User
02/02/2018 - 01:30
ok opened
Excelchat Expert
02/02/2018 - 01:30
Col A contains the amount.
Excelchat Expert
02/02/2018 - 01:31
Col B contains the Amount 1
Excelchat Expert
02/02/2018 - 01:31
Col C contains Amount 2.
User
02/02/2018 - 01:32
ok that makes sence
Excelchat Expert
02/02/2018 - 01:32
If Column A is less than or equal to 29,200, then Col B will just copy it. And if Col A is more than 29200, Col C will have the difference of Col A and 29200.
User
02/02/2018 - 01:33
so if i make a list of 10,000 consolidation, so i need to make the formula for each one?
Excelchat Expert
02/02/2018 - 01:33
No. Just copy the formula.
Excelchat Expert
02/02/2018 - 01:34
Just drag the formula down to the last record.
User
02/02/2018 - 01:34
where do i input the formula?
Excelchat Expert
02/02/2018 - 01:34
Please wait.
User
02/02/2018 - 01:35
theres 10 formulas and they all have diffrent collums
User
02/02/2018 - 01:35
where do i input the formula?
Excelchat Expert
02/02/2018 - 01:35
A'll apply it to the online spreadsheet you uploaded.
User
02/02/2018 - 01:36
ok
Excelchat Expert
02/02/2018 - 01:36
In in the spreadsheet you have, I put the formula in I2.
Excelchat Expert
02/02/2018 - 01:37
But it seems that the spreadsheet is not yet consolidated.
Excelchat Expert
02/02/2018 - 01:37
I guess a new worksheet must be created first.
Excelchat Expert
02/02/2018 - 01:38
A worksheet that contains the sum of the amount from same contributors.
User
02/02/2018 - 01:39
look on worksheet 2 please
Excelchat Expert
02/02/2018 - 01:39
Okay.
User
02/02/2018 - 01:39
any way i can make it a rule in H?
User
02/02/2018 - 01:40
so that it sends a limit of 29,200 in I and the rest in J?
Excelchat Expert
02/02/2018 - 01:40
Okay. Please wait.
Excelchat Expert
02/02/2018 - 01:44
Okay. Please see Sheet 2.
User
02/02/2018 - 01:44
ok
Excelchat Expert
02/02/2018 - 01:45
Is it good?
User
02/02/2018 - 01:45
yes its perfect
User
02/02/2018 - 01:46
do I need to set this rule for every individual cell?
User
02/02/2018 - 01:46
or can i just set it in the row?
Excelchat Expert
02/02/2018 - 01:46
No.
User
02/02/2018 - 01:46
how can i make this a rule in the colum*
User
02/02/2018 - 01:46
my bad
Excelchat Expert
02/02/2018 - 01:46
Just pull the small square on the lower right of the selected cell.
Excelchat Expert
02/02/2018 - 01:47
In that case, you can copy the formula down to the last record.
Excelchat Expert
02/02/2018 - 01:47
Or you can just do the Copy-Paste commands.
User
02/02/2018 - 01:48
ok and it will auto go in that one?
Excelchat Expert
02/02/2018 - 01:48
Yes.
Excelchat Expert
02/02/2018 - 01:49
Are we good now?
User
02/02/2018 - 01:49
so if i copy and past comands it would not be a problem?
User
02/02/2018 - 01:49
yes thank you!
Excelchat Expert
02/02/2018 - 01:50
Yes. The formula will work on every row of your worksheet.
Excelchat Expert
02/02/2018 - 01:50
Do you have further questions?
User
02/02/2018 - 01:50
and all i have to do is just copy and past it in every cell?
User
02/02/2018 - 01:50
when i copy and past it, the H# stays the same
Excelchat Expert
02/02/2018 - 01:51
Yes. But you're not gonna do that one by one.
User
02/02/2018 - 01:51
then how do i just do it in the whole row? sorry im not good at this
Excelchat Expert
02/02/2018 - 01:52
Please wait. I'l make screenshots for you.
User
02/02/2018 - 01:53
im trying to figure out how to make it a rule for everyone in the colum instead of doing it cell by cell sorry
Excelchat Expert
02/02/2018 - 01:55
[Uploaded an Excel file]
Excelchat Expert
02/02/2018 - 01:55
Please see the image.
User
02/02/2018 - 01:57
ok i clicked on it
User
02/02/2018 - 01:57
where do i paste the forumula?
User
02/02/2018 - 01:58
and what forumula?
User
02/02/2018 - 01:58
since the amount 1 and 2 have 2 diffrent ones
Excelchat Expert
02/02/2018 - 01:59
Yes. They have different formula. They are not shown, but when you select those cells with formula and drag the FILL HANDLE down the formula will be automatically copied to the cells below.
User
02/02/2018 - 02:00
ok
User
02/02/2018 - 02:00
where do i paste the formula?
Excelchat Expert
02/02/2018 - 02:01
For Amount1, it is initially attached to Cell I2. For Amount2, it is initially attached to Cell J2.
Excelchat Expert
02/02/2018 - 02:02
Select I2 and J2, then drag the FILL HANDLE and then the formula attached to those cells will be automatically pasted to the cells below.
User
02/02/2018 - 02:02
ok that makes so much
User
02/02/2018 - 02:02
that is actually really awesome!
User
02/02/2018 - 02:02
thank you so much!
User
02/02/2018 - 02:02
Hope you have an amazing day!
Excelchat Expert
02/02/2018 - 02:02
Wow. I am happy to help you.
User
02/02/2018 - 02:03
also thank you for being patient with me!
Excelchat Expert
02/02/2018 - 02:03
Thank you so much for your cooperation. Please give your honest rating on our service. I hope to have you again in Got It Pro-Excel. Have a good day ahead.
User
02/02/2018 - 02:03
5/5!!!
Excelchat Expert
02/02/2018 - 02:03
No problem. Thank you again.
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.
