Apply formula and functions for as long as the data is

[andre199017]

Hi guys,

A bit tricky to explain, but I have data across several worksheets which are all interlinked via vlookups/references. The data is across 6 columns and runs for multiple rows.

My question is: Sometimes, I could have 100 lines of data, the other, 500. How can I make my 'working range' dynamically expand when I copy and paste in a new data set? (Everything is the same apart from the lines of data).

At the moment, if I paste in a 120 line set of data after it had 540 lines in, I've got 420 rows of N/As and blanks messing up any filtering/subsequent work I want to do.

Thanks

[FDibbins]

1 way...
=if(your-formula=N/A,"",your-formula)

[andre199017]

Not all the fields 'not in use' give N/A, some give 0. I have read a little about dynamic ranges, but wasn't sure if this was my solution here?

[dflak]

I don't see what version of Excel you are using. If you are using Excel 2007 or later, then you can convert the lookup range to an Excel Table and give it a name like Table_Lookup or whatever makes sense. One of the many features of tables is that they know how big they are. So =VLOOKUP(A2,Table_Lookup,4,False) will look at exactly the right number of rows no matter how much data is added to or taken away from the table. You will also note that Table_Lookup makes a lot more sense than something like 'Sheet XYZ'!$A$2:$H$136.

Here's some more information on tables: http://www.utteraccess.com/wiki/inde...ables_in_Excel.

For any version of Excel, you can also define a named dynamic range to cover the data. For information on how to make dynamic ranges, see this article: http://www.utteraccess.com/wiki/inde...Dynamic_Ranges.

So suppose you create a named dynamic range called Lookup_Data, then you can use it like: VLOOKKUP(A2,Lookup_Data,4,False). If you set the named range up correctly, it will also grow and contract with the data.

Tables are easier, but it's worth knowing how to set up a dynamic range; they come in handy for dynamic charting and sometimes you can overlay them over a pivot table result and then use the results of the pivot table as a data source for VLOOKUP or range for data validation or even another pivot table!

[Tony Valko]

You will also note that Table_Lookup makes a lot more sense than something like 'Sheet XYZ'!$A$2:$H$136.

Not to me!

If I see 'Sheet XYZ'!$A$2:$H$136 I immediately and instantly know where that's located.

If I see Table_Lookup I might not remember where that is located and will have to go look for it.

[dflak]

I suppose it's a matter of whether you want to know where something is or what it means. If you go to the name manager, the tables are listed and if you click on the table, it will take you to the page.