Showing Custom Value Based on the Condition of Dynamic Table

[DocAElstein]

Hi,
This is probably a question that is half a VBA question and half a bit of logical thinking.
I don’t think it is difficult, but just requires a bit of tedious coding.

I will give you some ideas, first. Tomorrow, if you still need help, I will give you a solution, based on the logic below:

---

My initial thinkings
My logical thinking is not necessarily the best. I am sure if you think about this problem long enough then you can come up with lots of different ways to do it .

My initial thinking is that you build up an array ( list ) of flags for each row. Anything will do, like for example a 1 could indicate that student was decreasing in that row .
Then you would loop through that array ( list ) , and build up the string to be pasted out.

To explain that pictorially : I am talking about producing something like this shown in yellow:

ENGLISH	50	45	30	20	15		1
HINDI	45	60	40	50	65		0
MATHS	70	55	40	25	10		1

That yellow column would not necessarily be in a spreadsheet. It could be built up in an array in the coding, ( pseudo like: my1sArr() = {1,0,1} ) , and that array then used in a loop internally to build up the string to be pasted out to A1.
That is just my initial thinking.
Producing an array or list of those 1’s can easily be done with some VBA coding.
Routinely when I would do such coding, that list would be produced in a way that it does not rely on a fixed table size. It is typical in such coding to calculate first the current Last row and last column. ( Typically in my codings I use the Long type variables, Lr and Lc

You want all this to happen automatically I assume, so you would need to have the coding in the worksheet event macro , Private Sub Worksheet_Change(ByVal Target As Range)
The initial code lines would typically be those to restrict the macro to running when you select within the table range. The very first code lines would need to determine the table range based on top left of B4 and bottom right based on a on determining Lr and Lc



I expect also that your problem could be solved by a clever formula. I have seen some very clever formula solutions at excelforum.com and mrexcel.com and eileenslounge.com to solve similar problems to yours. But I have no experience with formulas.

I will first have time again tomorrow to help further. If you do not get a solution in the meantime, then I will give you a macro to match the logic I described above

Alan

---

P.S.
If you want to make a start on a macro following my logic, then the first few code lines would be those required to calculate Lr and Lc
Those are the ones that typically take a form , pseudo like similar to
Lr = _.Range ( _ , _.Rows.Count ) .End(xlup) .Row
Lc = _.Cells ( _ , _.Column.Clount ) .End(xlToLeft) .Column

The next bit is to restrict the coding to work on the table range