[Dba-office] Referencing previous sheet in Excel

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;
>>>>...
>>>>
>>
>



More information about the Dba-office mailing list