I want to autofill this formula in a column

VLOOKUP(40725,Stats,2,FALSE)

40725 is a date which I want to increment by 1, Stats is a cell range. 2 is a column in the cell range

✔ Best Answer

If you want to increment your "dates" (40725) by 1 as you drag the formula down, take advantage of the ROW() function: ROW() returns the Row number of whatever Row it is used in.

In any cell in Row 1, use this and drag it down, since 40724 + 1 = 40725

=VLOOKUP(40724+ROW(), Stats, 2, FALSE)

If you need to start your formula in a Row other than 1, simply subtract the starting Row number from 40725, e.g.

In any cell in Row 5, use this and drag it down, since 40720 + 5 = 40725

=VLOOKUP(40720+ROW(), Stats, 2, FALSE)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Try using a helper column: Column A will have your dates

Make A1 your first date: 40725,

autofill down as many rows as needed.Enter the formula in column B: =VLOOKUP(A1,Stats,2,False)

Drag down the formula as many rows as needed.MIKE

unfortunately the data is imported from an input sheet,and is then sorted descending thus the cell numbers change. therefore the Cell references will always be incorrect.The VLOOKUP finds the date in the column and brings forward the data fine.

Thanks for the reply though.

Please understand that sitting here, I have no idea what your spreadsheets look like or are supposed to do. That being said, your reply makes no sense to me.

If you would care to explain a bit more and possible post an example of your spread sheet, perhaps we can find a solution.

MIKE

If you want to increment your "dates" (40725) by 1 as you drag the formula down, take advantage of the ROW() function: ROW() returns the Row number of whatever Row it is used in.

In any cell in Row 1, use this and drag it down, since 40724 + 1 = 40725

=VLOOKUP(40724+ROW(), Stats, 2, FALSE)

If you need to start your formula in a Row other than 1, simply subtract the starting Row number from 40725, e.g.

In any cell in Row 5, use this and drag it down, since 40720 + 5 = 40725

=VLOOKUP(40720+ROW(), Stats, 2, FALSE)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thankyou, All is resolved.

Cheers

Ask Your Question

Weekly Poll

Do you think Jony Ive could make a big impact on Airbnb?

Discuss in The Lounge

Poll History