Solved excel autofill formula

July 13, 2011 at 15:35:41
Specs: Windows XP
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

See More: excel autofill formula

Report •


✔ Best Answer
July 13, 2011 at 21:12:13
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.



#1
July 13, 2011 at 16:00:00
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

http://www.skeptic.com/


Report •

#2
July 13, 2011 at 16:19:46
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.

Report •

#3
July 13, 2011 at 17:20:54
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

http://www.skeptic.com/


Report •

Related Solutions

#4
July 13, 2011 at 21:12:13
✔ 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.


Report •

#5
July 14, 2011 at 13:00:51
Thankyou, All is resolved.
Cheers

Report •

Ask Question