Hi,
I have a formula which works, although it takes too long to update. Am using it to update approx 100 000 rows of data.
I have tried to turn automatic calculation off and manually calculate and replace with values, however still not helping.
Any better way of achieving the same result with a quicker calculation speed.
Thanks
JP
{=INDEX(Sheet1!$A:$C,MATCH(1,(ROW($A1)>=Sheet1!$B:$B)*(ROW($A1)<=Sheet1!$C:$C),0),1)}
Original problem as follows:
Sheet 1:
Column A = Ref Number, Column B = Row Start Number, Column C = Row End Number
ie. ROW 1 Column A ='BTA340', Column B = '1', Column C = '250'
ROW 2 Column A ='ALP340', Column B = '251', Column C = '1250'
I need Sheet 2 to display as follows
ROWS 1 - 250 Column A should read 'BTA340' for all 250 rows
ROWS 251 - 1250 Col Column A should read 'ALP340' for all 1000 rows
Please help!!
I have a formula which works, although it takes too long to update. Am using it to update approx 100 000 rows of data.
I have tried to turn automatic calculation off and manually calculate and replace with values, however still not helping.
Any better way of achieving the same result with a quicker calculation speed.
Thanks
JP
{=INDEX(Sheet1!$A:$C,MATCH(1,(ROW($A1)>=Sheet1!$B:$B)*(ROW($A1)<=Sheet1!$C:$C),0),1)}
Original problem as follows:
Sheet 1:
Column A = Ref Number, Column B = Row Start Number, Column C = Row End Number
ie. ROW 1 Column A ='BTA340', Column B = '1', Column C = '250'
ROW 2 Column A ='ALP340', Column B = '251', Column C = '1250'
I need Sheet 2 to display as follows
ROWS 1 - 250 Column A should read 'BTA340' for all 250 rows
ROWS 251 - 1250 Col Column A should read 'ALP340' for all 1000 rows
Please help!!