refer to same row in other columns by using entire column in formula

Context
I have lots of columns of data which I want to do calculations on,
and i always want to consider the same row only.

I would like to use names in the formulas to make it easier to keep
track of which columns are being used. So it's not as if I have
a problem at the moment, just that it could be easier to follow if
i used named ranges.

Now, I noticed a 'feature' of excel and was wondering
if it is safe to use. Here it is:

Column A has, say, row numbers in it, so A1 has a 1, A2 a 2, etc.

If in cell B1 I type: '=A:A', the result is 1.
Below is cell the formula is typed into and the result:
C1, 1
C2, 2
C3, 3
C4, 4
IV12, 12

If i type '=A:A^2', the results in the following cells are:
C1, 1
C2, 4
C3, 9
C4, 16
IV12, 144

Does anyone know if I can trust this?
This looks too good to not have some major drawback....

Thanks for any tips on this

Brz
0
brzak
3/2/2010 12:40:32 PM
excel 39879 articles. 2 followers. Follow

2 Replies
826 Views

Similar Articles

[PageSpeed] 10

I urge you not to use this non-standard use of full column references like 
=A:A
Try changing one of the numbers you have in A1:A4 and not that the 
corresponding cell in C does not update until the worksheet is 
recalculated - by pressing f9 or by editing the formula in the C cell.
best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"brzak" <brzaak@gmail.com> wrote in message 
news:f03c3db6-38c3-4573-8938-e8b51e98d665@f35g2000yqd.googlegroups.com...
> Context
> I have lots of columns of data which I want to do calculations on,
> and i always want to consider the same row only.
>
> I would like to use names in the formulas to make it easier to keep
> track of which columns are being used. So it's not as if I have
> a problem at the moment, just that it could be easier to follow if
> i used named ranges.
>
> Now, I noticed a 'feature' of excel and was wondering
> if it is safe to use. Here it is:
>
> Column A has, say, row numbers in it, so A1 has a 1, A2 a 2, etc.
>
> If in cell B1 I type: '=A:A', the result is 1.
> Below is cell the formula is typed into and the result:
> C1, 1
> C2, 2
> C3, 3
> C4, 4
> IV12, 12
>
> If i type '=A:A^2', the results in the following cells are:
> C1, 1
> C2, 4
> C3, 9
> C4, 16
> IV12, 144
>
> Does anyone know if I can trust this?
> This looks too good to not have some major drawback....
>
> Thanks for any tips on this
>
> Brz 

0
Bernard
3/2/2010 1:31:11 PM
Strange.... I did check that initially and I am finding that it does
update.
(on xp, excel 2002...)


On 2 Mar, 13:31, "Bernard Liengme" <blien...@TRUENORTH.stfx.ca> wrote:
> I urge you not to use this non-standard use of full column references like
> =A:A
> Try changing one of the numbers you have in A1:A4 and not that the
> corresponding cell in C does not update until the worksheet is
> recalculated - by pressing f9 or by editing the formula in the C cell.
> best wishes
> --
> Bernard Liengme
> Microsoft Excel MVPhttp://people.stfx.ca/bliengme
>
> "brzak" <brz...@gmail.com> wrote in message
>
> news:f03c3db6-38c3-4573-8938-e8b51e98d665@f35g2000yqd.googlegroups.com...
>
>
>
> > Context
> > I have lots of columns of data which I want to do calculations on,
> > and i always want to consider the same row only.
>
> > I would like to use names in the formulas to make it easier to keep
> > track of which columns are being used. So it's not as if I have
> > a problem at the moment, just that it could be easier to follow if
> > i used named ranges.
>
> > Now, I noticed a 'feature' of excel and was wondering
> > if it is safe to use. Here it is:
>
> > Column A has, say, row numbers in it, so A1 has a 1, A2 a 2, etc.
>
> > If in cell B1 I type: '=A:A', the result is 1.
> > Below is cell the formula is typed into and the result:
> > C1, 1
> > C2, 2
> > C3, 3
> > C4, 4
> > IV12, 12
>
> > If i type '=A:A^2', the results in the following cells are:
> > C1, 1
> > C2, 4
> > C3, 9
> > C4, 16
> > IV12, 144
>
> > Does anyone know if I can trust this?
> > This looks too good to not have some major drawback....
>
> > Thanks for any tips on this
>
> > Brz- Hide quoted text -
>
> - Show quoted text -

