Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup and autofill
Can i use autofill to increase the column count it looks in?
On row 1 for example I have =vlookup($b3,sheet1!$a:$cg,5,0) and i then want to increment it by 3 in the next column i.e. =vlookup($b3,sheet1!$a:$cg,8,0) and then =vlookup($b3,sheet1!$a:$cg,11,0) and so on. Thanks in your advance for your help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup and autofill
That used to bug me as well.
What you need to do is set up something so that an autofill will work across as well as down. So instead of =vlookup($b3,sheet1!$a:$cg,5,0) you will have =vlookup($b3,sheet1!$a:$cg,c$1,0) drag this across and down and it will be d$1, e$1 etc etc then put value 5 in cell d1, and in cell e1 put =d1+3, and drag that across to the right. HTH -- Allllen "LostwithoutLost" wrote: Can i use autofill to increase the column count it looks in? On row 1 for example I have =vlookup($b3,sheet1!$a:$cg,5,0) and i then want to increment it by 3 in the next column i.e. =vlookup($b3,sheet1!$a:$cg,8,0) and then =vlookup($b3,sheet1!$a:$cg,11,0) and so on. Thanks in your advance for your help |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup and autofill
As a P.S.
I have tried replacing the 5 at the end with COLUMN(E1),0 but the auto fill only increases the increment by 1 Thnaks "LostwithoutLost" wrote: Can i use autofill to increase the column count it looks in? On row 1 for example I have =vlookup($b3,sheet1!$a:$cg,5,0) and i then want to increment it by 3 in the next column i.e. =vlookup($b3,sheet1!$a:$cg,8,0) and then =vlookup($b3,sheet1!$a:$cg,11,0) and so on. Thanks in your advance for your help |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup and autofill
Excellent, I will give that a blast
thanks "Allllen" wrote: That used to bug me as well. What you need to do is set up something so that an autofill will work across as well as down. So instead of =vlookup($b3,sheet1!$a:$cg,5,0) you will have =vlookup($b3,sheet1!$a:$cg,c$1,0) drag this across and down and it will be d$1, e$1 etc etc then put value 5 in cell d1, and in cell e1 put =d1+3, and drag that across to the right. HTH -- Allllen "LostwithoutLost" wrote: Can i use autofill to increase the column count it looks in? On row 1 for example I have =vlookup($b3,sheet1!$a:$cg,5,0) and i then want to increment it by 3 in the next column i.e. =vlookup($b3,sheet1!$a:$cg,8,0) and then =vlookup($b3,sheet1!$a:$cg,11,0) and so on. Thanks in your advance for your help |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup and autofill
try a helper column 5, 8 11
vlookup($b3,sheet1!$a:$cg,c1,0) -- Don Guillett SalesAid Software "LostwithoutLost" wrote in message ... As a P.S. I have tried replacing the 5 at the end with COLUMN(E1),0 but the auto fill only increases the increment by 1 Thnaks "LostwithoutLost" wrote: Can i use autofill to increase the column count it looks in? On row 1 for example I have =and i then want to increment it by 3 in the next column i.e. =vlookup($b3,sheet1!$a:$cg,8,0) and then =vlookup($b3,sheet1!$a:$cg,11,0) and so on. Thanks in your advance for your help |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup and autofill
That worked a treat thanks guys.
Regards Chris "LostwithoutLost" wrote: Excellent, I will give that a blast thanks "Allllen" wrote: That used to bug me as well. What you need to do is set up something so that an autofill will work across as well as down. So instead of =vlookup($b3,sheet1!$a:$cg,5,0) you will have =vlookup($b3,sheet1!$a:$cg,c$1,0) drag this across and down and it will be d$1, e$1 etc etc then put value 5 in cell d1, and in cell e1 put =d1+3, and drag that across to the right. HTH -- Allllen "LostwithoutLost" wrote: Can i use autofill to increase the column count it looks in? On row 1 for example I have =vlookup($b3,sheet1!$a:$cg,5,0) and i then want to increment it by 3 in the next column i.e. =vlookup($b3,sheet1!$a:$cg,8,0) and then =vlookup($b3,sheet1!$a:$cg,11,0) and so on. Thanks in your advance for your help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
No autofill VLookup function | Excel Worksheet Functions | |||
VLOOKUP Changing reference cells in autofill | Excel Worksheet Functions | |||
Using VLOOKUP in VBA code | Excel Worksheet Functions | |||
Autofill Formula | Excel Discussion (Misc queries) | |||
Numbers stored as text causes problem with VLOOKUP | Excel Worksheet Functions |