Search results

Hi SirJB7 I have already uploaded a sample file - http://speedy.sh/F3Ffe/Named-Ranges-Sample-Extract.xlsx - the solution suggested by Narayan works well. The only issue is that due to the high number of entries (~8000) across more than 10 worksheets is leading to a long, complicated formula...

Bump! Any suggestions? File size is shooting up to 25MB from 250kb (xlsx) and becoming too unwieldy to work with.

Hi Narayan/ Jeffrey Follow up to this query - how many sheets can be consolidated using the formula given by Narayan? Need to consolidate 12 sheets now and the formula is becoming too long and the file size is also shooting up. Any suggestions? VBA solution is also fine. Thanks

Jeffrey, its a shared workbook using Google. Everyone edits their respective location data (i.e. on their sheet) at the end of the week.

Jeffrey, Thanks so much for the input - the worksheets are all in the same workbook and hence the link shared is appropriate. Of course that is entirely macro based which I dont know much about and hence am still trying to understand it. Would you be able to suggest a VBA solution to my...

Narayan, Sure - let me know when possible regarding the first question. The macro solution sounds good - the first option seems quite efficient. Even if the new data is inserted in any order, I can always sort it (or even that can be automated). Regarding pivot tables - one of the reasons...

Ok. Got it. I was copying it only for exact requirement as the total/ subtotal rows come in at the bottom. Also, the entry was being done bottom-top i.e. newest data on top. Anyhow, that can be managed. Named ranges are dynamic and include fresh data, so no issues there as well. For my...

Narayan, Got it - so if it has to be truly automatic, then macros will be required. As of now, the insertion of rows will be required regularly i.e. for every new row added in either M,B or C, a row needs to be added in the Master sheet. Whether the addition of rows is at the bottom or...

Narayan You are seriously GOOOOD!! Here I have been struggling with this since a couple of days making absolutely no headway and you have a solution in a half hour. Thanks man! Wow - though I am sure it will take me sometime before I fully understand the straightforward way. Anyhow, it...

Hi, As required, the sample workbook is attached below. http://speedy.sh/F3Ffe/Named-Ranges-Sample-Extract.xlsx

Merge/Stack Multiple Named Ranges (Across Multiple Worksheets) in a Master Sheet I would appreciate help in a problem which I am facing - despite all efforts of searching multiple blogs/ forums (including my favorite, chandoo.org), I am unable to find a non-VBA solution. Briefly, I work for...

Narayan, Thanks for all the suggestions till now. Appreciate it. Till you get any fresh ideas, I will assume this thread is resolved. Vinay

Dear Narayan, Seems to be that XIRR is pretty buggy and is prone to many errors, most notably, 2.98 error when empty cells, zero-valued date cells are entered, be they at the beginning, middle or end of the value and date ranges...

Dear Narayan, Suppose if I want to filter the worksheet data for investments made since calendar year 2005 to date (Column AI to be filtered by selecting 2005, 2007, 2008 and 2009 years - Total Outflow will be Rs. 91,575 and Current Value/ Inflows Total will be Rs 117,380), then the XIRR...

Narayan, I have tried to put your explanation to good use in finding filtered XIRR from the data, but am still in a mess. I have uploaded the sample file with my 3 best attempts at using CHOOSE/ OFFSET and Array Functions to solve the problem. File uploaded to...

Dear Narayan, Awaiting followup to your previous message.. Haseeb, Narayan, anyone, Any ideas on how to compute XIRR from filtered data as mentioned above?

Haseeb - that is seriously brilliant! Simple and elegant.. Thanks! Narayan - I would still love to understand the formula you suggested if you can spare the time for it. Any ideas for getting XIRR for filtered data though?

Sure. Appreciate all the time you are spending on this.

Narayan, I think you have misunderstood me. You are absolutely correct that if the dates and values differ in number, XIRR will not work. There are multiple portfolios for which I need the XIRR calculated. One of the portfolios has 1126 rows and the other has 3214. The number of entries of...

Hi Narayan, I had named the ranges but was not using either of TRANSPOSE/ MOD/ ROW/ INT. In fact with simple OFFSET I was getting the same #NUM! error. Can you take me through why we are using the above functions? I would really appreciate a step by step explanation so I understand properly...

Narayan, Thanks a ton! I tried the same approach by naming ranges, somehow it didnt work for me. I will compare both files and see where I was going wrong. But this definitely seems like it works well - I only have to try and implement this over 3000 rows and see if that complicates matters...

Hi, I have already gone through multiple websites including the ones sent by you and short of UDF using VBA (which I dont understand at all) there doesnt seem to be any solution. IRR accepts non contiguous ranges, but not XIRR. I am not sure if Excel 2010 or 2013 supports this function and...

Hi, I have been an avid follower of this site and the tips have helped to a great extent (short of the cape and spandex pants!) I would appreciate if you someone could help me out with a couple of issues I am facing. For a portfolio management sheet, the data available is Col 1 - Outflow...