add sheet name in column & show & pickup the item quantity in columns form purchase sheet

[rana19]

r/sir
i add this "=MID(CELL("filename",A14),FIND("]",CELL("filename",A14))+1,255)" sheet name formula in A14 column in sheet3 & than sheet3 B16 to R16 columns only show dated "1-09-2015" item quantity pickup from purchase sheet
i add this "=MID(CELL("filename",A14),FIND("]",CELL("filename",A14))+1,255)" sheet name formula in A14 column in sheet4 & than sheet4 B16 to R16 columns only show dated "2-09-2015" item quantity pickup from purchase sheet
than i am insert new sheet & rename this sheet with date for example i am insert sheet5 & rename with date "3-09-2015 & A14 column show sheet name & than sheet5 B16 to R16 columns only show dated "3-09-2015" item quantity pickup from purchase sheet & the balance stock was carry forward sheet 2 sheet & + the balance stock in opening balance in sheet to sheet please send the formula for this & resolve this issue sir
please see the attchmnets thanking you sir

[FlameRetired]

Three things I noticed.

Your formula for returning the sheet numbers is good, but it returns text since MID is a text function. Dates are numbers. Change these

=MID(CELL("filename",A14),FIND("]",CELL("filename",A14))+1,255)

to

=--MID(CELL("filename",A14),FIND("]",CELL("filename",A14))+1,255)

That "--" in front of the formula is called a double unary. It coerces the text "date" into its underlying numeric value ... a 42248 for Sept 1, 2015 or 42013 for Jan 9, 2015. The rest is handled through formatting. Formatting is cosmetic only. It has no effect upon the values. The date generated by the above formula for the 1-09-2015 sheet is 42013 which is actually Jan 9, 2015. The PURCHASE list value is 42248 which is actually Sept 1, 2015. The formatting reveals that formatting for the 1-09-2015 and 2-09-2015 sheets is set to English (United States) or m/d/yyyy. The formatting for PURCHASE is Custom and set to d/mm/yy. (Hence the confusing results.) Make sure the dates are the same numeric value and same formatting (to avoid confusion).

Then change the formula in 1-09-2015 sheet in B16 to

=IF($A14="","",IF(ISBLANK(INDEX(PURCHASE,MATCH($A14,DATE,0),MATCH(B$14,PURCHASE!$B$3:$W$3,0))),"",INDEX(PURCHASE,MATCH($A14,DATE,0),MATCH(B$14,PURCHASE!$B$3:$W$3,0))))

and fill across to R16. Do likewise with the rest of the dates in PURCHASE, the "--" and the above formula in the other 2-09-2015 sheet.

[rana19]

r/sir
everything was working fine but the balance stock in V13 Column was not carry forward to sheet3 to sheet4 & balance stock was display in opening balance stock column S13 in sheet4 than i am insert sheet5 the balance stock form sheet4 was carry forward to sheet5 & + the opening balance stock in S13 column in sheet5
Respected sir please send the formula & resolve this issue sir
thanking you sir & see the attachments i am resend the attachments sir

[FlameRetired]

The recent upload does not have the dates reconciled, and I don't understand the rest of what you are saying.

Try uploading a date corrected file and then please explain to me which cells and ranges you are referring to by cell addresses and hand type in what your expected results should be.

[rana19]

r/sir
i am saying the balance stock in V13 column was not carry forward to sheet3 name 1-09-2015 to sheet4 name 2-09-2015 & the balance stock was copy & paste in opening balance stock column S13 in sheet4 name 2-09-2015 than i am insert new sheet & rename this sheet with name 3-09-2015 in sheet5 name 3-09-2015 the balance stock was copy from sheet4 name 2-09-2015 was carry forward to sheet5 name 3-9-3015 & plus the balance stock in opening balance stock in S13 column in sheet5 name 3-09-2015
Respected sir please send the formula & resolve this issue sir
thanking you sir & see the last posted attachments

[shukla.ankur281190]

Sheet 01-09-2015 S13 =IFERROR(INDIRECT("'"&DAY(--MID(CELL("filename",A14),FIND("]",CELL("filename",A14))+1,255)-1)&"-0"&MONTH(--MID(CELL("filename",A14),FIND("]",CELL("filename",A14))+1,255)-1)&"-"&YEAR(--MID(CELL("filename",A14),FIND("]",CELL("filename",A14))+1,255)-1)&"'!v13"),0)

Please Login or Register  to view this content.

Use this formula in All of sheets S13 cell.

Please check the attachment.

[rana19]

R/SIR
i add Copy Sheet command button & some rows in this excel sheet & i am re-insert this formula=IFERROR(INDIRECT("'"&DAY(--MID(CELL("filename",A14),FIND("]",CELL("filename",A14))+1,255)-1)&"-0"&MONTH(--MID(CELL("filename",A14),FIND("]",CELL("filename",A14))+1,255)-1)&"-"&YEAR(--MID(CELL("filename",A14),FIND("]",CELL("filename",A14))+1,255)-1)&"'!v13"),0) in this excel sheet W107 column than i am clink on "Add New sheet And name as current Date" button the new sheet was added but this formula was not working i am send the edited excel sheet please correct this formula
thanking you sir & please see the edited excel sheet attachments sir

[rana19]

r/sir
please resolve my issue sir & reply to me sir thanking you sir

[shukla.ankur281190]

Formula if absolutely working fine. Let me explain you when i had provided the formula that time you were using the sheet names like 1.09.2015,2.09.2015 & 3.09.2015 etc now you are useing 01.09.2015,02.09.2015 & 03.09.2015 that is why it is happen. Please use below one for leading 0 with sheet name

=IFERROR(INDIRECT("'"&"0"&DAY(--MID(CELL("filename",B108),FIND("]",CELL("filename",B108))+1,255)-1)&"-0"&MONTH(--MID(CELL("filename",B108),FIND("]",CELL("filename",B108))+1,255)-1)&"-"&YEAR(--MID(CELL("filename",B108),FIND("]",CELL("filename",B108))+1,255)-1)&"'!z107"),0)

Please Login or Register  to view this content.

[rana19]

r/sir
no this formula is not working in this excel sheet i add Copy Sheet command button & some rows in this excel sheet & i am re-insert this formula in this excel sheet W107 column than i am clink on "Add New sheet & name as current Date" button the new sheet was added but this formula was not working i am send the edited excel sheet please correct this formula please resolve this issue i am resend the attachments sir please see the attachments sir

[shukla.ankur281190]

Your closing balance is as on 01.09.2015 then how can you put this opening balance as on 29.09.2015 first thing.

Second you will have to add the sheet with naming next date name for getting the opening bal as on 29.09.2015.

Hope now your query have been solved.

[rana19]

Respected Sir
you can edit this excel sheet & resolve this issue sir thanking you sir

[shukla.ankur281190]

How did you get this VBA code. If you can change your VBA code it can easily work. Anyway if you are looking for solution to me then i will suggest you to put sheets name in series like 1-09-2015,2-09-2015,3-09-2015 and etc then Above given formula will work. It is because the formula is putted Indirect "current sheet date - 1" so if current date is 29-09-2015 it will refer the 28-09-2015 sheet's cell no W107 and which is not in your spreadsheet result become 0.

I have no knowledge of VBA , so this time I am sorry.