Reference column question

Setting up a tabulation speadsheet,has 40 rows and 10 columns.Question is 
when I do my formula which is a subtraction for each column,I want to use a 
refernce # from the first row of each column =Sum(F1-D3)=SUM (G1-D3)and so 
to=Sum(P1-D3)Rather than enter each manually,because cut and paste adds 1 to 
each column how can I get to use f1 as reference for that column G1 for that 
column.Thanx
0
Argente40 (1)
7/29/2007 3:48:01 PM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
236 Views

Similar Articles

[PageSpeed] 11

If I understand correctly, try putting a $ in front of the row reference.  
Also, sum is not necessary for what you are doing.

=F$1-D3

as you copy that down it becomes
=F$1-D4

copied across to the right it becomes
=G$1-E3


You can fix the row or column or both in this fashion:
F1 (relative row, relatative column)
$F1 (relative row, absolute column)
F$1 (absolute row, relative column)
$F$1 (absolute row, absolute column)


"Argente4.0" wrote:

> Setting up a tabulation speadsheet,has 40 rows and 10 columns.Question is 
> when I do my formula which is a subtraction for each column,I want to use a 
> refernce # from the first row of each column =Sum(F1-D3)=SUM (G1-D3)and so 
> to=Sum(P1-D3)Rather than enter each manually,because cut and paste adds 1 to 
> each column how can I get to use f1 as reference for that column G1 for that 
> column.Thanx
0
jmb (270)
7/29/2007 4:02:03 PM
Reply:

Similar Artilces:

PlotArea question
Hi there, I have a chart I'm trying to build with VBA (Excel 2000). It's a xlBarClustered type (horizontal bar chart) but it has unusually long labels. What I want to do is to make the ratio of space for the labels to bars roughly 75/25. PlotArea seems to include BOTH the data and the labels, so resizing this doesn't seem to help - I just end up with more whitespace if I try reducing the plotarea size. Is there any way of controlling the size of the labels versus the bars? Any help much appreciated Gromit Hi, The simply answer is no. Excel automatically decides the area allo...

Cell Value as Named Range Reference
Little bit of a quirky question... Trying to use a cell value as a reference in a formula, where that cell value is the name of a named range. So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2. I want to get the correlation vale for A1:A3 and B1:B3 So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the formula: =correl(D1,D2). But I get an error. Have also tried using Indirect to no avail. Any help would be hugely appreciated. Thank you. =CORREL(INDIRECT(D1),INDIRECT(D2)) -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "ste...

Need to reference a block of cells from another sheet.
Dear All, apologies if this is probably a mundane question, but I didn't know what 'search terms' to put in google to return any results.. I've got a Spreadsheet with 12 tabs (sheets), I need part of tab 12 (sheet) to appear on tabs 1-11 (sheets), so that if I change data in tab 12 (sheet), tabs 1-11 (sheets) are changed too.. the cells themselves will never move location, just the data within them.. Is it possible? Regards Paul. On your sheet1 select the cell that should receive the data from Sheet12; Hold down the Shift key and Click on Sheet11 << this shoul...

Newbie question: How to set up OWA in Exchange 2003
I am using Small business server 2003 without any patch. I could check my mail at the server by typing http://localhost/exchange/userid. How could I config Exchange to let other users to check e-mail though internet? I believe I haven't setup a front-end server. But could I use IE to check the mail. if the front end server haven't setup properly, how could I check the email at the server? I got serveral web sites. However, I cannot find a website with a directory or virtual directory named "exchange". Is the web site will be created when I setup a front-end server? ...

Dates question #2
I want to generate a list on a new sheet (sheet4) that will show info corresponding to "sheet 1". Basicly make a list on sheet 4 that will show rows of info from sheet 1 between 4.5 through 5 years past. If today is past five years remove from list on sheet 4. Sheet 1 700 rows and columns out from A to AH column A entry listing 1-700. this column will grow with repeated info as time goes on, updated as I do required service. column B Date of service Need to be notified on Sheet 4 of service coming due. Service needs to be done every 5 years. If I confused you I'll try to get...

add columns to a pivot table
How can I add columns to a pivot table? ...

automatic column.autofit
I have a csv text file that I want to automatically open with column autofit. the text file is generated from a query to a remote database, so it cannot contain any code. pls advise mechanism. Igor CSV files are just plain ascii text files. They'll never contain code. So you could have a wrapper workbook that opens the .csv and does your formatting or you could do it manually. Igor Lisbaron wrote: > > I have a csv text file that I want to automatically open with column > autofit. > the text file is generated from a query to a remote database, so it cannot > contain ...

