|
change a cell formula
To All,
I need a cell on a sheet named "YEAR" to alternate between 2 possible array formulas (stored as TEXT on a sheet named "WorkSheet"). Could someone advise how to setup these 2 formulas properly as strings AND then how to copy the 2 formula's to cell A1 on the sheet "YEAR" ??? =IF(B$4="","",INDEX(WorkSheet!Sked_Cycle_Range,HLO OKUP(LEFT($A7,1),WorkSheet!Which_Row,2,FALSE),Work Sheet!row_range,2,FALSE),(MOD(B$4-WorkSheet!Seed_Date,WorkSheet!SEQ)+1))) and the 2nd: =IF(B$4="","",INDEX(WorkSheet!Sked_Cycle_Range,HLO OKUP(HLOOKUP(LEFT($A7,1), WorkSheet!Which_Row,2,FALSE)+MOD(4*(TRUNC((B$4-WorkSheet!Seed_Date)/WorkSheet!SEQ)),7),WorkSheet!row_range,2,FALSE),(M OD(B$4-WorkSheet!Seed_Date,WorkSheet!SEQ)+1))) Any help wold be appreciated ... Jim Pellechi |
change a cell formula
sf1 =
"=IF(B$4="""","""",INDEX(WorkSheet!Sked_Cycle_Rang e,HLOOKUP(LEFT($A7,1),Work Sheet!Which_Row,2,FALSE),WorkSheet!row_range,2,FAL SE),(MOD(B$4-WorkSheet!See d_Date,WorkSheet!SEQ)+1)))" sf2 = "=IF(B$4="""","""",INDEX(WorkSheet!Sked_Cycle_Rang e,HLOOKUP(HLOOKUP(LEFT($A7 ,1), WorkSheet!Which_Row,2,FALSE)+MOD(4*(TRUNC((B$4-WorkSheet!Seed_Date)/WorkShee t!SEQ)),7),WorkSheet!row_range,2,FALSE),(MOD(B$4-WorkSheet!Seed_Date,WorkShe et!SEQ)+1)))" or With woksheets("worksheet") sf1 = Trim(Range("A1").Value) sf2 = Trim(Range("A2").Value) with worksheets("Year") if flag = 1 then .Range("B9").FormulaArray = sf1 else .Range('B9").FormulaArray = sf2 End If End With -- Regards, Tom Ogilvy JimP wrote in message om... To All, I need a cell on a sheet named "YEAR" to alternate between 2 possible array formulas (stored as TEXT on a sheet named "WorkSheet"). Could someone advise how to setup these 2 formulas properly as strings AND then how to copy the 2 formula's to cell A1 on the sheet "YEAR" ??? =IF(B$4="","",INDEX(WorkSheet!Sked_Cycle_Range,HLO OKUP(LEFT($A7,1),WorkSheet !Which_Row,2,FALSE),WorkSheet!row_range,2,FALSE),( MOD(B$4-WorkSheet!Seed_Dat e,WorkSheet!SEQ)+1))) and the 2nd: =IF(B$4="","",INDEX(WorkSheet!Sked_Cycle_Range,HLO OKUP(HLOOKUP(LEFT($A7,1), WorkSheet!Which_Row,2,FALSE)+MOD(4*(TRUNC((B$4-WorkSheet!Seed_Date)/WorkShee t!SEQ)),7),WorkSheet!row_range,2,FALSE),(MOD(B$4-WorkSheet!Seed_Date,WorkShe et!SEQ)+1))) Any help wold be appreciated ... Jim Pellechi |
change a cell formula
WORKS like a charm Tom, Thanks again ...
Jim Pellechi *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
change a cell formula
Tom (or any one else that understands this problem),
HELP ... a problem has crept in and now I'm doubting I understand the basics. As I improve my spreadsheet I rename it - a simple form of Configuration Management. The problem is that my formulas stopped working ... after debugging I can see that for some unknown reason the name of the FILE that the formula existed in PRIOR to my renaming the file, has become part of the worksheet name I'm using within the formula????? Since I tend to delete former versions of the spreadsheet - as soon as the file is deleted, the next attempt at running the latest version of the spreadsheet fails ... I'm not even sure I'm explaining this properly. Since I haven't a clue how this happened, I realize I don't understand the significance and the 'hidden' relationship of a worksheets FILENAME, WORKSHEET NAME, RANGE_NAME ... etc. Could you instruct me on how to isolate the variable names I'm using within a formula to ONLY the worksheet level within WHATEVER workbook the formula resides in ... Jim Pellechi ---------------------------------------------------------- Message 2 in thread From: Tom Ogilvy ) Subject: change a cell formula View this article only Newsgroups: microsoft.public.excel.programming Date: 2003-11-04 19:11:34 PST sf1 = "=IF(B$4="""","""",INDEX(WorkSheet!Sked_Cycle_Rang e,HLOOKUP(LEFT($A7,1),Work Sheet!Which_Row,2,FALSE),WorkSheet!row_range,2,FAL SE),(MOD(B$4-WorkSheet!See d_Date,WorkSheet!SEQ)+1)))" sf2 = "=IF(B$4="""","""",INDEX(WorkSheet!Sked_Cycle_Rang e,HLOOKUP(HLOOKUP(LEFT($A7 ,1), WorkSheet!Which_Row,2,FALSE)+MOD(4*(TRUNC((B$4-WorkSheet!Seed_Date)/WorkShee t!SEQ)),7),WorkSheet!row_range,2,FALSE),(MOD(B$4-WorkSheet!Seed_Date,WorkShe et!SEQ)+1)))" or With woksheets("worksheet") sf1 = Trim(Range("A1").Value) sf2 = Trim(Range("A2").Value) with worksheets("Year") if flag = 1 then .Range("B9").FormulaArray = sf1 else .Range('B9").FormulaArray = sf2 End If End With -- Regards, Tom Ogilvy James Pellechi wrote in message ... WORKS like a charm Tom, Thanks again ... Jim Pellechi *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
| All times are GMT +1. The time now is 05:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com