Non-positional cell formulas?

Ok, I have a spreadsheet with multiple columns, for example:

Date |       spanning header1     |        spanner header2     |
spanning header3      | Average   header      |
        |  subhead1a | subhead1b |  subhead2a | subhead2b |  subhead3a |
subhead3b |                                |
date  |  s1adata     |  s1bdata     |  s2adata     |  s2bdata     |
s3adata     |  s3bdata     | average s1a,s2a,s3a |

Now for every row, I need to calculate the subtotal data.

No problem with the formula (like in =AVERAGE(B3,D3,F3)). The next line's
formula would be =AVERAGE(B4,D4,F4).

The problem is that I don't want to have to enter the "hard-coded" reference
to each column unique to each line (since I potentially have a hundred rows
of data) I would like just duplicate the formula in each line and let Excel
figure out the relative columns.

I THINK variable names might be the solution, but is there a better way
and/or a built-in way to handle this seemingly simple problem?

Thanks,

Marc


0
ries (5)
9/14/2004 6:51:24 PM
excel.newusers 15348 articles. 1 followers. Follow

1 Replies
513 Views

Similar Articles

[PageSpeed] 37

You don't need to.  Type the formula in one cell as you have done and then 
select that cell and do Edit / Copy.  Now select the range you wish to fill with 
the formula and do Edit / paste and it will all adjust automatically.

Shortcuts include just grabbing the little black cross at the bottom right of 
the cell and dragging down, or just doubleclicking that same little black 
square.

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Private Person" <ries@ccountry.net> wrote in message 
news:10kef5ke9qefh3d@corp.supernews.com...
> Ok, I have a spreadsheet with multiple columns, for example:
>
> Date |       spanning header1     |        spanner header2     |
> spanning header3      | Average   header      |
>        |  subhead1a | subhead1b |  subhead2a | subhead2b |  subhead3a |
> subhead3b |                                |
> date  |  s1adata     |  s1bdata     |  s2adata     |  s2bdata     |
> s3adata     |  s3bdata     | average s1a,s2a,s3a |
>
> Now for every row, I need to calculate the subtotal data.
>
> No problem with the formula (like in =AVERAGE(B3,D3,F3)). The next line's
> formula would be =AVERAGE(B4,D4,F4).
>
> The problem is that I don't want to have to enter the "hard-coded" reference
> to each column unique to each line (since I potentially have a hundred rows
> of data) I would like just duplicate the formula in each line and let Excel
> figure out the relative columns.
>
> I THINK variable names might be the solution, but is there a better way
> and/or a built-in way to handle this seemingly simple problem?
>
> Thanks,
>
> Marc
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.760 / Virus Database: 509 - Release Date: 10/09/2004 


0
ken.wright (2489)
9/14/2004 10:00:49 PM
Reply:

Similar Artilces:

Creating a field to search and enter non duplicate data
Hello all. I have a team of 15 pople that all work with reference numbers, on occasion a number has to be sent to a different work group. I've created a spreadsheet for them to enter the numbers into, but we have been duplicating numbers. I want to create a field to enter th number into, have the field cross reference the exsting numbers, and if it does not find a match, enter the number and sort the list. I know how to create a custom macro that will accomplish what I want to do, but I'd prefer a static formula if it's possible. A formula can not do waht y...

record a date a cell was changed
I need to record the date a particular cell is filled or changed. I was thinking I could create a formula that would enter the current date whenever anything is entered into a cell. For instance when cell A1 has something entered or changed in it A2 gets the date the cell was changed placed in it. Any good ideas? Ian, here is one way, Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count > 1 Then Exit Sub If .Column = 1 And .Row = 1 Then 'change Now to Date if you don't want the time .Offset(0, 1).Value = N...

non
...

