excel help

[Andy™]

making a spread sheet, but when dragging formula down i need it to change letter instead of number.(letter changes when dragging sideways, but i need it to change when dragging down. i have a $ infront of the number to keep that the same.) just its a lot of work to change the letter in a large number of boxes

is there a symbol or something like $ but to make it change number, instead of keeping something the same?

 

[NozSpark]

If you do the formula in at least a couple of cells and then drag it down it should then do the changes as you want

 

[Andy™]

nope. tried it with 3, but it just repeats the 3, doesnt add to it

 

[Barx]

Do you mean you want to copy the formula down a column but instead of auto increment the rows, you want it to increment the column letter reference?

 

[Andy™]

the formula i have is

='AUG - OUT'!$N$3

i need to drag this down, and for 'N' to change to 'O, P...AC'

---------- Post Auto-Merged at 19:03 ---------- Previous post was made at 19:03 ----------

Do you mean you want to copy the formula down a column but instead of auto increment the rows, you want it to increment the column letter reference?

yes

 

[NozSpark]

[quote name='Andy

 

[Andy™]

doesnt work (the $ was when i dragged it right, so nothing would change, then i could alter the month)

now i need to drag it down and only change the letter, but it only does that automatically if you drag it sideways, not down

 

[Barx]

OK paste this into a1

=OFFSET($A$1,0,ROW(A1))

put some data across top row. Copy a1 down and watch what happens. Is this what your after.

If that is the effect you need, just a matter of tweaking your existing formula to include it.

 

[NozSpark]

Andy,,

you have just forced me to use Excel,,

and I can confirm that my solution does not work,,, sorry :coat

 

[Andy™]

i give up... just going to drag the formula and change the letters myself. isnt taking as long as i thought it would

 

[NozSpark]

what are you doing Andy??

 

[Barx]

I'm positive the offset function does the required action.

Your formula would become

='AUG - OUT'!N$3

='AUG - OUT'!(OFFSET($A$1,0,ROW(A1)))

I think lol

 

[Andy™]

what are you doing Andy??

new accounts sheet

basically, page 1 is a summary of everything (its the breakdown of outgoings from each month onto page 1 i needed the formula for)

once i get it all done & working properly itll go into the downloads section (i know there is one there, but it isnt really suitable for me. easier to take some ideas from it and start again)

 

[Barx]

That is assuming your info starts at 'a1'

 

[NozSpark]

Andy,,

I have an excel sheet like that if you want it,,,

Double entry book keeping with a sheet per month, a summary sheet, a profit loss sheet and a summary sheet IIRC..

PM me your email and I'll send you a copy.

 

[Barx]

Looks like he has started the manual editting. Probably no posting for him now till about 10:30. By which time, his eyeballs will be hanging out their sockets. lol.

Never been a fan of spreadsheets to be honest, especially for stuff like this. I tend to crack open Visual Basic and write myself a program. Alot more control over it's functionality...

 

[Andy™]

all done. took all of 5 minutes. including eating at the same time

there was only 192 letters to change....

 

[Voltimax]

Looks like he has started the manual editting. Probably no posting for him now till about 10:30. By which time, his eyeballs will be hanging out their sockets. lol.Never been a fan of spreadsheets to be honest, especially for stuff like this. I tend to crack open Visual Basic and write myself a program. Alot more control over it's functionality...

That's all well and good if you understand VB, but alot of folks don't.

 

[a1]

[quote name='Andy

 

[Andy™]

Come on then, you just can't come on here and say that it is all done.Tell us all, how you did it.

PS. Sandra phoned me to say that she knew how to do it - but she couldn't access the net. :_|

dragged the formula down (it stayed the same), and then changed each letter....