Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
can lookup return cell reference istead of "text" for sumif? | Excel Worksheet Functions | |||
reference cell from previous worksheet without "naming" worksheet | Excel Worksheet Functions | |||
Macro to Create New Worksheet and Reference Cell in Old Worksheet As Tab Name - "Object Required" Error | Excel Discussion (Misc queries) | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) |