Salakhetdinov Shamil
mcp2004 at mail.ru
Sun Dec 21 15:38:21 CST 2014
Yes, Susan, You create a Name for B2 cell on each sheet and you set this Name scope for a different sheet - for a sheet where formula referring this B2 cell's Name will be used. Then you can use the same formula on each sheet. Then you can keep you sheets on a workbook in any order. -- Shamil Sun, 21 Dec 2014 15:04:27 -0500 from Susan Harkins <ssharkins at gmail.com>: >I think this has potential if sheets might be rearranged. > >I have a quick question though because I'm only half-following what you're suggesting. The range names are sheet level, correct? So, you're creating a name for B2 in each sheet. Is this correct? > >Susan H. > >On Sun, Dec 21, 2014 at 2:51 PM, Salakhetdinov Shamil < mcp2004 at mail.ru > wrote: >>Susan -- >> >>If you set the scope for the names as I have described then you can use the same formula in different worksheets but the same *names* in these formulas will refer to different worksheets's cells. This is exactly what you need AFAIU. >> >>Well it's a bit tricky - you have asked for a way to *directly* refer to the previous worksheet's cells, and when you are using proposed here scoped names (with a name's scope set for a worksheet not for a whole workbook) you're getting something like "indirect" cells references, I mean, >> >>1. in a worksheet you use a formula, which uses (/refers to) a Name (a named cell/names cells range) and >>2. this Name's scope is defined as a worksheet where the formula is used but >>3. the Name is defined for a cell (cells' range) located in a different worksheet. >> >>IOW >> >>You first define a Name for a cell/cells range and set the scope for this Name for a worksheet where this Name will be used in a formula. >> >>Does it sound better? >> >>-- Shamil >> >>P.S. BTW, I have never used explicitly scoped name before. >> >> >>Sun, 21 Dec 2014 14:15:51 -0500 from Susan Harkins < ssharkins at gmail.com >: >>>It took me a minute -- but I see what you're doing. You're right, I think that would work, but if I'm going to give each total cell a name, I might as well refer to it directly. I don't see that this simplifies anything in my example, but I think it has potential -- it's certainly interesting! Thanks! >>> >>>Susan H. >>> >>>On Sun, Dec 21, 2014 at 2:00 PM, Salakhetdinov Shamil < mcp2004 at mail.ru > wrote: >>>> Susan -- >>>> >>>>I suppose to solve your task you can set a Scope for a Name. Let's say your sample B2 cell names are keeping Month Total Sum values. Then you can define the same name, let's call it 'MonthTotalSum' for your [January], [February], [March] worksheets and for: >>>> >>>>- [January] worksheet set 'MonthTotalSum' name's scope to [February] worksheet; >>>>- [February] worksheet set 'MonthTotalSum' name's scope to [March] worksheet; >>>>- [March] worksheet set 'MonthTotalSum' name's scope to [April] worksheet' >>>>... >>>> >>>>And when you'll reference >>>> >>>>- MonthTotalSum name in your formula in [February] worksheet you'll actually reference 'January'!B2; >>>>- MonthTotalSum name in your formula in [March] worksheet you'll actually reference 'February'!B2; >>>>- MonthTotalSum name in your formula in [April] worksheet you'll actually reference 'April'!B2; >>>>... >>>> >> >