"Entering" Data and column/row swaps

[aGrimmjob]

ok, I have a ton of data for work that is set in rows, and i need it in columns. So my first question would be, is there a simple way to do this?

Every time I'd try to fill a series of data (to auto copy the trend i was trying to make apparent to excel) i couldn't make it auto fill in the equations correctly. if you have =G10, for example, and you auto fill DOWN... it'll keep the G and trend the 10 to 11, 12, whatever. And auto filling across mean it'll keep the 10, and auto trend the G to and H, I , ect. The best i could do is initially manually convert one entire row to a column and using =$G10 I'd copy across (but now i have to manually change numbers).

Anyone have a bright idea for getting this done?

in the meantime, i figured out that excel CAN TREND the way i want if i just enter TEXT! so entering just G10 G11 , and auto filling across the row DOES work. But after using a ="="&G10 and copying the VALUE ... i'm left with a bunch of cell that I could make (kinda efficiently) that just show =G10 or =G11 .... to get excel to RECOGNIZE that they're equations, i have to manually click on each cell, hit enter, and THEN it magically becomes the equation.

So is there any way to highlight all cells and..say.... auto enter or evaluate them or something?

[Pete_UK]

You could use INDIRECT (which is volatile) or INDEX (which is not) to do this. What's your starting cell with the formula =G10?

Pete

[aGrimmjob]

Well, i haven't used INDIRECT before, so is there a way to easily trend this function the right way so that I can save time here? I'm not sure how you mean I could be using this.

My starting cell is just a number. All the cells i'm dealing with are numbers for heat release curves I'm working with. The data is a little spread out though unfortunately so, for instance, first number i need (to be put in a set down a column) is in G3, then J3, then M3, then, P3 .... and it continues like this until something like DD3

So how is something volatile or not helpful here? =) i'm sorry, i've done many things with excel throughout college, but i've only used, maybe 20 functions ever for solving equations and engineering stuff. Any help/direction is appreciated here.

[Pete_UK]

I meant which cell is it going into, but from your later description you have changed things. So, could you attach a file that demonstrates exactly what it is that you want to achieve, i.e. where you want to get the data from (the cells), and where you want that to go.

Pete

[aGrimmjob]

ok, i'm having trouble attaching a file here, but basically what i'm looking to do is take data from a ton of rows like A1:DD1 and convert that to columns like A1:A100.

And i'm trying to do this for enough bits of data that i really don't want to have to do anything at all to INDIVIDUAL CELLS. I'm trying to figure out how i can use some kind of table feature? or some method of using the fill handle.

[Pete_UK]

Okay, let's take that particular example of copying data from B1:DD1 into A2:A100 - the A1 cell is common to both ranges. Put this formula in A2:

=INDEX($B$1:$DD$1,ROW()-1)

Then you can just copy it down. If you have blanks in your initial range, you might like to change it to this:

=IF(INDEX($B$1:$DD$1,ROW()-1)="","",INDEX($B$1:$DD$1,ROW()-1))

Hope this helps.

Pete