Solved

more efficient formula

Posted on 2013-06-16
5
215 Views
Last Modified: 2013-07-11
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
Comment
Question by:Svgmassive
  • 2
  • 2
5 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39251452
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
 

Author Comment

by:Svgmassive
ID: 39251469
it's not vba,yes they are name ranges
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39251561
can you post a workbook ?
gowflow
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39251729
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
 

Author Comment

by:Svgmassive
ID: 39255672
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now