Hyperlink Question #3
I have the following code: Sub sheets_list() Range("M1:M100").Select Selection.ClearContents Range("M1").Select r = ActiveCell.Row s = ActiveCell.Column For a = 0 To Sheets.Count - 1 Cells(r + a, s).Value = Sheets(a + 1).Name Next a End Sub It returns a list of all sheets in my workbook. Is it possible to amend the code so that it returns each of the sheet names as a hyperlink to the corresponding sheet ? TIA, Phil Phil, I assume you have the list on the last sheet in the workbook. With some modification to your code, I came up with the following. Note: that Xl97 hype...

Sent mail folder question for MVPs
I have two questions about Outlook, both related to having multiple mail accounts (e.g one Exchange and one IMAP). 1) Can I have mail from my imap account go to that accounts sent mail folder instead of the Exchange or default one? 2) Can I disable spam filtering for one account while allowing for another? 3) Can I have rules/filters on folders other than inbox? Thanks for any help. 1) Yes, but you need to use rules to move the mail by account and if it can't connect to the imap for some reason, no copy is saved. 2) No. 3) Only if you use Run Now. -- Diane Poremsky [MVP - O...

Button hides unhides columns, how?
I inherited a workbook where in some tabs buttons are used to hide and unhide columns. These buttons appear above the column headers and are labeled with the + sign. When clicked several columns are unhidden and a button appears with a "-" sign. Clicking this button in turn rehides the columns. How is this done? Are these command buttons? How are they inserted above the column headers? I've not been able to figure out how to see the code behind the buttons. Any guidance will be greatly appreciated. -- JimH check in Help for Group and UnGroup. This is an option acc...

refering to a name range area as a reference value multiple times on a worksheet at different locations
i was wondering if i had posted this in the right section as it could apply to both please could you take a look at the following post http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/f088a5737dd26570 thank you Is there some reason you can't post your question here instead of asking us to look elsewhere? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Blinds Nottingham" <bradrail.blinds.awnings@googlemail.com> wrote in message news:d8768e43-e035-45eb-856a-0ccdc843441d@g1g2000yqi.googlegroup...

Counting entries in column C, based on entries in column A
I would like to count the # of entries of a value in column C, grouped by values in column A. Binder Note Category AA01 abc 2 AA01 ccc 2 AA01 abc 2 AA01 ddd 3 AA22 aaa 1 AA22 ccc 2 Need to find the # of Category 2's , for every Binder # Do not have the nested IF's I have tried available at the moment, but I am getting close... Try this: E1 = some binder code like AA01 E2 = some category number like 2 =SUMPRODUCT(--(A2:A7=E1),--(C2:C7=E2)) Biff <xj6rob@hotmail....

