Live Chat & Podcast at 1:00PM Eastern on Sunday!
There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
Search
Business Applications
Tag Cloud
access acer asus batch bios bsod crash desktop driver drivers error ethernet excel freeze gaming gpu hard drive hardware hdmi internet laptop malware memory modem monitor motherboard netgear network printer problem ram registry router slow software sound trojan ubuntu 11.10 uninstall usb video virus vista wifi windows windows 7 windows 7 32 bit windows 7 64 bit windows xp wireless
Search
Search for:
Tech Support Guy Forums > Software & Hardware > Business Applications >
Solved: formulas w/named ranges in excel

Reply  
Thread Tools
higginsbl's Avatar
Member with 121 posts.
 
Join Date: Aug 2001
16-Apr-2009, 11:45 AM #1
Solved: formulas w/named ranges in excel
Is it possible to add two named ranges of multiple rows and get the answers to post in a third column in SEPARATE ROWS and yield a formula NOT a number?
ie: column A has figures for j\January. column b has figures for February. Put the answers of adding January to February in column C in separate ROWS without making the first formula for row 2 and then copying the formula down. (Name a2:a4 January; name b2:b4 February and then add January + February and get the totals in c2:c4) I did it through Data Consolidation, but it yielded the ANSWERS in the appropriate places but did NOT yield a formula so changing a figure in column a or b would not change the answer in column c.

january february total
4 5 9
4 6 10
4 4 8

Thanks for any information and help you can provide.

BL
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
16-Apr-2009, 12:00 PM #2
Hi there,

A couple of options you have here I guess. You could use Consolidation from another worksheet. You could also use the Subtotals feature to automatically add subtotals to your data. Of course this all depends on data structure. You have the structure you can use moderately easy for Consolidation, but not so easy for Subtotals, although it can be done.

Of course the other option is to use formulas manually. So in C2 you'd put =SUM(A2:B2), press Enter, select the cell again (C2), put your cursor over the bottom right corner and double click it.

HTH
slurpee55's Avatar
Computer Specs
Distinguished Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
16-Apr-2009, 12:05 PM #3
I'm not clear about what you want. Is it
ColA |ColB |ColC
january|february|total
4 |5 |=A2+B2
higginsbl's Avatar
Member with 121 posts.
 
Join Date: Aug 2001
16-Apr-2009, 12:07 PM #4
Dear Zack,

I definitely do not want to use two worksheets. From what I can tell subtotals doesn't actually put the answers in the format I want -- unless I'm not understanding what you mean. I am trying to skip the copy step to copy the formula down the column, so that part is obviously a way to do it, but it's what I was trying to avoid. (If the the two columns were REALLY long, it would be easier to name the ranges and be able to add -- or any other type of formula -- them in one step.

BL
higginsbl's Avatar
Member with 121 posts.
 
Join Date: Aug 2001
16-Apr-2009, 12:09 PM #5
Yes, basically I want to add (or subtract or any other formula) two columns put the answer in the third column in separate rows. So if the columns were really long I could type @sum(January+February) and get each individual total in the total column.

BL
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
16-Apr-2009, 12:09 PM #6
I think the only thing shorter than typing the formula in and copying down (3 clicks?) is to use VBA and assign a keyboard shortcut. Consolidation would be nice, but as you've found out, no links are established unless you're on another sheet.
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
16-Apr-2009, 12:10 PM #7
Oh, there always is the Alt + = keyboard shortcut. Select C2 and hit Alt + =. Should enter an autosum for you. I think that may help.
higginsbl's Avatar
Member with 121 posts.
 
Join Date: Aug 2001
16-Apr-2009, 01:13 PM #8
excel ranges
Dear Zack,

I kinda figures, that wasn't going to be an option. Thanks for the ideas though.

BL
Reply

Tags
excel, named ranges, ormulas

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.
Thread Tools



Facebook Facebook Twitter Twitter TechGuy.tv TechGuy.tv Mobile TSG Mobile
You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 02:40 AM.
Copyright © 1996 - 2011 TechGuy, Inc. All rights reserved.

Powered by Cermak Technologies, Inc.