• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Error in IFERROR() Formula

sgmpatnaik

sgmpatnaik

Active Member
Hello Sir,


Kindly solve the Problem while using the Formula


=IFERROR(INDEX(DISP_DATE,SMALL((DISP_DEAL_NAME="XYZ")*ROW(DISP_DEAL_NAME),COUNTA(DISP_DEAL_NAME)-COUNTIF(DISP_DEAL_NAME,"XYZ")+ROW(A1))-ROW(DISP_DEAL_NAME)+1),"")


The Mistake is displaying in the Sheet of Shiva Shakti Enterprises


for your better knowledge i upload my file


https://dl.dropbox.com/u/75654703/Toshali%20Cements%20Accounts.xlsm


Kindly suggest me why this problem is happen and if possible please suggest me for how to speed up my workbook


hence i Oblige


Thanking you


With Regards


SP
 
Hi, sgmpatnaik!


This seems to be the same question as of your last topic, 4 days ago:

http://chandoo.org/forums/topic/date-combination


I'm sure you've yet read the three green sticky posts at this forums main page, particulary the third one in the paragraphs about cross posting. I think you should remember what Hui wrote here:

http://chandoo.org/forums/topic/how-to-add-the-scroll-msg-in-excel-sheet#post-24171


First time, it could have been unintentionally, everyone makes mistakes. But second time in two months... it's a little suspicious, don't you think so? I actually do.


I'd humbly but strongly recommend you to avoid incurring in such reprehensible practices that are now well seen in communities like this.


Regards!
 
hello SirJB7


I am Extremely sorry for this, but my aim is not like that and i am feel very great as i got a great teacher from this site and i thank full to you all. and i have no words to tell you and i am i am extremely sorry (Actually i think my last post is closed that's why i post this question again)


Thanking You Sir


Witharegards


Sp


Sorry again for this mistake
 
SP


I don't see any errors on the Shiva Shakti Enterprises worksheet?


We are back to where we were two weeks ago ?


You still haven't told us what the problem is?

You haven't told us where the problem is?

You haven't told us what you want or how it should work ?


If you want us to help you, please pay attention to what we ask of you and in particular the instructions in: http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting


We are now getting too many posts to waste time on posts that don't supply sufficient information to assist you. If you want us to help you, you must help us.


ps: I don't think any of your posts are Closed, But I may be wrong?


pps: As a totally wild guess you have no data in the Despatch Register Sheet, Row 74 but you have a value in cell E74 which is why you have a row of 0's on the Shiva Shakti Enterprises worksheet
 
Gooday I am running Intels duo core with 4gig of ram no other programs open,opening this file is to say the least painful, download saved to hard disk. I they open/run while it is loading I get the mower out and cut the grass weed the flower beds and then get a nice long shower return to the lap top refreshed to see to wait circle still going around. When it does final load we can access the spread sheet.....but click in any cell or on any tab and it totally freezes Excel...there is some thing very wrong with this workbook, can not have a look as can not get in!!!!
 
I've got a Quad core i970 with 16GB ram and it takes over 1 min to recalc

Yesterday's file that looked the same was ok
 
Good day Hui when I have managed to get into the spread sheet (before it locks up) there is not a great amount of data (visible) and yet the file size is just under 12MB??. I have multi tab spread sheets for accidents at work,formulas, drop down dependent validation, conditional formatting tabs from 2007 to 2012, pivot tables and pivot charts for each year and the file size is no where near this
 
Bob

It's an XLSB file and so is compressed

I think it's is 75 or 85 MB as a XLSM file

There are also Lots of Hidden worksheets


The size shouldn't slow it down that much

It is the structure and overuse of Volatile functions that are the cause of the slowness
 
Hui thanks for the clarity, sgmpatnaik says in his first post that it is slow in loading, he needs to explain to the nijas just what the problem is and when it manifests it self
 
Hello Sir,


Good Morning


Thank Q sir for your replay


Yes Sir as per your Question there is no data in dispatch Register Sheet Row 74 and i enter the party code in ColumnD of Row 74 then the Dealer name appear the rest heading of the row is blank. When we go to Shiva Shakti Hardware Sheet i got the error in Date field in ColumnB as 00-01-1900 and the rest rows with 0 when i use the end of the formula with this strings (""). If the mistake is not showing in your system then some thing wrong with my system and i have to check that


Thanking You


With regards


SP
 
SP


If I manually Populate Despatch Register Row 74 the values transfer through to Row 3 of the Shiva Shakti Enterprises sheet


If I delete value 25 from Despatch Register D74 then the Row 3 of the Shiva Shakti Enterprises sheet is blank as it should be


Isn't this correct ?


It is taking several minutes to recalculate on my PC which is powerful and has lots of memory.


Are you waiting long enough ?
 
yes Sir, you are right


i am entering the data in Dispatch Register only and the data will transfer automatically to all party ledger with the base of ColumnD & ColumnE


if we left blank all the columns in Dispatch Register except ColumnD & ColumnE then the blank data will transfer to the parties Sheet as per our formula for ColumnE in the parties Sheet!


Have find out the mistake in Shiva Shakti Enterprises Sheet when we putting the data in ColumnB,ColumnC and etc. then the data will transfer to Shiva Shakti Enterprises Sheet if we remove the entered data in Dispatch Register then the mistake is displaying in ColumnB and all in shiva shakti Enterprises, (In ColumnB is Date field it's displyaing as 00-01-1900) and the rest columns 0


for your clarify please enter the no. 1 in ColumnD of Dispatch Register Row 74 and check the Sheet of Basant Febrication what is the different between Basant Febrication and Shiva Shakti Enterprises


Thanking you


With Regards


SP
 
00-01-1900 is just Excels way of saying 0 as the cell is formatted as a date and the formula can't find a value
 
If I put 1 in Cell D78 (Sl No 74) the values in Shiva Shakti Enterprises clear out of Row 7 (SL No 3) and the values now appear in Basant Febrication Row 5 as (SL No. 1)


I don't see any 00-01-1900 in any of the 2 sheets Shiva Shakti Enterprises or Basant Febrication date fields


So After 2 weeks I now suspect this spreadsheet is too big for your PC and it isn't updating properly !


The Workbook you used in the last post was doing the same as this but was very speedy, Can't you use that ?


Instead of a Separate Sheet for each fabricator why not just have 1 Report sheet and change the value in a Cell at the top to extract the report for the company you are interested in.

You collection of 34 Fabricators is slowing this workbook down massively!
 
Thank Q Hui Sir,


I think you are right as my PC is not updating properly, then which format i will save my work book


Thanks Once Again to help me about this problem on your valuable time


With Regards


SP
 
You must log in or register to reply here.
Back
Top