0
brzak
3/2/2010 2:10:41 PM
Reply:

Similar Artilces:

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

update column
How would I update a column with numeric values so that there are 3 leading zeros for each row? hi it is not possible to add leading zeros to a numeric value. Mathematically, this is redundent and unnecessary. "brian" wrote: > How would I update a column with numeric values so that there are 3 leading > zeros for each row? opps. hit the post button too quick. option 1. custom format if your numeric value is 12345 then see the custom format to 00000000. note. format do not change data - it just changes the way it looks in the cell. option2. format to text then use the c...

Summing Alternate Rows
We have spreadsheets with many rows and need to sum alternate rows. Is there a way to do this without entering a formula using the number of each row? -- KarenQ ------------------------------------------------------------------------ KarenQ's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19608 View this thread: http://www.excelforum.com/showthread.php?threadid=395115 Try this: =SUMPRODUCT(--(MOD(ROW($A$1:$A$18),2)=1),$A$1:$A$18) That formula sums odd-row values in the range A1:A18. To sum even-row values the formula is: =SUMPRODUCT(--(MOD(ROW($A$1:$A$18...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

Global Column Row Preview Font Size
I know I can change the column, row and preview font size for the current email folder's view, but how do I do it for all of the email folders? I have loads of email addresses each with lots of folders. I don't want to have to do each one at a time. Surely there must be a default font setting (even if it's only in the registry)? Thanks in advance, Tim. I too would love an answer to this. Ian "Timie Milie" <tim_milstead@yahoo.co.uk> wrote in message news:45782ee8$0$27107$db0fefd9@news.zen.co.uk... >I know I can change the column, row and preview font ...

Use exchange from a trusted domain
Hi folks, I have domain a and domain b. Domain a is all set upp with exchange etc. Domain b was bought, and they don't have exchange. I have trust between the domains and file sharing and authentication are ok. What we want to do is, make users on domain b and let them use the exchange (on a seperate storage) on domain a. The users on domain b must logon to domain b, but need to have mailbox on domain a. Domain a are running on win2003 and exchange 2003. Domain are running on win2003 with forest and domain ready for exchange. Any advices? thanks in advance, - Bjarni Hi, Thi...

Extending formulas
Subject: Extending formulas Hi, For my application that uses Excel for calculations. I need to be able to extend the forula base of Excell with complex scientifc functions. Is there a way to add new functions to the Excel function base? Thanks Spx. MS has provided Visual Basic for Applications (VBA) to customize Excel with new functions, commands, forms, menus, etc. Tools|Macro|Visual Basic Editor From the VBA editor Insert Module Then write your functions in VBA. Details of writting functions in VBA is a very big topic, http://www.fontstuff.com/vba/vbatut01.htm may help y...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

How do I insert space between 2 consecutive columns of an XL Shee.
I want to have space between two consecutive columns of a worksheet (of course, without having inserted another column between the two) in order to have separated the Border Lines of the adjacent cells/columns. Please guide me if it can be done in XL. Can you achieve the effect that you're looking for by using a double vertical border down the right side of the left column and having no border down the left side of the right column? Rgds, ScottO "Shamshad Butt" <Shamshad Butt@discussions.microsoft.com> wrote in message news:1222EE13-11A9-4354-9F12-D1F1155D3902@microsof...

Need to add to current formula
I have this formula that will cause values to change based on the mont that is referenced in the formula ($L$1). Currently the formul is:=VLOOKUP($A$1,$AD$7:$AG$44,IF($L$1="January",2,IF($L$1="February",2,IF($L$1="March",2,IF($L$1="April",2,IF($L$1="MAY",4,IF($L$1="June",3,IF($L$1="July",3,0))))))),0) I need to add August, September, October, November, & December to thi formula but excel is not allowing me. Does anyone know how I can get around this? Oh by the way November thru April =2, May and October=4 and June thr...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

How Use ShellExecute with a CMemFile
Hello, I need to store files of various types (such as .doc, .xls, .jpg, .pdf, ..wmv, ...) in a database. Upon retrieval of such a file I have the file as data in memory in a CMemFile. I need to then "display" the file in the aproriate application. If I were to write the CMemFile to disk and create an actual file I could then, of course, use ShellExecute() to launch , say Word to display a .doc file. However, I would much prefer to avoid the overhead of writing the data to an actual file and then dealing with having to detect when the User is done and cleaning up the file I had ...

Using INDIRECT within functions
I learned a bit from JLathman in a previous post but tried to use the same methodology with this formula (also learned from this Group to get the last entered value in a column) without much luck. The original formula was this with 2009 sheet being static: =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536) Trying to make it dynamic I tried the following without success: =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & "!$H$3:$H$65536)")) Did I miss something or are...

