• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

more efficient formula

i am import data from various workbook i am using the formula to determine what row to start on the worksheet i would eventually like to apply it to a name range.I am looking for the most efficient way Thanks
=IF(ROW(LASTROW)>ROW(START_ROW),ADDRESS(ROW(LASTROW)+1,1),IF(ROW(START_PASS)=2,ADDRESS(ROW(START_PASS)-1,1),ADDRESS(ROW(START_PASS),1)))
0
Svgmassive
Asked:
Svgmassive
  • 2
  • 2
1 Solution
 
gowflowCommented:
Sorry is this VBA or it is a formula ?
if it is a formula then presume all of
LastRow
Start_Row
Start_Pass
are named ranges ??
can you post a sample workbook ? as cannot see how you LastRow get updated when you add data !!!

gowflow
0
 
SvgmassiveAuthor Commented:
it's not vba,yes they are name ranges
0
 
gowflowCommented:
can you post a workbook ?
gowflow
0
 
byundtCommented:
Unless your name is barryhoudini, I have generally found that any formula using ADDRESS is taking a roundabout way of solving the problem.

The INDEX function returns a range reference, and would be a better approach for your named range:
=INDEX($A:$A,IF(ROW(LASTROW)>ROW(START_ROW),ROW(LASTROW)+1,IF(ROW(START_PASS)=2,1,ROW(START_PASS))))

It may be that the formula can be further simplified if we could see your sample workbook and the logic for LASTROW, START_ROW and START_PASS.
0
 
SvgmassiveAuthor Commented:
point taken.looking at the workbook I think a simpler  approach would be to return the address of the last row text or numeric since the  data is mixed
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now