PDA

View Full Version : Strings and Variables



aimatarv
05-05-2016, 05:38 AM
Please provide some insight on this issue I am having: In the image you will see the word "vlookup" in column "J". That is where I need the vlookup formula to be. The first "vlookup" should read the formula:
=VLOOKUP($B14, 'Table 1'!$B$10:$P$47, 15, FALSE)"

the second one in row 21 would read "=VLOOKUP($B21, 'Table 1'!$B$10:$P$47, 15, FALSE)"

So essentially the only thing that is changing is the lookup value which is changed by the range. The column is staying the same however the row is not.

Thing to note:

There are more than 1 associates, in fact there are 200+ and it varies so whatever is on the screen will not be the same week-to-week.


I tried to write the the vlookup formula in a string in vba within a loop, however when it comes to factoring in the row variable I run into issues. I tried to concatenate but I quickly found out I con-cate-not (sorry for bad pun :( ).

16097

samuelimtech
05-05-2016, 05:57 AM
Hi,

sorry its not clear what you want the formula to do?
its looking for some value (Bx) in table1 column B and returning the value 15 columns to the right, is that correct?

if so what isnt the formula doing?

Kenneth Hobs
05-05-2016, 07:16 AM
It is easier to help with a sample file. Obfuscate sensitive data.

I wonder if you need a VBA solution? Just add a formula to do it and fill down. That takes about 30 seconds. I would add an IF($E14<>"Totals","",VLOOKUP($B14, 'Table 1'!$B$10:$P$47, 15, FALSE)")

If you want to do it in VBA and you data is consistent (same offsets) then just use your Do Loop from your other thread and add a Formula string. Use ROW() to get the row number.

aimatarv
05-05-2016, 12:42 PM
Hi,

sorry its not clear what you want the formula to do?
its looking for some value (Bx) in table1 column B and returning the value 15 columns to the right, is that correct?

if so what isnt the formula doing?


I only need the lookup value (the first argument of the 4 in a Vlookup) to be a variable which returns the location in which it will always be column "B" but the row will always change. So Bx. Every other argument will stay the same and will not change.


16102

aimatarv
05-05-2016, 12:45 PM
I can absolutely do that with the fill down option. It works, however there I would like to have it cleaner so I don't have those #N/A values. I would also like to learn how if what I stated in the post is even possible so I can know for the future :)

Kenneth Hobs
05-05-2016, 12:56 PM
As I said, use =IF and/or =IFERROR. This is standard practice.