Text problems

[GordNYC]

1st. In cell G10, I have a to-from destinations. Eg LAX-SFO-DEN.
I would like to have the “-“ indicate that cells I10:R10 should be added together and the total put in cell T10.

2nd. At the bottom of a column where I have a total, I would like to have this sum automatically sent to the top of the next page, in the first cell, in the corresponding column. Should I do this on the same sheet or make another sheet?

I am using Excell 2000
I am trying to make a log book.
I have tried every fx that I can think of

 

[Chris Davison]

no replies....

I assume these are all Flight locations ?

is all your conditional data the same 11 character format ?

ie AAA-BBB-CCC

and LHT-PAF-BOL

or could some be direct flights ? : LAX-NYC

and LHT-BOL ?

for example....

 

[GordNYC]

Thanks for the response.

That is correct. These are destinations and I may have AAA-BBB, AAA-BBB-CCC or even four destinations. To complicate matters, some data in a cell in the same column might not be a destination but read “Training”.

I don’t know if you can help but I do appreciate the reply.

Thanks Gord

 

[Aladin Akyurek]

On 2002-03-27 09:22, GordNYC wrote:
1st. In cell G10, I have a to-from destinations. Eg LAX-SFO-DEN.
I would like to have the “-“ indicate that cells I10:R10 should be added together and the total put in cell T10.

2nd. At the bottom of a column where I have a total, I would like to have this sum automatically sent to the top of the next page, in the first cell, in the corresponding column. Should I do this on the same sheet or make another sheet?

I am using Excell 2000
I am trying to make a log book.
I have tried every fx that I can think of

In T10 enter:

=ISNUMBER(SEARCH("-",G10))*SUM(I10:R10)

Is this what you're looking for?

 

[Chris Davison]

2nd part :

(assuming your original sheet was called "flights")

in A1 on your new sheet, enter :

=INDEX(flights!A:A,MATCH(9.99999999999999E+307,flights!A:A))

and copy across as many columns as needed

(I'm assuming you just meant a different sheet, as opposed to the sheet with your above "-" formula in T1)

also, I borrowed this from Aladin's often-used solution, so he's technically answered both questions

 

[GordNYC]

Thanks to Chris and Aladin.
Very nice of you to reply.
The formula; =ISNUMBER(SEARCH("-",G10))*SUM(I10:R10) worked like a charm.
I was trying to do it with a text function.

The formula; =INDEX(flights!A:A,MATCH(9.99999999999999E+307,flights!A:A))
I have not yet used. I don’t know if I am going to repeat these pages on the same sheet or use individual sheets for each page of log.

Thanks again

Gord

 

[GordNYC]

I have tried the formula, =INDEX(flights!A:A,MATCH(9.99999999999999E+307,flights!A:A))
It does not seem to work. I may be using it incorrectly, but it is probably due to my poor description.

On sheet One, my template, which I need to copy many times, contains columns G:W.
In row 5 of these columns are the totals that are brought forward from the previous page (or sheet, I do not know which would be easier). The totals on the preceding page are in columns G:W, row 27.

I could just copy and past the totals from G:W row 27 from the preceding page, onto the active page/sheet Into G:W row 5.
It would take a great deal of time and I do not think it is the proper way of doing it.

Any Ideas?

Thank you again for your help.

GordNYC

 

[Aladin Akyurek]

On sheet One, my template, which I need to copy many times, contains columns G:W.
In row 5 of these columns are the totals that are brought forward from the previous page (or sheet, I do not know which would be easier). The totals on the preceding page are in columns G:W, row 27.

I could just copy and past the totals from G:W row 27 from the preceding page, onto the active page/sheet Into G:W row 5.
It would take a great deal of time and I do not think it is the proper way of doing it.

Any Ideas?

In G5 of the "active page", that is, in the destination sheet enter:

=INDEX(Source!$G$27:$W$27,COLUMN()-6)

and copy across to W5 in the destination sheet. Substitute the actual name of the sheet for 'Source'.

Aladin
This message was edited by Aladin Akyurek on 2002-03-31 09:33