Referencing data in a column which changes length daily

[lewis1]

Hi all,

Situation:
I have a column of data 'reference column' which changes length on a daily basis.
In other columns 'formula columns' I have formulas which are dependent on the 'reference column' data.


Current Solution:
My data length will not be longer than 1000 rows. For all my 'formula columns", I use an IF statement to leave these cells blank if there is nothing in the 'reference column' and I run that down for 1000 rows.


Problem:
The current solution is heavy, inflexible and inefficient.


Question:
What is the best practice to populate my 'formula columns' where my data from the 'reference column' changes daily.

Thanks very much,
Lewis.

 

[gsbelbin]

It sounds like you need to use a Dynamic Named range. There are plenty of examples on here and there is a good description at www.ozgrid.com/Excel/DynamicRanges.htm ‎See how you get on and if it doesn't make sense come back here

 

[Aladin Akyurek]

If you are able to create a table (Insert | Table on the ribbon) and have those formula ranges as part of the table, everything would go automatically.

Otherwise, you need to sollicite for code in VBA which inspects data area and runs the formulas in formula columns just far as needed.