Loop type formula - no Macro
Is it possible to have a formula do something simila to a loop Macro?
I have a large spreadsheet and I want tpick up values in column out to the right when I get the the bottom of a list. So if Column A has values down to row 100 and columns D-G have values corresponding to Column A. In another tab I want to bring in the values in Column D, then when I get the bottom of A or D because they are blank then start at the top of column E values and so on through to the bottom of G. So that this second tab will have 400 values in it. The reason I can't just hard code is because the length of Column A might grow. Can this be done without a macro? I am creating jnl and could create 4 separate jnls but was hoping to keept it to just one. Thanks Rick |
Loop type formula - no Macro
what you suggest is well beyond A formula. formulas return values to the cell
in which they reside. if you excect to have 400 values in your second sheet, then it would be expected that you have 400 formulas. since you are vague about your data and only mentioned that column a values match columns d-g value, this might suggest a lookup or match formula but without fruther details, i would hesitate to suggest anything specilfic or even try. there is no real problem with a varying length to column a. you can find the last cell of column a with this line each time a macro is run. dim c as long c = cells(rows.count,"A").end(xlup).row msgbox c i would guess that a macro would be your best bet for you project. formulas just wont do what you ask. Regards FSt1 "RD" wrote: Is it possible to have a formula do something simila to a loop Macro? I have a large spreadsheet and I want tpick up values in column out to the right when I get the the bottom of a list. So if Column A has values down to row 100 and columns D-G have values corresponding to Column A. In another tab I want to bring in the values in Column D, then when I get the bottom of A or D because they are blank then start at the top of column E values and so on through to the bottom of G. So that this second tab will have 400 values in it. The reason I can't just hard code is because the length of Column A might grow. Can this be done without a macro? I am creating jnl and could create 4 separate jnls but was hoping to keept it to just one. Thanks Rick |
Loop type formula - no Macro
Thanks I thought that might be the case.
I'll go the long way and create 4 separate jnls for now till I have more time. "FSt1" wrote: what you suggest is well beyond A formula. formulas return values to the cell in which they reside. if you excect to have 400 values in your second sheet, then it would be expected that you have 400 formulas. since you are vague about your data and only mentioned that column a values match columns d-g value, this might suggest a lookup or match formula but without fruther details, i would hesitate to suggest anything specilfic or even try. there is no real problem with a varying length to column a. you can find the last cell of column a with this line each time a macro is run. dim c as long c = cells(rows.count,"A").end(xlup).row msgbox c i would guess that a macro would be your best bet for you project. formulas just wont do what you ask. Regards FSt1 "RD" wrote: Is it possible to have a formula do something simila to a loop Macro? I have a large spreadsheet and I want tpick up values in column out to the right when I get the the bottom of a list. So if Column A has values down to row 100 and columns D-G have values corresponding to Column A. In another tab I want to bring in the values in Column D, then when I get the bottom of A or D because they are blank then start at the top of column E values and so on through to the bottom of G. So that this second tab will have 400 values in it. The reason I can't just hard code is because the length of Column A might grow. Can this be done without a macro? I am creating jnl and could create 4 separate jnls but was hoping to keept it to just one. Thanks Rick |
All times are GMT +1. The time now is 03:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com