Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# more efficient formula

Posted on 2013-06-16
Medium Priority
251 Views
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
0
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
• 2
• 2

LVL 31

Expert Comment

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

ID: 39251469
it's not vba,yes they are name ranges
0

LVL 31

Expert Comment

ID: 39251561
can you post a workbook ?
gowflow
0

LVL 81

Accepted Solution

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

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

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦
###### Suggested Courses
Course of the Month9 days, 19 hours left to enroll