MS-excel Jedi masters in here.

tommyj27

tommyj27

Not really Banned
Ok, is it possible to do a fill down in excel with a formula that includes static and dynamic cell addresses. ok, that's confusing, here's what i want.

A1
A1+B2
A1+B3
A1+B4
.
.
.

where A1 is uniform but changes and Bx is unique per row. the only other way i can think of is to use a hidden column with the value of A1 like so,

A1
=A1 A2+B2
=A2 A3+B3
=A3 A4+B4
.
.
.

but that seems kind of half-assed.
 
Please explain this:

where A1 is uniform but changes

I think we can get you solved with $ in your cell references, but that line is throwing me.
 
If I read you correctly, you want column A to remain static while the row number changes? If so, use $A1 instead of A1. It'll lock the column.
 
=SUM($A$1+B3)

Then copy the cells where your formula lies to the other locations where you want your responces. :D

If you want...you could laways put the non-static numbers on another tab and refer to them...that way, you see the results but not the source.
=SUM($A$1+Sheet2!B1)
 
Professur said:
If you were talking Lotus, I'd have the answer.
Click to expand...
The coding isn't hugely different...hell, you can even tell Excel to accept Lotus 1,2,3 programming code :D
 
Professur said:
For the basic stuff, yeah. The higher level scripting doesn't work, tho.
Click to expand...
hmm... I havn't tried the heavier scripting stuff... I'll have to check that out.
 
MrBishop said:
If you want...you could laways put the non-static numbers on another tab and refer to them...that way, you see the results but not the source.
=SUM($A$1+Sheet2!B1)
Click to expand...

That's the way I always do it. Much easier to debug that way too.

Note that the $ thing should fix your fill down question, however (always assuming I understood the question correctly).
12
14 2
15 3
16 4
17 5
18 6
19 7
20 8
21 9
22 10
23 11
24 12
25 13
26 14
27 15
28 16
29 17

If I'm at work there is always an Excel window open on my desktop. :nerd:
 
chcr said:
If I'm at work there is always an Excel window open on my desktop. :nerd:
Click to expand...
I don't get as many chances to play with Excel as I'd like... though I used to use it to keep track of my sales, commissions, goals etc...when I worked sales.

Pretty little pie-charts with things like "Work harder you lazy bum" next to them, varying (via programming) on how close to my goals I was and the number of days until the end of the month. Fun fun!
 
Heh, I've got one that queries two UNIX databases, summarizes that info in three pivot tables then returns the data via various useful formulae (Julian to MM/DD/YYYY is a cool one, UNIX uses Julian dates) to a one page report and a one page pie chart (summarizes service labor by month by dept). They used to spend three or four days printing and then slogging through the printed reports to get the same info (which they then plugged into a hand-written spreadsheet which they made copies of). :D
 
MrBishop said:
=SUM($A$1+B3)

Then copy the cells where your formula lies to the other locations where you want your responces. :D

If you want...you could laways put the non-static numbers on another tab and refer to them...that way, you see the results but not the source.
=SUM($A$1+Sheet2!B1)
Click to expand...
that's what i was looking for exactly, forgot that you needed a $ next to each coordinate that stays static ($A$1, not $A1). I'm refining the spreadsheet I put together to keep track of student grades because I hate the actual grading package they have here.
 
Professur said:
Shame you spent the time on humourus programming instead of selling.
Click to expand...
Spent too much damn time selling, did the spreadsheet on a Saturday, at work :(

Got myself 'downsized' along with about 300 others theday after Independance day. My immediate boss didn't want to let me go :shrug: He even gave me the option to quit or get fired. I chose 'fired'..used my UI to re-educate myself and here I am again.
 
You must log in or register to reply here.
Back
Top