Macro to selectively hide columns
Hi Guys, Am using MS Excel 2002. Simply, I have the following columns: May-04 | Jun-04 | Jul-04 | Aug-04 | Sep-04 | Oct-04 | Nov-04 Above this a user can choose to view the spreadsheet by any combinatio of months (e.g. just May-04 - hide Jun-04, Jul-04, Aug-04, Sep-04, Oct-04, Nov-04 May-04 to Jun-04 - hide Jul-04,Aug-04,Sep-04,Oct-04,Nov-04 Aug-04 to Oct-04 - hide May-04,Jun-04,Jul-04,Nov-04 Currently I am invoking a change event to tell the following macro t hide the corresponding fields. This is done via a drop down lis currently. Ideally, the user would be able to key in the range...

Comparing Columns and Counting
Ok, I have been working through this for the last few hours to no avail. I have 2 columns of data, driver1 and driver2. Column 1 (driver1) contains the following (for example) Row1 - Not Impaired Row2 - Impaired - Tested Row3 - Impaired - Not Tested Row4 - Not Impaired Row5 - Not Impaired Column2 (driver2) contains the following. Row1 - Impaired - Tested Row2 - Impaired - Tested Row3 - Not Impaired Row4 - Not Impaired Row5 - Impaired - Tested Each row represents a crash report. I need to determine the accidents that involved impaired drivers (tested and untested). I want to compare the ...

Pivot Table question #3
Hi Folks: I'm helping a pal with a table that has little data, and when the PT is setup, the result is 90% of the cells have the word "blank" Any way to have the cells show nothing (be empty) ?? Thanks Steve Try just typing a space character over it the (blank). Debra Dalgleish has a pivottable addin that you may want to use to make somethings easier: http://www.contextures.com/xlPivotAddIn.html and for instructions: http://www.contextures.com/xlPivotAddIn02.html s boak wrote: > > Hi Folks: > > I'm helping a pal with a table that has little data, and wh...

What is IVA# in Distribution Reference and Reference?
We did an inventory adjustment and now we get a unknown amount in Debit under the Inventory account and Credit for COGS account in the Transaction Entry Zoom. We're trying to figure out where that amount came from but we couldn't zoom more than what it's currently shown. In both fields, Reference and Distribution Reference, it's shown IVA#######... that is unknown to or setup by us in anywhere, i.e. Audit Trail, Source Document. Does this amount affect the dollar in the accounts mentioned? And can anyone please help to where we could drill down the IVA details?...

Resize Register Columns
The product review for Money 2005 says when using the advanced register you can resize columns and sort by column. I can do the sort, but I have yet to figure out how to resize the columns. For instance, the date column is nearly twice as wide as it needs to be. And since most of my transactions deal in the thousands and not billions, I don't need the Payment, Deposit and Balance columns nearly as wide as they are. What I would ike is Number column wider (where you put check number, ATM, Debit, etc) so I can detail the transactions (how they happned) and see it just by looking a...

How to group all data from 1 column into several columns (Redo)
I have 2 columns in my spreadsheet, Company Names and Event. The Event column values are Event 1, Event 2, Event 3, Event 4 and the company names repeat for each of the events that they attended. So sometimes I might have a company listed 4 times with each Event corresponding to it in the next column. How can I consolidate all of the company names so that there is 1 row for each company and all events are on the same row but in different columns marked with an x (the column labels will be Company name, Event 1, Event 2, Event 3, Event 4). This is what I have... Company Name...

macro button refers to prev. workbook?
Hi, When I create a macro in Excel, and assign a button to it, it works fine the first time, but when I re-open the workbook for a second time, and try to run the macro again, the macro seems to refer to the previous workbook...For some reason, the macro saves the name of the previous workbook... Sub test() ' Dim name As String Dim adress As String Sheets("sheet1").Select Range("A1").Select name = ActiveSheet.Range("B6").Value adress = ActiveSheet.Range("B4").Value ActiveWorkbook.SaveAs Filename:="C:\WINDOWS\Desktop\Temp...

want questions on EXCEL-2007
Hello Everyone, I want some questions on EXCEL-2007 for checking competency level of the user. Please help. -- Ananta Gupta Hello Ananta. Go to this page:- http://www.ecdl.org/programmes/index.jsp?p=109&n=185 There are sample test papers here. Go to ECDL / ICDL Advanced Syllabus Version 2.0 and, in that section, VISTA 2007 then Sample Test Paper (VISTA 2007) - ECDL / ICDL Advanced Spreadsheets. There are 20 questions in here that you might like to ask. Don't worry too much about the VISTA bit as that is not important (I have tested it on other oper...

Column and Row reference
How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top and right hand side references Dave Tools>Options>View. Uncheck "row and column headers". Good idea to spend a few minutes browsing through the various Tools>Options tabs to see what else is available to toggle on/off. Gord Dibben Excel MVP On Mon, 19 Jul 2004 15:53:02 -0700, "Dave" <Dave@discussions.microsoft.com> wrote: >How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top an...

How do I copy part of each cell in a column?
I have a column of data - ie: (1APB-0101) BFP Motor Valve I want to copy the information in ( ) to another column. How can I do this? =MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1) and fill down "I should know this!" <I should know this!@discussions.microsoft.com> wrote in message news:6DF67909-D5BF-4F62-A9FE-0AFB6C979279@microsoft.com... > I have a column of data - ie: (1APB-0101) BFP Motor Valve > I want to copy the information in ( ) to another column. > How can I do this? > One way: If all the items inside parens in th...

How do I convert columns of data to rows in excel?
I am trying to convert 1 column of data into a row. Is there a simple way to do this? Copy/Cut your row and rightclick the destination. Choose "Paste Special" and check the "Transpose" option. That should do it! -- -SA "Glen in Pen" wrote: > I am trying to convert 1 column of data into a row. Is there a simple way to > do this? I just tried the Cut/Paste Special... and it's not available. You can only do this using the Copy function. Weird... -- -SA "StumpedAgain" wrote: > Copy/Cut your row and rightclick the destination....

Question about Microsoft Excel file: PROPLUS.MSI
Hi Everyone, I'm trying to help a friend out. When opening Excel it starts an installation period. During installtion it stops. The message that comes up is, "The path 'C:\documents and settins\EG\my documents\office extracted\PROPLUS.MSI cannot be found. Verify that you have access to this location and try again. Please help, I'm lost with this one. If it helps my friend recently had a new proccesor installed and all spyware removed. Could any of those two things cause the file to be lost. -- Thank You Kindly, Harpua ...

Changing rows by Columns
Hi, I have a query. I need to show the result of this query changing rows by columns in the table of my report. How I can to make it in SSRS 2005? Best regards and thanks!! Julio I would T-SQL , a new PIVOT command does that "julito" <julito@discussions.microsoft.com> wrote in message news:E79F47EB-504E-494B-AA4F-EFEEFF510A8D@microsoft.com... > Hi, > > I have a query. I need to show the result of this query changing rows by > columns in the table of my report. > > How I can to make it in SSRS 2005? > > Best regards and thank...