copy a cell value based on condition Solved

Question

Hello,

I'm reaching out to you because I'm stuck on creating a macro.

I have a table with the following columns:

- the number of employees in the company
- workforce categories
- contract amounts

I want to test the amount in each cell for each workforce category, and based on its value, there are two solutions:

- if its value does not exceed a value x, we copy this value into a fourth column
- if its value exceeds value x, we write value x in the fourth column

Unfortunately, I don’t know how to do this. I spent the morning on it and I'm lost. My macros do nothing or crash... I’m starting to get frustrated, I must admit.

Could you help me?

Thank you very much,

McFly

Configuration: Windows 7 Firefox 3.0.17

Mike-31 · Answer

Hi,  Why a macro for that? If your data is in columns A, B, and C, for instance, the sums in C and for row 2 in cell D2 this formula  =IF(C2

McFly · Answer

Well, actually, it's more complicated.

Let's say I have 6 classes of sizes: [0-9], [10-29], [30-49], [50-99], [100-199], and [200-250]
For each class size, I have a maximum amount not to exceed.

[0-9] ==> 14840
[10-29] ==> 29191
[30-49] ==> 40000
[50-99] ==> 47406
[100-199] ==> 50279
[200-250] ==> 76185

I have a base of about 550 rows. I need to test the cell where I have my class sizes, once that's done, I test the amount, and based on the class size, I check if this amount exceeds my ceiling. Then, I either keep the initial amount or replace it with my ceiling.

With an IF formula, I don’t see how to do it. It would be too complex, right?
That's why I turned to VBA actually, even if my level limits me at this time :D.

Mike-31 · Answer

Re,

Which column are the values [0-9],[10-29],[30-49],[50-99],[100-199] and [200-250] in?

Can there be values less than 0 and greater than 250?

Then, are the values 14840, 29191, 40000, 47406, 50279, 76185 fixed values or recorded in cells, and which ones?

And finally, in which column are the values to compare with the latter?

A+
Mike-31

A period of failure is a dream moment to sow the seeds of knowledge.

McFly · Answer

Hi Mike.

My class sizes are in column A and my maximum amounts (14480, 29191, etc.) are entered in a small table on another tab (but nothing prevents me from bringing it back to this tab).

As for negative values or those above 250, neither case is possible.

The values to compare (i.e., the actual amounts) with the maximum amounts are in column C.

Thanks for taking the time to help me.

McFly

Mike-31 · Answer

Hi,

Check the attached file with the link below, in column A you enter the class and in B the amount.
I suggest several formulas where the reference amounts are on the same sheet column K or on another sheet, for example, sheet 2 column B.

https://www.cjoint.com/?bksP0mtQka

--
Best regards,
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.

McFly · Answer

Oh yeah!!! Those formulas you came up with are impressive :D

Listen, it looks like it fits what I want, thank you very much. I think I'll use the formula in column D then. By the way, I have a question, can I drag this formula without any issues?
Another question, can I use it on Excel 2002?

Anyway, a big thank you for your help. You've taken a huge weight off my shoulders :)

Mike-31 · Answer

Re,

On Excel 2002 there shouldn't be any problem; if you leave the reference values on another sheet, I advise you to name each cell (Insert(Name/Define) for the first one, in my example I named it class1, the second class2, etc.

Explanation of the formula, it processes the maximum value to finish with the minimum value check.
For each group, the formula is =IF(AND(A2>=200;B2>class6);class6;

which clearly sets a double condition, the value must be greater than or equal to the last class IF >=200 but also AND> to the reference value, so display the reference value, otherwise check the next class, etc. up to the last class, and otherwise, at the end of the formula, if in none of the cases the value is not greater than the reference value according to its class, display the value in B

When you talk about dragging the formula, I think you mean incrementing, so yes of course, thanks to absolute references (when you want to refer to a fixed cell we say absolute, we place it between dollar signs $B$2 for example or we name the cell

If your expectations are met, don't forget to set your status to resolved

Thank you
A+
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.

McFly · Answer

Thank you for this explanation. You've also introduced me to the "AND" function which seems VERY handy apparently :)

However, I don't know how to mark my subject as Resolved.... :/

Thanks again.

McFly

Mike-31 · Answer

Re,

To set the status to resolved, you need to click on resolved at the top of the discussion and then OK. If you can't do it, I will do it for you tonight.
--
A+
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.

Mike-31 · Answer

My mistake, you can't do that, you need to be a member, register if you need to intervene another time.
I will mark your status as resolved
--
A+
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.

Copy a cell value based on condition

10 réponses

Pixma mx495 printer error code b204 and still under warranty

Texture issues in enshrouded

Canon mb5350 - cartridge replacement impossible

Have you ever been scammed by justlo?

Nexplay reviews

How to cancel adopte un maturé subscription

Non-functioning rj45 sockets

Sbc ru 252 universal remote code

Chicsoso reviews - is the site reliable?

My text disappears when i type on the keyboard?