Adding blank "separator" Rows in a Pivot Table
Does anyone know how to insert blank rows into a pivot table (say, between groupings, etc.)? There has to be a way, since some of the various table formats that come with Excel include blank rows. Double-click the field button that you want to format Click Layout Add a checkmark to Insert blank line after each item Click OK, twice Bucko wrote: > Does anyone know how to insert blank rows into a pivot table (say, > between groupings, etc.)? There has to be a way, since some of the > various table formats that come with Excel include blank rows. -- Debra Dalgleish Contextures http...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...

Formula Problem?
I am using Excel 2000 with Windows XP. I am having a problem. I am on Sheet 2 of my workbook. I have SSN on a sheet named Employees in the same workbook. I need to take the numbers on the Employees Sheet and transfer it to the sheet 2. I know how to do this. It just won't work. This is a copy of my formula. =SUM(Employees!C3) This should take the SSN that is in the C3 cell on the employees sheet and place it at the cell where the formula is typed. When I put this formula in the cell I am getting just a "0". Please help. =Employees!C3 -- Kind regards, Niek Otten...

formula auditing/macro
Can anyone give me the sytax to goto - special - precedents so I can create a macro so I can assign to a hotkey and dont have to go through 4 steps ? Thanks, Yosef With A1=1 and D2=2*A1, and D1 as active cell: I recorded a macro for these steps: Edit|GoTo->Special->Precedence And the macro contained just one line: Selection.DirectPrecedents.Select best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ynissel" <ynissel@discussions.microsoft.com> wrote in message news:DA544BDE-3717-4953-A5E3-06191BC28373@microsoft.com... > Can anyone...

SQL query / Formula for Reorder Point?
Is it possible to issue an SQL command(s) that would: 1. Set the Restock Level for all items to be equal to the current on-hand quantity for each item. 2. Set the Reorder point for all items to be equal to 1/3 of that items Restock Level. I'm currently doing this manually, or using a formula in excel when items are initially imported into the DB, but there are still several thousand that need to be retro-fitted like this... Thanx in advance! -=RFM=- UPDATE Item SET RestockLevel=(Quantity - QuantityCommitted) UPDATE Item SET ReorderPoint=ROUND(RestockLevel/3,0) - Evan Culver Ne...

copy values generated by conditional formula in one sheet to the other work sheet as values
Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Select your range to copy edit|goto|special|c...

formula to have 0 to 6.5 only in ref to other cell val of 0<=6.5
...

MS Money '04 Reference Books
Is anyone aware of any good MS Money '04 reference books? I've checked the MS Press website, but they seem to have one for every software program other than Money. Any suggestions? Thanks much, Too many books ended up getting pulped--I'm not sure there are any for Money anymore. You might check on http://www.amazon.com. "Mike" <anonymous@discussions.microsoft.com> wrote in message news:e72001c3f056$477b1c10$a501280a@phx.gbl... > Is anyone aware of any good MS Money '04 reference > books? I've checked the MS Press website, but they seem > to...

Column searching problem
I have a worksheet called "net" containing the following: NETWORK AVG MIN MAX STD SAMPLES ABC 17.17 16.26 17.71 0.4469 19 CBS 12.99 11.69 14.56 0.6524 30 NBC 15.39 14.08 16.70 0.7323 38 NBC* 15.12 14.08 15.99 0.4910 31 Fox 10.63 9.07 12.09 0.9374 8 HBO 10.94 9.07 13.22 1.2507 19 Showtime 11.52 6.84 13.23 1.9017 11 HDNet 18.14 14.22 18.85 1.1619 14 HDNet* 18.45 18.13 18.85 0.2847 13 HDNet-Movies 1...