Carring over data from one sheet to another

[spectraflame]

I have a column that can have 1 - 24 entries. These entries are not taken every hour and are not necessarily taken at the same hour each day. To reduce data entry. The first day of the month is keyed in manually. The remaining days, on separate sheets in the same workbook, have manual data also, but a great deal is carried over from the previous day. If the previous days data was in the same spot every time, this would not be an issue, but they sometime end in cell A12, but some end in A9 or A4. Is there a way to have the value of the last entry in that column carried over to the next sheet?

So if my last entry on the 26th of JAN was 30% at 9:00 PM, my carried forward rate would be 30% for the beginning of the 27th of JAN.

Any suggestions?

 

[Aladin Akyurek]

=LOOKUP(9.99999999999999E+307,A:A)
This message was edited by Aladin Akyurek on 2003-01-27 13:58

 

[Jaafar Tribak]

Hi Matthew,

In the Sample worksheet below,cell E1 always returns the last entry in Column A and it is to be carried Forward to the next sheet (Sheet2) in cell A1.

A1 in Sheet2 will therefore contain the Formula : =Sheet1!E1.

Continue the same with the subsequent sheets.

Book1
	A	B	C	D	E	F	G
1	10%	12:00:00AM		LastEntry:	30%
2		01:00:00AM
3		02:00:00AM		Enter=E1InCellA1ofSheet2
4		03:00:00AM
5	15%	04:00:00AM
6		05:00:00AM
7	10%	06:00:00AM
8		07:00:00AM
9		08:00:00AM
10	30%	09:00:00AM
11		10:00:00AM
12
13
Sheet1

Let me know if any problems.

Jaafar.

 

[Aladin Akyurek]

On 2003-01-27 14:39, rafaaj2000 wrote:
[...]Let me know if any problems.

INDIRECT, ADDRESS, also array-entered... That's a lot of volatility.

 

[spectraflame]

Aladin,

You suggestion works well for what I am trying to use it for. Is there a way to modify your example to work with cells that are formatted as just text?

EX: I have some cells that have entries like 50/50 or 20/20. I would like to be able to copy those over as well.

EX: I also have columns that contain whole numbers and well as things like 26% or 42%. Is there a way to extract just the final number that has the "%" sign?

Thanks again,
Matthew

 

[Aladin Akyurek]

On 2003-01-27 15:52, spectraflame wrote:
Aladin,

You suggestion works well for what I am trying to use it for. Is there a way to modify your example to work with cells that are formatted as just text?

EX: I have some cells that have entries like 50/50 or 20/20. I would like to be able to copy those over as well.

EX: I also have columns that contain whole numbers and well as things like 26% or 42%. Is there a way to extract just the final number that has the "%" sign?

Thanks again,
Matthew

If col A has text as underlying format, the following will fetch the last value in A...

=INDEX(A:A,MATCH(REPT("z",90),A:A))

The earlier formula does the same for a column/row/range of numeric type.

I need more info on the 2nd example. It looks like you have

73
6
3%
8
9%

9

and you want 9%, that is, last percentage value. Right?

 

[spectraflame]

You are correct. In your example, I would like to see 9%.

Matthew

 

[Aladin Akyurek]

On 2003-01-27 16:26, spectraflame wrote:
You are correct. In your example, I would like to see 9%.

Matthew

I hope you don't mind creating an additional column and applying the LOOKUP formula with the big number to that column...

Book6
	A	B	C	D	E
1	73			9%
2	6
3	3%	0.03
4	8
5	9%	0.09
6
7	9
8
Sheet1

 

[spectraflame]

I am not sure that I fully understand your latest example. Can you explain a little further?

Matthew

 

[Aladin Akyurek]

On 2003-01-27 16:46, spectraflame wrote:
I am not sure that I fully understand your latest example. Can you explain a little further?

Matthew

In A1:A7 you have the values of interest.

The formula in B1 (copied down as far as needed) determines whether the value in A1 is a percentage. If so, B1 takes the value of A1, otherwise B1 stays blank. The LOOKUP formula is then applied to B to fetch the last percentage value.

Matthew, there is a difference between 0.02 (an ordinary numeric value) and 2% (formatted as such). My setup exploits the existence of values like 2%. If you don't have such values in A and rather values 1 and< 1 are the percentages, we need a different approach. Is this the case (reminding me of your earlier query)?
This message was edited by Aladin Akyurek on 2003-01-27 17:00