|
Using non defined names from another sheet
I think I'm missing a vital point here. I often use existing (not defined)
labels to reference cells or calculate values on a sheet. Can I also do this from a different sheet without defining ranges by somehow adding the sheet name in the reference? (Tried a few permutations of this idea but always get a #name? error.) TIA |
Hi pQp.
I may have tottally misunderstood - in which case apologies - but perhaps you are looking for syntax like: =Sheet2!A15 or =SUM(Sheet2!B4:B6) --- Regards, Norman "pQp" wrote in message ... I think I'm missing a vital point here. I often use existing (not defined) labels to reference cells or calculate values on a sheet. Can I also do this from a different sheet without defining ranges by somehow adding the sheet name in the reference? (Tried a few permutations of this idea but always get a #name? error.) TIA |
Yes, Sorry Norman. Thanks for replying but I was worried that I wasn't
explaining properly, because I don't actually know what those non defined names are called. Basically if you want to refer to a cell or calculate a range, you don't have to have labels or defined names, simply type something like =March Income and you will be referring to the cell where March row intersects Income column, even when those words are not in R1 or C1. They can be anywhere. You can calculate the same way just using the words(and numbers) above a group of numbers. If the same words appear more than once, it will assume the first instance, left to right/top to bottom. All this works just fine if the 'names' are on the same sheet as the reference to them. So, back to it....I'd always thought (until I tried) that to do the same from another sheet would just be a matter of including the sheet name as you would any ref (like your example). But either it can't be done or I don't know the right syntax. Hope this makes more sense Thanks again. "Norman Jones" wrote in message ... Hi pQp. I may have tottally misunderstood - in which case apologies - but perhaps you are looking for syntax like: =Sheet2!A15 or =SUM(Sheet2!B4:B6) --- Regards, Norman "pQp" wrote in message ... I think I'm missing a vital point here. I often use existing (not defined) labels to reference cells or calculate values on a sheet. Can I also do this from a different sheet without defining ranges by somehow adding the sheet name in the reference? (Tried a few permutations of this idea but always get a #name? error.) TIA |
Hi pQp,
You appear to be talking about names added with the: Insert | Names | Create command. If so, you can use these names, on any sheet within the workbook, without using any sheet qualification. If, therefore, the original table used to create the names included: Anne 100 Freda 200 Frank 300 On any sheet within the workbook, the formula: =Anne + Freda + Frank should return 600. Or, perhaps, I still have not understood? --- Regards, Norman "pQp" wrote in message ... Yes, Sorry Norman. Thanks for replying but I was worried that I wasn't explaining properly, because I don't actually know what those non defined names are called. Basically if you want to refer to a cell or calculate a range, you don't have to have labels or defined names, simply type something like =March Income and you will be referring to the cell where March row intersects Income column, even when those words are not in R1 or C1. They can be anywhere. You can calculate the same way just using the words(and numbers) above a group of numbers. If the same words appear more than once, it will assume the first instance, left to right/top to bottom. All this works just fine if the 'names' are on the same sheet as the reference to them. So, back to it....I'd always thought (until I tried) that to do the same from another sheet would just be a matter of including the sheet name as you would any ref (like your example). But either it can't be done or I don't know the right syntax. Hope this makes more sense Thanks again. "Norman Jones" wrote in message ... Hi pQp. I may have tottally misunderstood - in which case apologies - but perhaps you are looking for syntax like: =Sheet2!A15 or =SUM(Sheet2!B4:B6) --- Regards, Norman "pQp" wrote in message ... I think I'm missing a vital point here. I often use existing (not defined) labels to reference cells or calculate values on a sheet. Can I also do this from a different sheet without defining ranges by somehow adding the sheet name in the reference? (Tried a few permutations of this idea but always get a #name? error.) TIA |
What you're actually talking about, without realizing the actual label for
it, is "intersection operator", which is simply a single space. Look it up in the Help files. *Without* defining or creating any names, =Tom Dick will return the intersection of a row and column that contain those 2 names. That is, as long as <Tools <Options <Calculation tab, "Accept Labels In Formulas" *IS* checked. AFAIK, this only works on the sheet that contains the data list that contains those names. On the other hand, if you *define* the names "Tom" and "Dick", To designate specific ranges that intersect, then: =Tom Dick will work on *any* page in the WB. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- .. "pQp" wrote in message ... Yes, Sorry Norman. Thanks for replying but I was worried that I wasn't explaining properly, because I don't actually know what those non defined names are called. Basically if you want to refer to a cell or calculate a range, you don't have to have labels or defined names, simply type something like =March Income and you will be referring to the cell where March row intersects Income column, even when those words are not in R1 or C1. They can be anywhere. You can calculate the same way just using the words(and numbers) above a group of numbers. If the same words appear more than once, it will assume the first instance, left to right/top to bottom. All this works just fine if the 'names' are on the same sheet as the reference to them. So, back to it....I'd always thought (until I tried) that to do the same from another sheet would just be a matter of including the sheet name as you would any ref (like your example). But either it can't be done or I don't know the right syntax. Hope this makes more sense Thanks again. "Norman Jones" wrote in message ... Hi pQp. I may have tottally misunderstood - in which case apologies - but perhaps you are looking for syntax like: =Sheet2!A15 or =SUM(Sheet2!B4:B6) --- Regards, Norman "pQp" wrote in message ... I think I'm missing a vital point here. I often use existing (not defined) labels to reference cells or calculate values on a sheet. Can I also do this from a different sheet without defining ranges by somehow adding the sheet name in the reference? (Tried a few permutations of this idea but always get a #name? error.) TIA |
Hi Ragdyer,
Thank you. Clearly, yours is the correct intepretation. --- Regards, Norman "Ragdyer" wrote in message ... What you're actually talking about, without realizing the actual label for it, is "intersection operator", which is simply a single space. Look it up in the Help files. *Without* defining or creating any names, =Tom Dick will return the intersection of a row and column that contain those 2 names. That is, as long as <Tools <Options <Calculation tab, "Accept Labels In Formulas" *IS* checked. AFAIK, this only works on the sheet that contains the data list that contains those names. On the other hand, if you *define* the names "Tom" and "Dick", To designate specific ranges that intersect, then: =Tom Dick will work on *any* page in the WB. -- HTH, RD |
| All times are GMT +1. The time now is 03:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com