Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Expanding data
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 |
#3
|
|||
|
|||
Expanding data
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting Filtered RC cell information into other worksheets | Excel Discussion (Misc queries) | |||
How do I import formmail data to a custom excel template? | Excel Worksheet Functions | |||
Pull Current Month's Data Out of List - Repost | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) |