How do I prevent cell resizing in pivot table.
Hi Folks, quick question: Is there a method to prevent Excel from automatically resizing cell when it refreshes data ? For example: I have a pivot table that uses an access query; sometime it returns no values, which is what I want. The problem is that whe no values are returned, it resizes all the pivot table fields and thi messes up my headings and the like. Could someone help? Thanks in advance Do -- Message posted from http://www.ExcelForum.com Nothing you can do about it. Nowadays if I need formatting I link another sheet to the PT and us VLOOKUP values etc -- Message posted fr...

hiding 0 from a cell
I can't figure out why this doesn't work, I keep getting circular references. i have 3 workbooks. 1 is a summary linked to other 2 which are stored in a different drive. The summary is I:\abc\etc\[summary.xls]sheet 1'!$d$6). At the moment the cell inwhich this reference sits has 00:00 which is mm:ss. I want this cell to remain empty until the cell from which it is linked is populated with a figure. so =if(c4=0,'','I:\abc\etc[summary.xls]sheet1'!$d$6) The response is "the formula you typed contains an error etc etc. I followed what I thought I had to but...

Query a non-MS/non-Active Directory LDAP Server
Hi All, Does anyone know how to query an LDAP server from Access VBA that is not a Microsoft / Active Directory (AD) server? I have found a lot of stuff about how to query AD and have succeeded in doing so. But that does not seem to transfer to the non-MS LDAP server. It gives the error "Automation error" "There is no such object on the server." Thanks, Clifford Bass -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201004/1 Clifford Is there a chance that there...

Non Delivery Strangeness
I rebooted Exchange 2003 SP2 and received the following NonDelivery email on 1/3/2006 for an email sent on12/15/2005. I and other users received a few of these and the server seems to perform normally for the 100's of other emails that are sent and received daily. Thanks, Dan Foxley ---------------------- Your message did not reach some or all of the intended recipients. Subject: Purchasing From Your Site Sent: 12/19/2005 2:05 PM The following recipient(s) could not be reached: smartservice@smartwool.com on 1/3/2006 10:17 AM The e-mail system was unable to deliver the message, but d...

Find first previous cell with data
Hi, I am not "fluent" in Excel, so please make any answers "dummy" level. I have a running balance column that spans multiple pages and I am trying to get the last balance from the previous page onto a cell in the following page. Additionally, there are column headings at the beginning of each page and I have all cells (except column heading cells) in this balance column conditionally formatted to not display duplicate balances, so there are cells that appear blank but technically are not. I read a post in General Questions dated 1/16/2006 by Derby Jim and answered ...

Maximum number of characters in merged cells
Is there a maximum number of characters a merged cells will hold? I have a text field of merged cells however, after about 11 rows all of the text does not show even though it looks like there is room for the additional text. Bill, Do a search in help with "Specifications" and you will find: Length of cell contents (text): 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar Bernard "Bill" <anonymous@discussions.microsoft.com> wrote in message news:A2366FCA-2E93-42E3-82A5-92316657DF4C@microsoft.com... > Is there a maximu...

Indirect Cell Reference
I want to find the maximum value in a variable-length array Currently, I use folloiwng to find max value of 1000 - element array =MAX(C1:C10000) However, only the first 1500 of these cells are populated, and I wish to exclude the last two populated cells from the MAX expression Cell E1 contains the count (1500) E1 = 1500 Cell E2 contains the # cells to exclude E2 = 2 How do I use E1 and E2 references in my MAX expression to obtain an equivalent to the desired expression =MAX(C1:C1498) Thank you, John try this where you are trying to match a number larger than possible in your...

Referring to cells by range names #7
no change. but i appreciate the continued interest -- rllane4 ----------------------------------------------------------------------- rllane47's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1647 View this thread: http://www.excelforum.com/showthread.php?threadid=27830 It is not clear to me what you tried, what did work and what didn't. I tested my answer: ---------- =INDIRECT(Q5) INDIRECT(T3) Space after (Q5) ----------- Did you? -- Kind Regards, Niek Otten Microsoft MVP - Excel "rllane47" <rllane47.1ftply@excelforum-nospam.com> w...

