You're quite right, they are really quite messy because of exactly what you have noticed, being cell based. This is also what makes them so prone to error, but also so easy to use for people who don't realise they are actually writing programmes.
The real answer if determined to use a spreadsheet is probably to use macros in OO, or VB in Excel. But the last time I tried to do anything serious in a spreadsheet, I did what the other writer said, just pasted the formula into the whole column.
The other problem with them is they are so slow. The last time I did this, I found it necessary to do all kinds of lookups and use a matrix. Well, it started to take minutes to load the thing. Not to get the report, to load the thing in the first place, because of all the automatic calculation that was happening as it loaded.
Yes, you can turn that off. But its a total kludge.
Same thing done in high level scripting language, you just put the data into variables in memory, then do 'for each x...' and it takes a couple of seconds.
Sorry, I was just hoping a spreadsheet would be a cleaner/tidier solution to my requirement than it actually is.