IF, Then

  • Thread starter Thread starter Uyen-Uyen
  • Start date Start date
U

Uyen-Uyen

I am getting an error when I do this. Do you know what the problem is? Or
if there is a simpler way to do this?

=IF(Sheet1!B61>0,CONCATENATE(Sheet1!A61," (",FIXED(Sheet1!B61,2)," -
",FIXED(Sheet1!D61,2),")"),IF(Sheet1!B62>0,CONCATENATE(Sheet1!A62,"
(",FIXED(Sheet1!B62,2)," -
",FIXED(Sheet1!D62,2),")"),IF(Sheet1!B63>0,CONCATENATE(Sheet1!A63,"
(",FIXED(Sheet1!B63,2)," -
",FIXED(Sheet1!D63,2),")"),IF(Sheet1!B64>0,CONCATENATE(Sheet1!A64,"
(",FIXED(Sheet1!B64,2)," -
",FIXED(Sheet1!D64,2),")"),IF(Sheet1!B65>0,CONCATENATE(Sheet1!A65,"
(",FIXED(Sheet1!B65,2)," -
",FIXED(Sheet1!D65,2),")"),IF(Sheet1!B66>0,CONCATENATE(Sheet1!A66,"
(",FIXED(Sheet1!B66,2)," - ",FIXED(Sheet1!D66,2),")"),
IF(Sheet1!B67>0,CONCATENATE(Sheet1!A67," (",FIXED(Sheet1!B67,2)," -
",FIXED(Sheet1!D67,2),")"), " ")))))))
 
Actually, I need more help than that.

I am going to copy that same formula into 7 cells.

So, if the cell above already says something, how can I get the cell not to
repeat that and move on?
 
Here is the basic idea. Look in the help index for INDIRECT for more info.
Play with it.

=INDIRECT("a"&sheet1!a61+1)
 
Thanks for your reply. But, I don't think that is what I am trying to do.
Let me explain better what I am trying to do.

I have a list of seven things that each have a different range. Depending
on what I am doing, sometimes not all seven things will have a range. Maybe
only 2 out of the 7.

So, on a separate page, I want to display the results. But, if there is no
range for that thing, then I don't want it to show up.

Does that make more sense?

Uyen-Uyen
 
Think you busted the maximum number of nested levels in your original formula

One alternative, try the formula below, array-enter it by pressing
CTRL+SHIFT+ENTER (instead of just pressing ENTER):
=IF(SUM(Sheet1!B61:B67)<=0,"","
("&FIXED(OFFSET(INDIRECT("'Sheet1'!B"&60+MATCH(TRUE,Sheet1!B61:B67>0,0)),,-1),2)&"
-
"&FIXED(OFFSET(INDIRECT("'Sheet1'!B"&60+MATCH(TRUE,Sheet1!B61:B67>0,0)),,2),2)&")")


---
 
There'll be a number of inadvertent line breaks when you copy the formula
direct from posting to paste into your formula cell. Just remove all the line
breaks carefully before you array-enter to confirm the formula.

Perhaps easier for the "direct copy-n-paste, array-enter",
here's a re-rendered version of the same formula:

=IF(SUM(Sheet1!B61:B67)<=0,""," ("&
FIXED(OFFSET(INDIRECT("'Sheet1'!B"&60+MATCH(TRUE,Sheet1!B61:B67>0,0)),,-1),2)
&" - "&
FIXED(OFFSET(INDIRECT("'Sheet1'!B"&60+MATCH(TRUE,Sheet1!B61:B67>0,0)),,2),2)
&")")


---
 
Sub FixLongFormulas() 'goto a remote area of ws & select 1st line
x = ActiveCell.Row
y = ActiveCell.Column
z = ActiveCell.End(xlDown).Row
For Each C In Range(Cells(x, y), Cells(z, y))
mstr = mstr & C
Next
Cells(x - 1, y) = mstr
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Have "IF,Then" to get data from multiple columns--can I nest add'l IF,THENs? 1
Compile Error sending pdf as attachment 1
Combine 2 Codes into 1 Code to do the lot? 2
Nested IF statements - Help 4
A macro to check range and then check another range if falset-then 2
If-then statement that checks for existence of series 2 3
if-then statement that checks for existence of series 2 2
VBA reset cell value if greater then 0 8

Back
Top