Need help autofilling LETTERS down and NUMBERS across which reference cells in formula

[photodotexe]

Hey there,

Here is my formula:
=IFERROR(LEFT(SUBSTITUTE([standardexchange.xlsx]Table 1'!C3,",","."),FIND(":",[standardexchange.xlsx]Table 1'!C3)-1)/RIGHT(SUBSTITUTE([standardexchange.xlsx]Table 1'!C3,",","."),LEN([standardexchange.xlsx]Table 1'!C3)-FIND(":",[standardexchange.xlsx]Table 1'!C3)),SUBSTITUTE([standardexchange.xlsx]Table 1'!C3,",",".")+0)

What this does, is it extracts data from the cell C3 in an external worksheet. The data it extracts looks like this 1:8,9 or 9,2:8. It then takes that, and divides it into a decimal so it looks nicer. That part works great.

The only problem is, I need to auto-fill it down. However, when I auto-fill that down, instead of doing D3, E3, F3 - it does C4, C5, C6. I understand this is by design, but I'd like to make it stop that and do D3, E3, F3.

Another issue is, when I auto-fill to the right, it does D3, E3, F3 when I want it to do C4, C5, C6.

So, basically I want to reverse the auto-fill default functionality. Instead of filling numbers vertically, I want letters. Instead of filling letters horizontally, I want numbers.

I've spent hours on this and I've found a few things that work stand-along (like CHAR) but, when I use them to reference a cell in this complicated formula - not so much.

Any ideas? THANKS!!

[Jonmo1]

Try entering the formula in one cell and fill it down/across as needed
I know, that's not the result you want..keep reading

THEN, Highlight the whole range of formulas, and use Find/Replace to replace = with '=
This turns the formulas in to text strings.
Copy the range, Paste special - Values - Transpose
Then reverse the Find/Replace to replace '= with =
Turns them back into formulas.

[photodotexe]

Thank you for the thought!

I'm afraid doing that got me only so far. At the paste - transpose section, I get an error saying "This selection is not valid. There are several possible reasons:"

Sorry, anything else? :D

[Jonmo1]

Oops, the paste spcecial transpose can only go into a blank area...

Try putting the formula in an available unused area (outside of where you actually want the result to go)
fill it accross/down
Do the find/replace
Copy it

Now paste special - values - transpose onto the area (make sure it's blank and large enough) you want the result to be.
Then reverse the find/replace.

[photodotexe]

You sir, saved me literally 30 or more lunch hours. Thank you, thank you, thank you!

[Jonmo1]

You're welcome