Excel Question

[imacbad]

Quick question, I'm trying to have excel fill a formula across a row of cells horizontally and when I do it changes the columns (letters) of the formula but what I want it to do is change the rows (numbers). I know it would do this if I was filling it down vertically, but the project i'm working on it has to be orginized horizontally. I put a $ in front of the letter so it doesn't change the letter but it still won't change the number in the formula. If anyone can help it would be greatly appreciated cause changing all these cells by hand would be a bitch.

 

[isasir]

Could you do it vertically, then copy and paste the values (transpose) horizontally?

 

[minendo]

Post a link to the file or a similar one so we can see exactly what you are trying to do.

 

[imacbad]

isasir understands, i did that last time but i'm working on such a huge file this time it would just be so annoying and I thought there might be a way. basically, imagine your dragging and filling a formula but excel is changing the wrong variable for you trying to be helpful but overall just pissing you off cause its not filling the formula right.

 

[minendo]

Originally posted by: imacbad
isasir understands, i did that last time but i'm working on such a huge file this time it would just be so annoying and I thought there might be a way. basically, imagine your dragging and filling a formula but excel is changing the wrong variable for you trying to be helpful but overall just pissing you off cause its not filling the formula right.

Where are you getting your data for the formula? Other cells, row dependent, etc? Just trying to recreate your problem so I can help.

 

[isasir]

Originally posted by: minendo

Originally posted by: imacbad
isasir understands, i did that last time but i'm working on such a huge file this time it would just be so annoying and I thought there might be a way. basically, imagine your dragging and filling a formula but excel is changing the wrong variable for you trying to be helpful but overall just pissing you off cause its not filling the formula right.

Where are you getting your data for the formula? Other cells, row dependent, etc? Just trying to recreate your problem so I can help.

From what I gathered, A1, A2, A3, etc. have data. Now instead of B1, B2, B3 referencing the A column (which Excel does with ease), he wants B1, C1, D1 to reference A1, A2, A3.

 

[imacbad]

other cells above. horizontally is different financial data for a company, vertically are 15 different years. now below i have the years arranged horizontally and financial ratios i'm trying to calculate vertically. I want to put a formula, lets say a1/b1, as the financial rato and then drage it to the right so that it becomes a2/b2, a3/b3 horizontally so i calculate the ratio for all the years. i know i should have just set up the ratio portion different but i already did all this using a drag/drop method and there were errors in 3 of the formulas so its just gonna be so annoying to do this all by hand if i could just put a symbol or something in and have it do the work for me

 

[petesamprs]

This how excel normally works. The first question I have is why is the data vertically presented if you need to display it horizontally? The second question is is the formula some sort of lookup of the vertical data or just formula applied to all the vertical data one by one but just displayed horizontally?

I'm guessing your situation is the latter, in which case you can use the ADDRESS() function to artificially create a cell reference that moves rows (down) as you move columns (across).

 

[imacbad]

yeah thats exactly what i need to do, how do i use the address function?

 

[petesamprs]

The help function explains how to use the function pretty clearly. Type in "Address" in the help box and it should be the first choice.