Hello

I'm trying to develop a Project in Excel 2010



I Want to Link 2 Tables in different Sheets.

Table in Sheet 2, has Information about a group o People set on the Time()+90 Expiring Date (highlight in Red by Excel conditional formatting).

Table in Sheet 1, should alert the User to when the Date is in Expiring Range.

The problem that I can see, is that the Array is set on a Column that has a Formula (IF Statement), and no value in the Cell.

In My Attachment, i have different solutions that I'm working on...

Sheet 2 has the Table that the User will from Time to Time inputa data

Sheet 1 has 2 Tables with different work ideias. The first goes straight to Tabel in Sheet 2 but does not exclude empty cells. The Second Table, I'm trying to make an Array that exclude cells in Sheet 2 that do not have expiring dates

I'd appriciate if somebody could give me a hand, Please!

Best Regards

Attached Files

Book1_12.xlsx (15.3 KB, 9 views)

Why have separate tables at all? Just filter the table by expiration date. If you absolutely must have two tables, just have one mirror the other and apply a filter there.

Thanks gebobs, that's a great and simple ideia... but if I can, I want a table that doesn't need Human interaction. For that I have the table in Sheet 2

"If(ROWS($G$6:G6)<COUNTA($B$6:$B$21);"";INDEX($B$6 :$B$21:AGGREGATE(15;6;(ROW($B$6:$B$21)-ROW(($B$6)+1<>"");ROW($G$6:G6)))))"

I think that the formula is the right one (I can't see the mistake inside the Formula)... But it doesn't display the information, don't know if the problem is that the cells on B6 to B21 have another Formula and no value.

Sorry, I can't help. Your formula is greek to me and I can't parse it. I passed this along to another person and he said the same thing. I will try my best to figure it out. Until then, hopefully someone else will help out.

So your formula...the table it's in seems to require the table to the left. Is that what you intend?

Let's go about this bit by bit. First, your if condition:

If(ROWS($G$6:G6)<COUNTA($B$6:$B$21)

If I'm reading this right, if the row number in the right table is less than the number of entries in the left table (in this example, 4), right? That being it should return a blank (the true result) for the first three rows. Only once the row number equals 4, thus equalling the count, will your formula (the false result) be calculated.

Is that right or is it backward? I would think you would only want it for the first four rows i.e. >=.

Assuming that, it lookslike COUNTA is not going to work since it will count cells with formulas regardless if they return a result. So I tried this:

=IF(ROWS($G$6:G6)>=ROWS($B$6:$B$21)-COUNTBLANK($B$6:$B$21) ...

I tried that and it get's the first and the third names, but misses the other two.

Progress? Um, maybe.