How to run IF function in a loop or any other solutions?

[shalomfern]

I want to refer to the last date of the data set and make the cell value move UP until cell value is equal to text or in this case "BRL"

If it is text, I want excel to run the formula in BLUE, otherwise I want excel to move up the column until it finds a text.

=IF(ISTEXT(A9); VLOOKUP('Transport Rates'!$A6; MDshowrooms!$C$5:$F$30; 4); OFFSET(A11; -1; 0; 1; 1))

but with this formular, it only moves once. it doesnt keep checking the new cell value.


[TABLE="width: 102"]
<tbody>[TR]
[TD]BRL[/TD]
[/TR]
[TR]
[TD]09.01.2010[/TD]
[/TR]
[TR]
[TD]02.02.2010[/TD]
[/TR]
[TR]
[TD]14.02.2010[/TD]
[/TR]
[TR]
[TD]16.02.2010[/TD]
[/TR]
[TR]
[TD]17.03.2010[/TD]
[/TR]
[TR]
[TD]04.04.2010[/TD]
[/TR]
[TR]
[TD]07.04.2010[/TD]
[/TR]
[TR]
[TD]19.04.2010[/TD]
[/TR]
</tbody>[/TABLE]

 

[synergy16]

I think this should work for you
assuming column A is the column you want the rows to change in:

for i = 9 to (what ever row you want to check until)
=IF(ISTEXT("A" & i); VLOOKUP('Transport Rates'!$A6; MDshowrooms!$C$5:$F$30; 4); OFFSET(A11; -1; 0; 1; 1))
next

 

[shalomfern]

I should have mentioned earlier, I suck at macros or advanced excel

it would be a great help if you can guide me where to include the formula you mentioned

thanks for you help