PDA

View Full Version : dynamic range lookup



rye123
12-15-2013, 11:03 PM
Hi All,

This might be an Excel formula question or a VBA question.

Here is the problem.

I know how to use the Vlookup function where the data is static meaning that if there were say 100 rows then there is always 100 rows.

My problems is that the number of rows can go up or down therefore any hard coded formula looking at a certain row will be out of luck because of all the additions and deletions of the rows.

On the left hand side of the sheet the 1st VLookup value is also a "string." So that is sending me for a bit of a warp too.

The second value in the VLookup is a number so that is something I can handle. The third value is non existant so there is no index value...per se.


Also as an added extra bonus this data updates every 10 minutes or so where the rows are added and deleted depending on what data is being downloaded. This update takes all of 4 seconds but that is an eternity in the computer world so i need to have something that will "do nothing" until these updates are completed.

When that new download happens then the screen goes either blank so that all rows and data are """" (nothing) or worse, negative. The first column is usually just nothing but I have seen it blink negative for half a second; the real worry is the second column that blinks negative all the time when this updating occurs.
thanks.

So i need a quick fix to ignore and do nothing until the second column is at least greater than zero.

I have attached a sample worksheet with notes on the sheet itself. I am looking for either a vba solution or excel formula solution.

Aussiebear
12-16-2013, 04:01 AM
Firstly create a dynamic table for the data, then create a validation list of the names in column A. Place the validation list in P10 and use a Vlookup function to find the population for the relevant city. I am not following your vlookup description well since to me it simply needs to be Vlookup( City Name, Name of table, Column 3, 0) to give you the population figure. in this case the City name will come from the selection in P10, the table array is the name of the dynamic table the data sits within, Column 3 is the column containing the population figures, and zero tells the function that you need the returned figure to be exact.