In the above attachment my question is that, i want only that month in which the employee has taken the leave salary without including blank cell when i drag the formula in sheet.
Thanks
In the above attachment my question is that, i want only that month in which the employee has taken the leave salary without including blank cell when i drag the formula in sheet.
Thanks
Can not see any attachment but try this.
month.xlsx
If you are happy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
Thanks.
but its not working. i will attached the sheet again.
regards
Noor
Please solve this issue.
Like this? I haven't been consistent with cell references here, one named range is dynamic, one is not and so on.
Nevertheless I think it turned out pretty well. Since you are using Excel 2003 I couldn't use the IFERROR so I just used Conditional Formatting to make error text white.
I put the spin button there just for testing.
<----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left
If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
Thank for the earlier reply. I tried to do the same but some error is coming. please see the attachment where is the problem. actually i have not the good knowledge of excel.
regards
Noorullah
I will look at your workbook now.
Does it have to be in Excel 2003? In Excel 2007 and 2010 it is possible to use the Table feature. It will automatically expand ranges to include new data.
Fixed it. You're doing good, you got it almost right. I had some trouble getting the AMOUNT formula to work. To make it easier to read I renamed your sheet (Datasheet) so it can not be confused with the range Data.
The second report sheet you can just delete if you want, again it was just for testing.
I see now by the way it is setup that the Table feature of Excel 2010 wouldn't help much. As long as you insert rows and columns within the data range then it will ofcourse update automatically regardless.
Thank you very much. Really its very helpful for me.thanks a lot again.
-------
Dear Sir,
I have the sales data. the problem with the formula is that it is calculating very late. First i used with sumproduct then i used sumif but the result it same. So i request you to please provide a best formula that can quickly calculate the data.
regards
Noorullah
Try to don't use whole column range in your Named ranges..Example for The Named range ="Units", you use this range..
=Data!$E$3:$E$65536
Do it smaller..like =Data!$E$3:$E$10000...
Or use dynamic named ranges..
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Thank your very much.
your are right. I fixed the range to row 50000 then its calculating little faster than earlier but every day i have to paste the data on data sheet to generate the report and that can reach to row 65000, the how can i give the range to row 10000 or row 50000 thousand to calculate faster. there should be another way to calculate faster than earlier sumproduct or sumif formula. please help me
Thanks & regards
Noorullah
Sir,
Another query is that when i change the date range to see the whole month report or any particular date report, calculating slow. how to delete the name ran
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks