Get value from last cell. Flipping data

[Kevin8960]

I am working on a tool/worksheet that grabs the data from a user-inputted sheet (Sheet 1). The data the user inputs can vary in length, i.e. # rows.
The last cell entered by the user is transferred to the first cell in another worksheet (Sheet 2). I want to basically flip the data from how the user entered it.
Do I need to have another sheet that flips the user's data then link that to Sheet 3? Or is there a formula/function that will invert the data?
I would like to have something that I can drag down as a formula.
Keep in mind, the entered data can vary in # rows and therefore will not always be in a specific cell.
Thanks in advance!

 

[jeffreybrown]

Hi Kevin & Welcome to the Board,

You could use

B1 =INDEX($A$1:$A$12,ROWS(A1:$A$12))

 

[Ron Coderre]

Which version of Excel do you use?

 

[Kevin8960]

Which version of Excel do you use?

My business uses Excel 2002...

 

[Kevin8960]

Hi Kevin & Welcome to the Board,

You could use

B1 =INDEX($A$1:$A$12,ROWS(A1:$A$12))

Thanks for the quick response! I've been trying to use an INDEX formula...the only problem is that the data set could be 12 rows (i.e. A2:A13) or 6 rows (i.e. A2:A7) or 20 rows (i.e. A2:A21), etc. Is there a way to refer to the last cell with actual data in a column? Because currently, this formula is grabbing an empty cell and therefore is returning a value of "0."
Would adding an IF statement to exclude blank cells work? I'll try that and watch for your response! Thanks so much!

Kevin

 

[jeffreybrown]

Hi Kevin,

Are these number or letters?

For numbers

=INDEX($Q$1:INDEX(Q:Q,MATCH(9.99999999999999E+307,Q:Q)),ROWS($Q1:INDEX(Q:Q,MATCH(9.99999999999999E+307,Q:Q))))

For Letters

=INDEX($N$1:INDEX(N:N,MATCH(REPT("Z",255),N:N)),ROWS($N1:INDEX(N:N,MATCH(REPT("Z",255),N:N))))

Adjust the columns to meet your needs

Excel Workbook
	Q	R
1	1	6
2	2	5
3	3	4
4	4	3
5	5	2
6	6	1
Sheet2

Excel 2007

Cell Formulas
Range	Formula
R1	=INDEX($Q$1:INDEX(Q:Q,MATCH(9.99999999999999E+307,Q:Q)),ROWS($Q1:INDEX(Q:Q,MATCH(9.99999999999999E+307,Q:Q))))

You could also create a name in the name manager as BigNum which equals =9.99999999999999E+307 and then go with

=INDEX($Q$1:INDEX(Q:Q,MATCH(BigNum,Q:Q)),ROWS($Q1:INDEX(Q:Q,MATCH(BigNum,Q:Q))))

 

[Aladin Akyurek]

Thanks for the quick response! I've been trying to use an INDEX formula...the only problem is that the data set could be 12 rows (i.e. A2:A13) or 6 rows (i.e. A2:A7) or 20 rows (i.e. A2:A21), etc. Is there a way to refer to the last cell with actual data in a column? Because currently, this formula is grabbing an empty cell and therefore is returning a value of "0."
Would adding an IF statement to exclude blank cells work? I'll try that and watch for your response! Thanks so much!

Kevin

Something like:

=OFFSET(INDEX(Sheet1!$A:$A,MATCH(9.99999999999999E+307,Sheet1!$A:$A)),-(ROWS(A$2:A2)-1),0,1)

which is entered in A2 on the destination sheet while the data is in column A on Sheet1. The data is assumed to be numerical.

 

[Ron Coderre]

For text and/or numbers on Sheet1...

On Sheet2...

this formula records the row number of the last Col_A cell with any value:

B1: =LOOKUP(2,1/(Sheet1!A1:A1000<>""),ROW(Sheet1!A1:A1000))

This formula begins the list, in reverse order, of the values on Sheet1

A1: =IF(ROWS($1:1)<=$B$1,INDEX(OFFSET(Sheet1!$A$1,,,$B$1),$B$1-ROWS($1:1)+1),"")

Copy that formula down as far as you need.

Is that something you can work with?

 

[Haseeb Avarakkan]

Try in Sheet2,

B1,

=SUMPRODUCT(--(TRIM(Sheet1!A1:A1000)<>""))

A1, copy down

=IF(ROWS(A$1:A1)>$B$1,"",INDEX(Sheet1!A:A,LARGE(INDEX((TRIM(Sheet1!$A$1:$A$1000)<>"")*ROW(Sheet1!$A$1:$A$1000),),ROWS(A$1:A1))))

 

[Kevin8960]

Something like:

=OFFSET(INDEX(Sheet1!$A:$A,MATCH(9.99999999999999E+307,Sheet1!$A:$A)),-(ROWS(A$2:A2)-1),0,1)

which is entered in A2 on the destination sheet while the data is in column A on Sheet1. The data is assumed to be numerical.

Thank you! I decided to use this one. It works the best for my needs.