Conditional Output.

[Cadman675]

Hey all, first post here and new to excel...
I am in the process of making a spreadsheet to calculate an output script for autocad to draw staircases.

I just need a little help with the correct formula to use in relation to displaying data from 1-25 cells into a main output cell based on whether a corresponding box has the same data as another (constant)corresponding box.? If that makes sense.?

Here is the formula I am using for the output cell but I am not sure how to put it together to work.

=IF(H33=I33,J33," ") IF(H34=I34,J34," ") IF(H35=I35,J35," ")

I am sure i should have an 'and' or an 'or' for false but i'm at a bit of a loss in the logic department.

I have 25 of these ifs to string together.

Any help would be greatly appreciated.

[Cheeky Charlie]

what happens if h33 = i33 and h34 = i34?

[Cadman675]

H33 is already an if formula,
=IF(I33=E14,1," ")

E14 is my main input box.
So if h33 does = i33(constant) then the true output of the cell should be j33.

[Cadman675]

I think i may have done an extra calculation i do not need.

[Cheeky Charlie]

perhaps you could upload an example showing what you have and what you'd like to see?

[Cadman675]

Here is a basic example of what i want to create.

[Cheeky Charlie]

Try this:
="draw 1 tread" & LOOKUP(A1,{0,1,2,3,4},{""," then copy once"," then copy twice"," then copy three times"," then copy four times"})
Where you change A1 to point to the cell where you type in the number of treads.

This feels more efficient, but isn't really, and indeed is harder to edit:
="draw 1 tread" & LOOKUP(A1,{0,1},{""," then copy "}) & LOOKUP(A1,{0,1,2,3,4},{"","once","twice","three times","four times"})

You know it's possible to upload Excel spreadsheet files, it makes it a bit easier to give the most useful formula.

[Cadman675]

That looks like a good solution however i will need to change a lot of my code and my script at 25 treads is 1360 characters in length.

Here is the spreadsheet to make it a bit clearer of what i already have to work with.

btw h33:h57 is there so i can double check that my output is correct quickly...


Edit; You will need to go to the second sheet.

[Cadman675]

=IF(H33=I33,J33,IF(H34=I34,J34,IF(H35=I35,J35," ")))etc...

Thanks Cheeky but I found another similar thread.. This works well for me..

[Cheeky Charlie]

Not sure exaclty where I'm looking, but for this many options, you can use VLOOKUP(), or INDEX() and write your list of results out.

Something like:
U2=once
U3=twice
U4=three
...

V1= draw one tread
V2="draw one tread then copy "&U2
(copy down one row)
V4="draw one tread then copy "&U4&" times"
(copy down to twenty-five)

then
O6=INDEX(V:V,E14)

Your formula in H33:H57 - if you put in:
=IF(I33=E$14,I33,"")
you can copy it down the length of the column without having to rewrite it for every cell

I would (strongly) advise against merging cells.

hth

[Cheeky Charlie]

=IF(H33=I33,J33,IF(H34=I34,J34,IF(H35=I35,J35," ")))etc...

Thanks Cheeky but I found another similar thread.. This works well for me..

Is a horrible approach.

[Cadman675]

My whole spreadsheet is a horrible approach now that i realize what the $ does.

I have redrawn it now and it's a lot simpler.

I am up to the same point with working out the final output.