ANmarS - Search results

Excel/VBA expert since Sep 1997 (20 years 9 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

1 Q & A posts tagged with select

Jun 9

In excel, is there a formula to select the last cell of worksheet to calculate values in the next worksheet?

I currently used this formula:
='NSN Motorsports'!A12+1
In this example my previous worksheet is "NSN Motorsports" A12 is the last cell in that worksheet. I used this formula to create the next number on the new worksheet. This requires me to go back to the previous worksheet and click the last cell. My workbook will have many worksheets, so doing it this way is very time consuming. Any ideas for formulas that will not require me to leave my current worksheet to get a value from the previous worksheet?


You can create a pattern for your sheet names
After that, use some formulas to determine "the previous" sheet to the one you are in...
1- In an empty cell (make it B1), paste this
=MID(CELL( "filename",A1),SEARCH( "]",CELL( "filename",A1))+1,500)
This will give you the name of that sheet, so copying that cell into another sheet will give the name of that sheet, etc
2- Now, If your 'Pattern" is NSN1, NSN2, NSN3, then paste this in B2
This will give you the name of the "Previous" sheet to the one that you are in
So if you are in NSN4, B2 will give you NSN3, and so on
3- Finally, to get the last value of the "Previous" sheet, do this
=MAX( INDIRECT( B2&"!A:A"))+1

Good luck with that, this will be totally automated method, justcopy and paste the three cells into the new sheet

If you need more info or think that you can customize it more, please contact me here

After all, it is the VBAXLMan here

Tuesday 6/9/2009 11:08:54 AM