HELP: IF Statement between Dates for Reverse Index failing


Y

Yunus

Hi

To create different condition using a working Formulae I have
substituted IF('SHEET1'!$K$5:$BT$138<=$IQ$1 with IF(AND('SHEET1'!$K$5:
$BT$138>$IQ$1,'SHEET1'!$K$5:$BT$138<=$IS$1) but it then corrupts the
formulae. What am I doing wrong. I Use CTRL, SHIFT, ENTER.

In Sheet2 Cell IQ1 is =NOW()
In Sheet2 Cell IQ2 is =COUNTIF('SHEET1'!K5:BT138, "<"&TODAY())
In Sheet2 Cell IS1 is =TODAY() +14
In Sheet2 Cell IS2 is = COUNTIF('SHEET1'!K5:BT138, "<"&TODAY() +14)

The following Formulae appears to work for IF Condition >IQ1.

In Sheet2 Cell B5:B150
=IF(ROWS($B5:B5)>$IQ$2,"",INDEX('SHEET1'!$B$5:$B$138,INT(SMALL(IF
('SHEET1'!$K$5:$BT$138<=$IQ$1,(ROW('SHEET1'!$K$5:$BT$138)-ROW('SHEET1'!
$K$5)+1)*10^5+COLUMN('SHEET1'!$K$5:$BT$138)-COLUMN('SHEET1'!$K$5)),ROWS
($B5:B5))/10^5)))

In Sheet2 Cell C5:C150
=IF(ROWS($B5:B5)>$IQ$2,"",INDEX('SHEET1'!$K$3:$BT$3,MOD(SMALL(IF
('SHEET1'!$K$5:$BT$138<=$IQ$1,(ROW('SHEET1'!$K$5:$BT$138)-ROW('SHEET1'!
$K$5)+1)*10^5+COLUMN('SHEET1'!$K$5:$BT$138)-COLUMN('SHEET1'!$K$5)+
1),ROWS($B5:B5)),10^5)))

YET this set of Formula for IF Condition between Dates IQ1 & IS1 does
not work.

In Sheet2 Cell I5:I150
=IF(ROWS($I$5:I5)>$IQ$2,"",INDEX('SHEET1'!$B$5:$B$138,INT(SMALL(IF(AND
('SHEET1'!$K$5:$BT$138>$IQ$1,'SHEET1'!$K$5:$BT$138<=$IS$1),(ROW
('SHEET1'!$K$5:$BT$138)-ROW('SHEET1'!$K$5)+1)*10^5+COLUMN('SHEET1'!$K$5:
$BT$138)-COLUMN('SHEET1'!$K$5)),ROWS($I$5:I5))/10^5)))

In Sheet2 Cell J5:J50
=IF(ROWS($I$5:I5)>$IQ$2,"",INDEX('SHEET1'!$K$3:$BT$3,MOD(SMALL(IF(AND
('SHEET1'!$K$5:$BT$138>$IQ$1,'SHEET1'!$K$5:$BT$138<=$IS$1),(ROW
('SHEET1'!$K$5:$BT$138)-ROW('SHEET1'!$K$5)+1)*10^5+COLUMN('SHEET1'!$K$5:
$BT$138)-COLUMN('SHEET1'!$K$5)+1),ROWS($I$5:I5)),10^5)))

Can some one please help.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top