?
Solved

more efficient formula

Posted on 2013-06-16
5
Medium Priority
?
243 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 31

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 31

Expert Comment

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

Accepted Solution

by:
byundt earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

777 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