Greetings to all Admins and Members.
I have created database for my store inventory and also a sheet to summarise the weekly closings but unable to connect the week ended date and balance quantity unless I input it manually.
Please help to check the formula and how to connect the data into a summary if its possible.
Thank you.
Ravindran
1st, when using dates, you should always try to use REAL dates. 16.8.15 is not a date to excel, it is text. 16/8/15 is a real date
2nd, you could probably use INDIRECT to find the sheet/date you want, but the names in Balance Sheet col A need to match EXACTLY to what your sheet names are.
Once you have corrected those 2 points, we can start towards getting the formulas you need.
On a side note, you dont need to use SUM when referencing a single cell (it's not wrong, just unnecessary...
=SUM('Roadline Green'!F16)
could be just...
='Roadline Green'!F16
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
FDibbins is right, you should use real dates. But as your data is set up now, there is a way as in the attachment. Insert a new column with Index no.(startign at 3, because first 2 should not be included). Define name for all Sheets like this: go to Formulas - Define Name - I've added the name SheetsN(add whatever you like) and then add this formula in the formula bar: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"") - OK.
In B2: =IFERROR(INDEX(SheetsN,A3),"")
Then add this formula in D column and drag it right and down:
=IFERROR(VLOOKUP(D$2,INDIRECT("'"&$B3&"'!"&"A:B"),2,0),0)
Click the * to say thanks.
Dear FDibbins and Paul. Thank you very much for your fast reply and advise.
I have amended the dates and managed to get the stock count as highlighted in the attached sheet.
As Paul advised I have insert the formula in column D and the figures updates but the formula (B2) I could not insert it comes out blank. Maybe I'm putting it wrongly.
Please review the amended worksheet.
Again, Thank you very much.
Not sure why you have blanks. To me it displays the correct results. I saved as xls and it still calculates them correctly
Hi Paul, In the B column (Brands) from A3 to A9 I have manually typed the sheet name. is it because of that? The calculation as I input on the respective sheets it captures the results in Balance sheet.
Good Day All,
I have managed to insert the data and formula as suggested by Paul and Fdibbins. Able to capture balances from different products into the Balance Sheet. Thank you Paul.
But cant figured out why in balance sheet index sheet no 16 cell 18 unable to detect the quantity in balance sheet.
Attached for your review.
Thank you.
The problem is that the worksheet named "2 Paint Brush" has a superfluous space character at the end of the name. Remove that space and the balance sheet will be populated correctly.But cant figured out why in balance sheet index sheet no 16 cell 18 unable to detect the quantity in balance sheet.
Dear All, I have managed to solve it and it works now. Thank you very much for your assistance.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Happy to help and thanks for the feedback
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks