Jump to another cell if reached the limit

[Petijandro]

Hello Excel pros!

I have a problem and couldn't figure it out.
There are 3 different types of assets (A,B,C)
On Sheet2 there are limits for each assets for every month(period)
So on the B2 you can see the asset's name,
C2 = the limit for the first month
D2 = the assets currently in use
E2 = Period (month) 1 = January, 2 = February ...

What I'm trying to do is..
If I put something on Sheet1 ie. the given example, it should update the Sheet2's "current" column.
And if it exceeds the given limit it should add it to the next month's current cell.

So on Sheet2, the B asset has 2 used assets but B has a 0 limit for the 1st period which means it should add it to the 2nd Period.


abc.xlsx

[ARGK]

Hi
I would strongly recommend using Data Validation here as it is easier to maintain than VBA.
I have enclosed a sample, have a look at the Data Validations setting and see if this gives you what you need.
Good luck.
Tony

[Petijandro]

Hey,

I couldn't figure out what your sample does, I checked the data validation, but didnt see anything. I think you misunderstood me. What I'm trying to do is:

If 9 used A assets was put in the table and in the other sheet the limit is 7 for the A asset so it should put 7 to that particular period and 2 to the next period.

Like this:
Sheet 2 has been filled out according to that 3 rows in Sheet 1.

Sheet1!
Description Used Date
1 A 9 2013.01.01
2 B 2 2013.01.02
3 C 1 2013.01.15

Sheet2!
Description Limit Current Period
1 A 7 7 1
2 B 0 0 1
3 C 7 1 1
4 A 8 2 2
5 B 2 2 2