If I'm not mistaken, what you're looking for is a *formula* that will dynamically grab the value from Sheet1!A2 for Sheet2, Sheet1!A3 for Sheet3, Sheet1!A4 for Sheet4, etc. If so, then the attached file should work for you. Note that this formula VERY MUCH depends on the fact that the worksheet name tells us something about the worksheet's POSITION. If you change the worksheet names from "Sheet2", "Sheet3", etc., this solution will break.
The formula you're looking for is this one:
=INDIRECT("Sheet1!A"&SUBSTITUTE(RIGHT(CELL("filename",A1)
,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))),"Sheet",""))
You'll notice the part highlighted in red. This does the work of giving us
a string with the name of the worksheet in it. After that, it's just a
matter of using some text manipulating functions to construct the address we
want from Sheet1, and then use INDIRECT to access the value in that cell.
I'd recommend looking up the CELL & INDIRECT functions in the Excel help if
you need more information.
Cheers,
Chris
On Wed, Feb 17, 2010 at 12:00 PM, Branden Durst <durs...@gmail.com> wrote:
> Okay, so I am in the process of creating a very large excel file and
> was hoping for a quick way to do something, but my intermediate skills
> aren't able to figure out the solution.
>
> Here is the situation, I am referencing cells off my first worksheet
> in subsequent worksheets. For each new sheet passed the first sheet,
> I'd like add one to the row for the cell reference. So in other words,
> On sheet two I'd like a cell to be referenced to sheet 1 cell A2, on
> sheet 3 I'd like a cell to be referenced to sheet 1 cell A3 and so
> on. Any ideas?
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our Facebook Group @
> http://www.facebook.com/group.php?gid=287779555678
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
>
> We reach over 6,800 subscribers worldwide and receive many nice notes about
> the learning and support from the group.Let friends and co-workers know they
> can subscribe to group at
> http://groups.google.com/group/excel-macros/subscribe
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
WorksheetReferencing.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