VLOOKUP returns formula instead of result
When I do a VLOOKUP formula, the cell returns the text of the formula instead of the result. I get the correct result when looking the in Formula Wizard grey box, but when I get back on the worksheet only the formula is displayed. This is not a case of Ctrl+~ to look at formulas. Any suggestions??? I would say your cell was pre-formatted as Text. Format to General then F2 and ENTER. Gord Dibben MS Excel MVP On Tue, 16 Mar 2010 14:50:01 -0700, slavvy11 <slavvy11@discussions.microsoft.com> wrote: >When I do a VLOOKUP formula, the cell returns the text of the f...

Combining 2 cells to be put into 1 cell with Vlookup
Hi Basically, what I want to do is combine firstname and surname which ar located in different columns - side by side though - the function I'm using is - =VLOOKUP($D$3,Order!B5:V60000,6,0) Which posts the firstname into the cell I want, is it possible to ge the surname AND the firstname into the same cell with a variation o this function? Thanks for any hel -- Message posted from http://www.ExcelForum.com Hi Souljah, =VLOOKUP($D$3,Order!B5:V60000,6,0)&" "&VLOOKUP($D$3,Order!B5:V60000,7,0) -- Kind Regards, Niek Otten Microsoft MVP - Excel "Souljah &...

Non-cash, Non-regular Transactions
Money 2003 SE V11 I have a need to set up transactions, such as Mileage, to keep track of non-cash activities. I currently handle it by setting up Bills & Deposits split into two transactions totalling $0.00. The first is the Category/Subcategory I want to track and the second is a Category/(Non-Cash Offset Subcategory) that I filter out of all reports. This works ok for me. Example: (meaning 23 miles traveled) Mileage:Medical/Dental 23.00 Mileage:Offset Non-cash Transaction -23.00 However, the transaction is not regularly occurring. Yet we are required to ...

Re-post:Formula Finesse Needed: COUNTIF or DCOUNT or Something bet
Hello, I might have posted my original question in the wrong area initially. The answer i recieved from the General Excel forum was way beyond my ken. Please see the post below and the answer I recieved. If someone could explain how the answer works or send an alternative answer that would be great. "Sean Timmons" wrote: > OK, make sure your months are actual numbers.. Jan would be, ay, 1/1 > formatted as mmm. > > in A2, > =SUMPRODUCT(--(ISERROR(SEARCH($A2,Sheet2!$A$2:$A$500))=FALSE),--(month(Sheet2!$B$2:$B$500)=month(B$1))) > > should get it > &g...

NON TAXABLE Item Setup, What TAX CODE to assign
What is the proper way to set up a non taxable item like a coupon or labor? I was assigning the <not assigned> (RMS dbase Tax ID 0) (But I just read said to delete the <not assigned> tax code. to prevent a runtime error (we have not had yet)) Do I need to setup a Non tax tax code? and add all non tax items to it? PS I have a < > (blank)(RMS Dbase tax id 3) tax code that seems to need to be deleted, RMS assigned it tax code 3. There are currently no items assigned to the < > blank tax id I can not find any guidance in the RMS Help. It seems MS ex...

Help with an IF formula please.
I have the following formula in a spreadsheet. Any value I use in the M column works EXCEPT F10. For some unknown reason, the formula still appears to be good, but does not return a true answer. =IF($M3="F10",72.4+2.7,IF($M3="F11",72.4+2.7,IF($M3="F12",72.4+2.7,0))) I will greatly appreciate any help! Thanks. -- rbp ------------------------------------------------------------------------ rbp's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20456 View this thread: http://www.excelforum.com/showthread.php?threadid=385401 rbp,...

Run-Time Error on Cell Range Select
I get a run-time error 1004 on the .Select line. Can someone suggest a fix? Sub FindDate() Dim rn As Long, offset As Long Dim stdate As Date, actdate As Date actdate = Range("Z2").Value rn = IsoWeekNumber(actdate) If rn / 2 <> Int(rn / 2) Then rn = rn – 1 stdate = Range("A" & rn + 6).Value offset = actdate – stdate Range(Cells(rn + 6, 3 + offset), Cells(rn + 7, 3 + offset)).Select End Sub Phil- My best guess is your use of the offset statement, but here goes a longer response just in case that isn't it. What are your variable values...

