Multiply If statements and Deleting digits

[Mikest]

This is a fantastic forum and thanks in advance for the answer to my questions.

1. I want to get a result a summary w/sheet to this scenario.

If E7=3,Project!A19:Q236,13 or IF e7=5,Project!A19:Q236,15 or E7=7,Project!A19:Q236,15
How do I get this to work.

2. I wish to delete the first or second digit plus the minus from this data (10-14 or 1-14) to give the result of 14

Once again thanks
Mike

[Chris Davison]

On 2002-03-06 22:05, Mikest wrote:
This is a fantastic forum and thanks in advance for the answer to my questions.

1. I want to get a result a summary w/sheet to this scenario.

If E7=3,Project!A19:Q236,13 or IF e7=5,Project!A19:Q236,15 or E7=7,Project!A19:Q236,15
How do I get this to work.

2. I wish to delete the first or second digit plus the minus from this data (10-14 or 1-14) to give the result of 14

Once again thanks
Mike

should that first 13 be 15 ?

if so, try :

=IF(OR(E7={3;5;7}),sum(Project!A19:Q236),15)

(having a go at your second question)

Hope this helps
Chris


2nd question :

assuming A1 holds your data, try this :

=REPLACE(A1,1,FIND("-",A1,1),"")+0

(any find and replace experts, please don't laugh !!)



[ This Message was edited by: Chris Davison on 2002-03-06 22:54 ]

[ This Message was edited by: Chris Davison on 2002-03-07 11:17 ]

[Mikest]

Thanks Chris,

Question 2 answered perfectly.

Question 1.
Made a mistake on the last section should have read. 17 not 15.
Summary: if E7=3 then display data in col13
if E7=5 then display data in col15 and if E7=7 the display data in col17.

Thanks Again.
Mike

[Mark W.]

Sounds like you want...

=INDEX(Project!A19:Q235,,E7+10)

...but, you said "summary" so the question is, "What kind of summary?" Perhaps, you want the sum...

=SUM(INDEX(Project!A19:Q235,,E7+10))

[Mikest]

I have a set of room numbers and measurements on a worksheet page called projects.

The idea is if a room has a cupboard 3 doors in it have a cell that tells me the size of the third door. (1 pair and 1 odd door)
Or if 5 doors the size of the fifth and so on.

All the third doors are in the 13th col, fifth doors are in 15 col and 7th doors are in the 17th col of the project w/sheet.

Thank again
Mike

[Mark W.]

Well, if you want to list the 217 values associated with a cupboard type then select a vertical range of 217 cells and enter the array formula...

{=INDEX(Project!A19:Q235,,E7+10)}

Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.


[ This Message was edited by: Mark W. on 2002-03-07 15:44 ]

[Mikest]

Here's a quick sample from my Projects w/sheet
rm dr1 dr2 dr3
1 2 3 4 5 6 7 8 13
801B2,1,1,1400,3,1,Left,936,463,463,437

Then a have w/sheet called doors, where I enter the room No (801B2) and it displays all
the data I need. Cell E7 gives me how many doors.

Have a try at that,
Thanks
Mike

[Mark W.]

On 2002-03-07 16:01, Mikest wrote:
Here's a quick sample from my Projects w/sheet
rm dr1 dr2 dr3
1 2 3 4 5 6 7 8 13
801B2,1,1,1400,3,1,Left,936,463,463,437

Then a have w/sheet called doors, where I enter the room No (801B2) and it displays all
the data I need. Cell E7 gives me how many doors.

Have a try at that,
Thanks
Mike

Well, let's see...

=INDEX(Project!A19:Q235,MATCH("801B2",A19:A235,0),E7+10)

...should give you the measurements for the doors in room 801B2.

[ This Message was edited by: Mark W. on 2002-03-07 16:10 ]

[Mikest]

Thanks again all,

I have been working on the Foluma's suggested and get either a N/A or VAULE result.

Any Thoughts
Regards

Mike