Locked-in vs. variable parts of a function

[DesertShrimp]

I have a file with multiple worksheets. The worksheets are labeled Jan, Feb, etc. Each worksheet has the exact same text with variable numbers (it is questions on a survey with the subsequent data). Following the worksheets (12 for 12 months) is an additional worksheet that acts as a data funnel to combine the data from all 12 previous worksheets into a single worksheet.

In the first 12 worksheets, I have a few survey question results that are listed horizontally (i.e. Satisfied, N/A, Unsatisfied) with their data listed below. In my data funnel worksheet, I need to list these answers vertically instead of horizontally.

What I want to do is create a formula that allows me to lock-in the row number while allowing the column letter be variable. This way when I type the formula in my 'data funnel' workbook, I will be able to click and drag that formula down the column and I will be receiving data from my other workbooks which are listed horizontally.

I can't seem to figure this one out. Thank you

 

[Jonmo1]

Welcome to the board...

Try this in the 'data funnel' sheet.

=INDEX('Other Sheet'!$1:$1,(ROWS(A$1:A1)-1)+1)

$1:$1 is the Row # to retrieve data from.
Don't change the (ROWS(A$1:A1)-1) part, it has nothing to do with your data.
The +1 represents the first column to retrieve data from.
So if you want to start in say Column C, you would change that to +3
C is the 3rd column..


Hope that helps.

 

[DesertShrimp]

That didn't work. I think I made a disaster trying to describe my issue. Let's do a very simple example of what I want.

If I were to create a blank sheet and then cell A1 I put a number and do the same in the next three cells in that row, so A1, B1, C1, D1. Then, somewhere else on the worksheet, I click in a cell and for the formual I put "=A1". Obviously that cell will now be equal to the value in cell A1. If I click on that cell and drag the formula over across the row, then I will get the same values I had in B1, C1, and D1 as well. Basic excel.

What I want to do:
Take the same values in A1, B1, C1, D1. Somewhere else on the worksheet enter a formula similar to "=A1" but this time I want to drag that formula down the column, but still retain the values from row 1.

Visually it would look like this:

A1 B1 C1 D1

to

A1

B1

C1

D1

What is the formula that allows me to do that? Right now if I click and drag it changes the row number instead of the column number. Is that possible?

 

[Jonmo1]

works for me

Excel Workbook
	A
1	this
2	data
3	is
4	across
5	row
6	1
7	in
8	Sheet2
Sheet1

Excel Workbook
	A	B	C	D	E	F	G	H
1	this	data	is	across	row	1	in	Sheet2
Sheet2