NDR from non-existent email address
Any user that sends a meeting request to the user in question gets the following responce. Your message did not reach some or all of the intended recipients. I changed the info to the innocent. Subject: test Sent: 8/16/2005 9:01 AM The following recipient(s) could not be reached: Gay, Esse M. on 8/16/2005 9:02 AM The recipient name is not recognized The MTS-ID of the original message is: c=US;a= ;p=XXX;l=XXXXXXX-XXXXXXX MSEXCH:MSExchangeMTA:EXCHANGE:XXXXXX This only happens with meeting invites, regular email goes through just fine. It is jus...

mail from non exsiting user accounts
Hi, I have one exchange 2003 cluster server. and the email address configured as user@corp.ho.com. We are not using it for external mails. one of our IBM AIX user is able to send mails to anybody in my exchange server eventhough he is not having mail account in the windows 2003 domain. if u check the ID of the sender u will see that he is also using the same domain name ie @corp.ho.com... How can i stop others who do not having account in exchange to send mails to my domain. thanks BM Uncheck "Anonymous access" from SMTP virtual server properties | Access | Authentication and r...

i need to add more options to a formula but i don't know how
hi, on one of my worksheets i use this formula =SUMIF(G126:G242,"UNPAID",F126:F242) this tells excel to add up all our unpaid invoices and tell me how muc we are owed. I now want the formula to add up all the boxes with unpaid next t them, plus all boxes with these phrases next to them: received-not banked yet banked-not cleared yet so i have tried things like thi =SUMIF(G126:G242,"UNPAID(or)received-not banked yet(or)banked-no cleared yet",F126:F242) but it does not work. as you will have guessed i am not too great wit excel. :confused: incidentally, unpaid, re...

Find duplicate cells
Is there a way to find all the cells with identical entry? I'm trying to find duplicate information but I don't know what the duplicate entries are. Find and replace does not have an option to find something like that. TIA, cpliu Hi have a look at http://www.cpearson.com/excel/duplicat.htm#TaggingDuplicates http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates -- Regards Frank Kabel Frankfurt, Germany "cpliu" <chanciusliuDeleteThis@yahoo.com> schrieb im Newsbeitrag news:Xns94A16109E4C87chanciusliuDeleteThi@130.133.1.4... > Is there a way to fi...

Cell comments
I have already created the code using VBA to pull the data from the M Access table. Is there a way to set it up so that when you pass ove the cell it will automatically run my routine -- LA ----------------------------------------------------------------------- LAF's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=965 View this thread: http://www.excelforum.com/showthread.php?threadid=26920 Hi not possible AFAIK as there's no event which got triggered by just moving over a comment field -- Regards Frank Kabel Frankfurt, Germany LAF wrote: > I have alr...

getting numbers in cells into formula
Hi everybody, I have the following question: Imagine that I have numers in column A, range A2:A30. With a formula I found the row number from which I want to start calculating and placed in cell C1 (let's say 7). Another formula in C2 finds the stop value (let's say 20). Is there a way to totalize values in the range found by the formulas in C1:C2? In other words, I'm looking for a formula that totalizes the following: sum(A&value in C1:A&value in C2), so that excel would interprete this as sum(A7:A20). Has anyone tackled this problem before? I'm very intereste...

Cursor position
Hi All, I have written a small VB script to collect data from the user. At the end of data entry a YesNo response pops up. When the user hits Yes I would like the cursor default position should be the first text box in the new window. How can I do that. Appreciate your help. Syed -- saziz ------------------------------------------------------------------------ saziz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6350 View this thread: http://www.excelforum.com/showthread.php?threadid=394290 "saziz" wrote: > > Hi All, > I have written ...