NegativeKurtosis
New Member
- Joined
- Jan 14, 2018
- Messages
- 15
Hello Everyone ! Hoping some of the brilliant, creative minds here can offer some suggestions.
I'm working on an assignment and having a great deal of difficulty finding a solution that works. Here is the situation :
I have a dynamic sheet that updates every hour via a web query. I have 2 VBA macros that handle "updating" the columns within the range $I$4:$O$517 with the web query data as it comes in. The TABLE ARRAY RANGE itself DOES NOT CHANGE. I have found a hundred examples on line that explain how to handle dynamic table arrays or dynamic column index numbers but nothing on how to deal with a table array that remains static but has it's values INSIDE that table array changing. The problem is that when the data is changed within the table array $I$4:$O$517 .....ie from the web query update, my Vlookup formula does not work. Nor can I get an index / match or vlookup / If / offset / counta combination formula to work either. I want to have faith that this is not some obscure method for which a creative nested formula can't be created. I have scoured the Forum, Google, as well as manipulating the aformentioned functions a dozen different ways but always end up with an NA error.
Here is my current static formula :
=IF(Q517>0,VLOOKUP(Q517,$I$4:$O$517,7,FALSE),VLOOKUP(Q517,$AB$4:$AH$517,7,FALSE))
I am dealing with positive and negative numbers, hence the >0 reference which checks two different table array ranges and determines which one to obtain the desired value from. I need to be able to obtain the CELL'S VALUE that I get with the static Vlookup formula with a dynamically updating Vlookup formula ( or some derivation of it ).
My lookup value is : Q517 ......this value gets updated every hour, but once this becomes dynamic ( ie....once the web query starts, the vlookup stops working and all other derivations of it). As mentioned previously I have created many combinations of If / Offset / Counta / Index / Match / Vlookup but to no avail.
Any suggestions or insights would be greatly appreciated.
Richard
I'm working on an assignment and having a great deal of difficulty finding a solution that works. Here is the situation :
I have a dynamic sheet that updates every hour via a web query. I have 2 VBA macros that handle "updating" the columns within the range $I$4:$O$517 with the web query data as it comes in. The TABLE ARRAY RANGE itself DOES NOT CHANGE. I have found a hundred examples on line that explain how to handle dynamic table arrays or dynamic column index numbers but nothing on how to deal with a table array that remains static but has it's values INSIDE that table array changing. The problem is that when the data is changed within the table array $I$4:$O$517 .....ie from the web query update, my Vlookup formula does not work. Nor can I get an index / match or vlookup / If / offset / counta combination formula to work either. I want to have faith that this is not some obscure method for which a creative nested formula can't be created. I have scoured the Forum, Google, as well as manipulating the aformentioned functions a dozen different ways but always end up with an NA error.
Here is my current static formula :
=IF(Q517>0,VLOOKUP(Q517,$I$4:$O$517,7,FALSE),VLOOKUP(Q517,$AB$4:$AH$517,7,FALSE))
I am dealing with positive and negative numbers, hence the >0 reference which checks two different table array ranges and determines which one to obtain the desired value from. I need to be able to obtain the CELL'S VALUE that I get with the static Vlookup formula with a dynamically updating Vlookup formula ( or some derivation of it ).
My lookup value is : Q517 ......this value gets updated every hour, but once this becomes dynamic ( ie....once the web query starts, the vlookup stops working and all other derivations of it). As mentioned previously I have created many combinations of If / Offset / Counta / Index / Match / Vlookup but to no avail.
Any suggestions or insights would be greatly appreciated.
Richard
