| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF, Then
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!B610,CONCATENATE(Sheet1!A61," (",FIXED(Sheet1!B61,2)," - ",FIXED(Sheet1!D61,2),")"),IF(Sheet1!B620,CONCATE NATE(Sheet1!A62," (",FIXED(Sheet1!B62,2)," - ",FIXED(Sheet1!D62,2),")"),IF(Sheet1!B630,CONCATE NATE(Sheet1!A63," (",FIXED(Sheet1!B63,2)," - ",FIXED(Sheet1!D63,2),")"),IF(Sheet1!B640,CONCATE NATE(Sheet1!A64," (",FIXED(Sheet1!B64,2)," - ",FIXED(Sheet1!D64,2),")"),IF(Sheet1!B650,CONCATE NATE(Sheet1!A65," (",FIXED(Sheet1!B65,2)," - ",FIXED(Sheet1!D65,2),")"),IF(Sheet1!B660,CONCATE NATE(Sheet1!A66," (",FIXED(Sheet1!B66,2)," - ",FIXED(Sheet1!D66,2),")"), IF(Sheet1!B670,CONCATENATE(Sheet1!A67," (",FIXED(Sheet1!B67,2)," - ",FIXED(Sheet1!D67,2),")"), " "))))))) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, Then
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? "Uyen-Uyen" wrote: 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!B610,CONCATENATE(Sheet1!A61," (",FIXED(Sheet1!B61,2)," - ",FIXED(Sheet1!D61,2),")"),IF(Sheet1!B620,CONCATE NATE(Sheet1!A62," (",FIXED(Sheet1!B62,2)," - ",FIXED(Sheet1!D62,2),")"),IF(Sheet1!B630,CONCATE NATE(Sheet1!A63," (",FIXED(Sheet1!B63,2)," - ",FIXED(Sheet1!D63,2),")"),IF(Sheet1!B640,CONCATE NATE(Sheet1!A64," (",FIXED(Sheet1!B64,2)," - ",FIXED(Sheet1!D64,2),")"),IF(Sheet1!B650,CONCATE NATE(Sheet1!A65," (",FIXED(Sheet1!B65,2)," - ",FIXED(Sheet1!D65,2),")"),IF(Sheet1!B660,CONCATE NATE(Sheet1!A66," (",FIXED(Sheet1!B66,2)," - ",FIXED(Sheet1!D66,2),")"), IF(Sheet1!B670,CONCATENATE(Sheet1!A67," (",FIXED(Sheet1!B67,2)," - ",FIXED(Sheet1!D67,2),")"), " "))))))) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, Then
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 "Don Guillett" wrote: Here is the basic idea. Look in the help index for INDIRECT for more info. Play with it. =INDIRECT("a"&sheet1!a61+1) -- Don Guillett Microsoft MVP Excel SalesAid Software "Uyen-Uyen" wrote in message ... 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!B610,CONCATENATE(Sheet1!A61," (",FIXED(Sheet1!B61,2)," - ",FIXED(Sheet1!D61,2),")"),IF(Sheet1!B620,CONCATE NATE(Sheet1!A62," (",FIXED(Sheet1!B62,2)," - ",FIXED(Sheet1!D62,2),")"),IF(Sheet1!B630,CONCATE NATE(Sheet1!A63," (",FIXED(Sheet1!B63,2)," - ",FIXED(Sheet1!D63,2),")"),IF(Sheet1!B640,CONCATE NATE(Sheet1!A64," (",FIXED(Sheet1!B64,2)," - ",FIXED(Sheet1!D64,2),")"),IF(Sheet1!B650,CONCATE NATE(Sheet1!A65," (",FIXED(Sheet1!B65,2)," - ",FIXED(Sheet1!D65,2),")"),IF(Sheet1!B660,CONCATE NATE(Sheet1!A66," (",FIXED(Sheet1!B66,2)," - ",FIXED(Sheet1!D66,2),")"), IF(Sheet1!B670,CONCATENATE(Sheet1!A67," (",FIXED(Sheet1!B67,2)," - ",FIXED(Sheet1!D67,2),")"), " "))))))) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, Then
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(TRU E,Sheet1!B61:B670,0)),,-1),2)&" - "&FIXED(OFFSET(INDIRECT("'Sheet1'!B"&60+MATCH(TRUE ,Sheet1!B61:B670,0)),,2),2)&")") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, Then
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,S heet1!B61:B670,0)),,-1),2) &" - "& FIXED(OFFSET(INDIRECT("'Sheet1'!B"&60+MATCH(TRUE,S heet1!B61:B670,0)),,2),2) &")") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF, Then
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Max" wrote in message ... 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,S heet1!B61:B670,0)),,-1),2) &" - "& FIXED(OFFSET(INDIRECT("'Sheet1'!B"&60+MATCH(TRUE,S heet1!B61:B670,0)),,2),2) &")") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
