define name for long formula
i have a long formula for which i tried to define a name for it. but the formula is too long to define a name.
what is the way to define a name for long formula?
Hello,
can you post the formula?
Do you want to apply a named range/formula to that formula?
What is the error message you get?
Please provide more detail.
find below formula.
=IF(AND(ROWS($A$2:$A2)<=MAX('CASH BOOK (10-11)'!$AC:$AC),COLUMN(A$3)<8),IF(INDEX(IF(INDEX('CASH BOOK (10-11)'!$F:$F,MATCH(ROWS($A$2:$A2),'CASH BOOK (10-11)'!$AC:$AC))='CASH BOOK (10-11)'!$AB$2,'CASH BOOK (10-11)'!B:B,'CASH BOOK (10-11)'!O:O),MATCH(ROWS($A$2:$A2),'CASH BOOK (10-11)'!$AC:$AC,0))=0,IF(AND(COLUMN(A$3)=6,INDEX('CASH BOOK (10-11)'!$F:$F,MATCH(ROWS($A$2:$A2),'CASH BOOK (10-11)'!$AC:$AC))='CASH BOOK (10-11)'!$AB$2),OFFSET(IF(INDEX('CASH BOOK (10-11)'!$G:$G,MATCH(ROWS($A$2:$A2),'CASH BOOK (10-11)'!$AC:$AC))=0,IF(INDEX('CASH BOOK (10-11)'!$I:$I,MATCH(ROWS($A$2:$A2),'CASH BOOK (10-11)'!$AC:$AC))>0,'CASH BOOK (10-11)'!$I$1,IF(INDEX('CASH BOOK (10-11)'!$J:$J,MATCH(ROWS($A$2:$A2),'CASH BOOK (10-11)'!$AC:$AC))>0,'CASH BOOK (10-11)'!$J$1,IF(INDEX('CASH BOOK (10-11)'!$K:$K,MATCH(ROWS($A$2:$A2),'CASH BOOK (10-11)'!$AC:$AC))>0,'CASH BOOK (10-11)'!$K$1,'CASH BOOK (10-11)'!$L$1)))),MATCH(ROWS($A$2:$A2),'CASH BOOK (10-11)'!$AC:$AC,0)-1,0,1,1),IF(AND(COLUMN(A$3)=7,INDEX('CASH BOOK (10-11)'!$S:$S,MATCH(ROWS($A$2:$A2),'CASH BOOK (10-11)'!$AC:$AC))='CASH BOOK (10-11)'!$AB$2),OFFSET(IF(INDEX('CASH BOOK (10-11)'!$U:$U,MATCH(ROWS($A$2:$A2),'CASH BOOK (10-11)'!$AC:$AC))=0,IF(INDEX('CASH BOOK (10-11)'!$V:$V,MATCH(ROWS($A$2:$A2),'CASH BOOK (10-11)'!$AC:$AC))>0,'CASH BOOK (10-11)'!$V$1,IF(INDEX('CASH BOOK (10-11)'!$W:$W,MATCH(ROWS($A$2:$A2),'CASH BOOK (10-11)'!$AC:$AC))>0,'CASH BOOK (10-11)'!$W$1,IF(INDEX('CASH BOOK (10-11)'!$X:$X,MATCH(ROWS($A$2:$A2),'CASH BOOK (10-11)'!$AC:$AC))>0,'CASH BOOK (10-11)'!$X$1,'CASH BOOK (10-11)'!$Y$1)))),MATCH(ROWS($A$2:$A2),'CASH BOOK (10-11)'!$AC:$AC,0)-1,0,1,1),"")),INDEX(IF(INDEX('CASH BOOK (10-11)'!$F:$F,MATCH(ROWS($A$2:$A2),'CASH BOOK (10-11)'!$AC:$AC))='CASH BOOK (10-11)'!$AB$2,'CASH BOOK (10-11)'!B:B,'CASH BOOK (10-11)'!O:O),MATCH(ROWS($A$2:$A2),'CASH BOOK (10-11)'!$AC:$AC,0))),IF(ROWS($A$2:$A2)=MAX('CASH BOOK (10-11)'!$AC:$AC)+1,IF(OR(COLUMN(A2)=6,COLUMN(A2)=7),SUM(A$1:A2),""),IF(ROWS($A$2:$A2)=MAX('CASH BOOK (10-11)'!$AC:$AC)+2,IF(AND(COLUMN(A2)=6,CELL("CONTENTS",$F2)>CELL("CONTENTS",$G2)),$F2-$G2,IF(AND(COLUMN(A2)=7,CELL("CONTENTS",$F2)<CELL("CONTENTS",$G2)),$G2-$F2,"")),"")))
i want to name this formula as "LEDGER".
i want to use this same formula in each of the work sheet in the whole workbook consisting about 30 worksheets. There are some absolute references and relative references to the cells in the formula and they are to be as it is.
Last edited by unas; 12-09-2010 at 11:36 PM. Reason: modified as per the comments of martindwilson.
That formula is fairly incomprehensible and way too long for a range name. What are you trying to do?
Last edited by teylyn; 12-08-2010 at 04:56 AM.
i agree ,that is a ludicrous formula there must be a better way there are some bits like
=CELL("COL",A$3)>8 which i cant see the need for =column(A$3)>8 would do
Last edited by martindwilson; 12-08-2010 at 07:04 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
The work book consists of two worksheets.
• Worksheet no.1 is named as CASH BOOK (10-11)
• Worksheet no.1 is named as ANY LF
1. CASH BOOK (10-11)
a. Column A to Column N is on credit side of cash book.
b. Column O to Y is on debit side of cash book
c. Column B and O are for entering dates.
d. Column C and P are for entering description of transaction.
e. Column F and S are for account heads
f. The remaining columns are for entering various other details.
g. Column AB1 is where we choose the account head for which we require the detailed account.
h. The rows in column AC count the no. of times the chosen account head, (eg. KKR) is in either column F or S.
2. ANY LF
a. All the cells from B3 to G8 consist of the same formula which I have given earlier.
b. The formula first compares the serial no. of row in A with maximum no. in column AC in cash book (10-11) and if it is less performs the following.
i. Count row no. of account head. (eg. KKR) in the cash book (10-11).
ii. Indexes the cash book starting from the first entry in the column and counts the no. of rows counted above (1) and depending upon the position of account head in column F or S displays the value in cash book (10-11) in credit or debit column of ANY LF.
iii. If there is no value in cash book (10-11) column G or T, it searches for value greater 0 in J / I / N or U/V/W/X depending upon the position of account head in F or S and displays it in either credit or debit column in ANY LF.
iv. If any cell is blank in Cash book (10-11), it also displays blank in the corresponding cell in ANY LF.
v. After completing all the entries, (no.6 in this case) the formula sums the column in the credit and debit sides in ANY LF.
vi. After that the difference in debit is shown, in ANY LF, on credit or debit side depending upon whichever is greater.
3. Briefly the formula does the above functions.
4. The formula is very long and if the same is put in each cell of ANY LF a lot of memory space will be occupaid. Hence I want to name the forumla and keep the name instead of formula in each cell in ANY LF.
5. I have taken care of obsolute and relative addresses in the formula so that it gives correct value no matter in which cell it is.
6. I hope I am able to explain clearly.
7. I am attaching with this a sample sheet of cash book (10-11) and ANY LF.
Now I request you to help me in this regard.
ok that says what it does, but what do you want and where?
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
4. The formula is very long and if the same is put in each cell of ANY LF a lot of memory space will be occupaid. Hence I want to name the forumla and keep the name instead of formula in each cell in ANY LF.
I think now iam clear.
Kindly help me in this regard.
thanking you in advance.
regards
unas.
no i am asking what is the end result i cant follow your explanation its as involved as your formula,
what is the desired output on the sheet that formula is, there must by a much easier what to achieve what you want
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
my workbook consists of two worksheets. worksheet no. is CASH BOOK (10-11) in which i enter all inputs. worksheet no.2 is where i get my output. at present there is no problem in giving input or gettig the output as per my requirements. Since the memory space occupied by my workbook is very high i wish to either simplify the formula or the formula is given a name so that i can use that name in every cell instead of formula.
So i request you to tell me the method by which i can define a name for my formula. All my attempts to do have failed. Hence this request.
regards
unas.
I agree with Martin that both the formula and the explanation are a bit hard to digest. I also have difficulties with "I request ..."
Nevertheless, if your one BIG FORMULA is too much for any one named range, consider breaking it up into several named ranges.
A simplified example:
=Sum(Vlookup(<formula1>),Vlookup(<formula2>),Vlookup(<formula3>))/Match(<formula4)*Max(<formula5>)
Use several range names to define the elements of the formula
form1 =Vlookup(<formula1>)
form2 =Vlookup(<formula2>)
form3 =Vlookup(<formula3>)
SumForm1to3 = Sum(form1, form2, form3)
form4 =Match(<formula4>)
form5 =Match(<formula5>)
Your final named range can then look like
FinalForm =SumForm1to3/Form4*Form5
So, you don't need to put each native function into the final named formula, if you create meaningful sub-chunks of calculations, which you can combine into the total formula without exceeding the character limit for named ranges.
cheers
Last edited by teylyn; 12-13-2010 at 03:04 AM.
Kindly inform the character limit for named range to upgrade my knowledge.
regards,
unas.
Can you inform me about the following comment which is below your signature. If it is simpler than my spreadsheet i would like to adopt the same.
"Good spreadsheet design: Data on one sheet in a contiguous table. Reporting on data on other sheets."
regards,
unas.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks