Expanding data

When i transfter data from an external source, I have formulas that pick up
the data from a Vlookup table, to match the transfered data, and return
various things.... the problem is, is that the transfer data is not always
the same amount of rows every day. To ensure i have calculated all the data
transfered, i have used the cheats way out and copied the formulas all the
way down to the bottom (Well 30000 cause i give up). Excel seems to struggle
with calculating cells all the time.... I know this is the wrong way of doing
it and i need to use some thing like Dynamic Data Ranges??? Ive looked into
it but to be honest i havent got the time to nerd around... im struggling to
understand the =OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1) formulas that i have
found, dont know where to input them etc... i thought i was quite good on
excel until this one! im going mad please help!
Thanks

You can use a named range in your Vlookup table that refers to the range of
the external data. Then use a macro to adjust the named range based on the #
of rows in the external data. You can use the special cells / last cell row
# to determine what row to go through (assuming the data starts in row 1).

"Excels Cracking me up!" wrote:

> When i transfter data from an external source, I have formulas that pick up
> the data from a Vlookup table, to match the transfered data, and return
> various things.... the problem is, is that the transfer data is not always
> the same amount of rows every day. To ensure i have calculated all the data
> transfered, i have used the cheats way out and copied the formulas all the
> way down to the bottom (Well 30000 cause i give up). Excel seems to struggle
> with calculating cells all the time.... I know this is the wrong way of doing
> it and i need to use some thing like Dynamic Data Ranges??? Ive looked into
> it but to be honest i havent got the time to nerd around... im struggling to
> understand the =OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1) formulas that i have
> found, dont know where to input them etc... i thought i was quite good on
> excel until this one! im going mad please help!
> Thanks
>

Clarify things a little, please:

Are you copying formulas to act on each row of imported data? If that's the
case dynamic ranges won't help you. Short of using a macro to copy your
formulas down the sheet to reference each row of imported data, the next best
thing is to have the formulas in row 1 or 2 in a column adjacent to the
imported data. Then, simply select the formulas and double-click on the
small square at the bottom right of the selected range. Excel will copy the
formulas to the bottom the used range.

Do you need to have the range of imported data identified in a range name,
for reference in formulas? If that is the case, you'd use the dynamic
formula in the Insert->Names->Define dialog box, in the Refers To text box.

"Excels Cracking me up!" wrote:

> When i transfter data from an external source, I have formulas that pick up
> the data from a Vlookup table, to match the transfered data, and return
> various things.... the problem is, is that the transfer data is not always
> the same amount of rows every day. To ensure i have calculated all the data
> transfered, i have used the cheats way out and copied the formulas all the
> way down to the bottom (Well 30000 cause i give up). Excel seems to struggle
> with calculating cells all the time.... I know this is the wrong way of doing
> it and i need to use some thing like Dynamic Data Ranges??? Ive looked into
> it but to be honest i havent got the time to nerd around... im struggling to
> understand the =OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1) formulas that i have
> found, dont know where to input them etc... i thought i was quite good on
> excel until this one! im going mad please help!
> Thanks
>