A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Reference cell in the "next" worksheet (regardless of name)?



 
 
Thread Tools Display Modes
  #1  
Old August 18th 09, 09:24 PM posted to microsoft.public.excel.misc
DBH1
external usenet poster
 
Posts: 2
Default Reference cell in the "next" worksheet (regardless of name)?

I need to insert a new worksheet into a bunch of existing workbooks and
reference a few cells in the next worksheet in the workbook. Unfortunately,
the worksheets do not have consistent names.

I'm looking for a formula something like ='NextWorksheetName'!F7. My new
worsheet will always be inserted before the existing worksheet, and will
always refer to the same cell in the existing worksheet.

Any suggestions on how to do this?
Thanks!
Ads
  #2  
Old August 21st 09, 05:08 AM posted to microsoft.public.excel.misc
jamescox[_73_]
external usenet poster
 
Posts: 1
Default Reference cell in the "next" worksheet (regardless of name)?


A lot of reasons come to mind why you shouldn't use such a formula, if
it was possible to write one - the first and most critical being if
someone (even you) accidently reordered the worksheets by clicking and
draging one to a new location, all your calculations would suddenly be
returning bad values - AND you would have almost no way of knowing it
had been done.

Excel does keep a collection of worksheets (that's why you can use
something like Worksheets(1).range("A1").value and get a value back) and
there are worksheet properties .Next and .Previous which return a
worksheet object. In VBA

strNextWorksheetName = Activesheet.Next.Name

should give you the name of the next workbook - but again, if anyone
changes the order of the worksheets, you're dead.

It sounds like you will make a copy of the worksheet you are going to
insert and then insert it into the right position. Let's call the sheet
you are pasting in My_Copy and let's say the formulas on the My_Copy
sheet you are going to paste in are like

=SUM('Base Sheet'!C7:C14)

When you paste My_Copy in front of the worksheet named Real_Sheet all
you have to do is a Find and Replace in the My_Copy worksheet to change
all the worksheet references.

That is, do a find on

'Base Sheet'!

and replace all occurances with

'Real_Sheet'!

That changes all your formulas in My_Copy to point at the values in
Real_Sheet - and it's a static link in the sense that it doesn't matter
what the order of the sheets is.

(Actually, the single quotes in 'Real_Sheet' aren't necessary because
there aren't any blanks in the sheet name, but Excel won't mind - and
it's a good habit to get into, just in case you forget that they have to
be there if the sheet name DOES have an embedded blank)

Hope this helps, even though it isn't what you really wanted... :Bgr


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126537

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell Steve Kay Excel Discussion (Misc queries) 2 August 8th 08 01:54 AM
can lookup return cell reference istead of "text" for sumif? [email protected] Excel Worksheet Functions 1 April 26th 08 03:23 PM
reference cell from previous worksheet without "naming" worksheet Kristin Excel Worksheet Functions 3 August 20th 07 08:30 PM
Macro to Create New Worksheet and Reference Cell in Old Worksheet As Tab Name - "Object Required" Error [email protected] Excel Discussion (Misc queries) 4 September 25th 06 01:35 PM
Complex if test program possible? If "value" "value", paste "value" in another cell? jseabold Excel Discussion (Misc queries) 1 January 30th 06 10:01 PM


All times are GMT +1. The time now is 08:53 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.