ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Validation Data (https://www.excelbanter.com/excel-worksheet-functions/84111-validation-data.html)

JorgeAE

Validation Data
 
I am using a validation data formula, initialy I have it set up to use only the range (A1:A24) and (B1:B24) now I want to expand the range to (B1:B30) (A1:A30)
Initial formula:

=IF(ROW(A1:A24)-ROW(A1)+1COUNT(B1:B24),"",INDEX(A:A,SMALL(B1:B24, ROW(INDIRECT("1:"&ROWS(A1:A24))))))

When I go to the data validation window and modify the formula as follows:

=IF(ROW(A1:A30)-ROW(A1)+1COUNT(B1:B30),"",INDEX(A:A,SMALL(B1:B30, ROW(INDIRECT("1:"&ROWS(A1:A30))))))

There is not changes the range still reads the initial formula and the new rows
read as A48 , A49....

I would like to know what should I do to modify my data as the second formula.


All times are GMT +1. The time now is 12:31 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com