In an Excel file, there are four summary sheets labeled as Summary A, B, C, and D. I want to create a formula that can automatically search for the correct month (January, February, March) within these sheets and fill in the corresponding values into the correct columns and blank cells. I’m seeking someone's help to solve this Excel problem.
Are you still using Excel 2010?
Explain the logic here - Summary A and Summary B are both January - what's the distinction?
You need to provide some background logic.
Last edited by AliGW; 03-12-2024 at 05:09 AM.
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.
I have updated the Excel file (master plan rev4). The summary contains more than one month's information. My Logic is shown in the following.
1. Information is manually entered into the summary sheets (A, B, C, D).
2. The headings in the summary sheets (A, B, C, D) match those in the monthly sheets (e.g., Date, Services, Work type, etc.) for each month (Jan 2024, Feb 2024, Mar 2024).
3. Upon entering data into the summary sheets, a formula initiates a search for the corresponding monthly sheet based on the date in the summary sheets.
4. This formula then automatically transfers the relevant data from the summary sheets to the corresponding monthly sheets.
For the last time, are you still using Excel 2010?
NO HELP TO BE OFFERED, please, until this is answered.
Thanks for AliGW
I overlooked the questions. My current version of Excel is 2013. Thanks
Last edited by Ben9108; 03-12-2024 at 09:43 PM.
Hi Everyone
May I know whether my idea is feasible or not in an Excel file? Thanks
Is there a reason why the data in the Summary sheets start in variable columns?
Please ensure SERVICES are consistently named: Electrical vs Elec vs El vs Elect!
Last edited by JohnTopley; 03-18-2024 at 03:54 AM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Then you need to update your forum profile. Please do this NOW.My current version of Excel is 2013.
I will post a solution when you update your profile as requested.
Thanks All
I updated my profile.
How do I add VBA code to Excel?
Please Login or Register to view this content.
1. Insert VBA code to Excel Workbook
2. Open your workbook in Excel.
3. Press Alt + F11 to open Visual Basic Editor (VBE).
4. Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu.
Copy the VBA code above
I added a RUN button to "JAN 2024" sheet to run macro
Last edited by JohnTopley; 03-19-2024 at 02:40 AM.
Hi John, Thank you for your reply my question
Since I do not know how to operate VBA in Excel, do you have other methods to reach my idea?
I think it will be very difficult to do with formula: what would make it (slightly) easier is having a SUMMARY sheet per month i.e. it only contains data for the specified month. Even better, only have ONE SUMMARY sheet.
Formula solution with ONE Summary sheet and it is good (best) practice to have single data source (sheet) for the same data.
in C6 of "JAN 2024"
Formula:
Please Login or Register to view this content.
Copy across to H and down
Copy to I6 BUT change highlighted reference to $I$3
Formula:Please Login or Register to view this content.
Repeat for ALL dates: see highlighted cells in "JAN 2024": change upper limit of range (10000) as required.
Copy to other months.
Consider setting these formula in sheet "TEMPLATE" and, for a new month, copy TEMPLATE, rename and add month dates in Row 3
UPDATE: template now contains both date and data extract formula so use as above.
WARNING: the formula-based solution has 1000s of formula when ALL sheets are considered which will certainly result in performance issues.
If you want to continue with the current monthly reporting format, then, as a minimum, reduce the number of rows (currently 40) to the likely maximum entries for ONE day (10?).
A better solution is to record the data in one sheet i.e. SUMMARY and to have a "report" sheet where the month is selected and its data output.
OR simplify the monthly reports to the format used in "Summary".
If I were a manger, I would glean liitle about my business as the current reports tell me nothing I could not get from a simple(r) listing as it they simple re-format the data.
Last edited by JohnTopley; 03-20-2024 at 05:58 AM.
Purely for illustration: see sheets "SUMMARY" and "OUTPUT"
Select MONTH and STATUS ("*" = ALL)
Click "Run Report" button
Thank You JohnTopley for your reply
May I learn more about the writing formula of Excel from you?
Formula 1: =IFERROR(@INDEX(SUMMARY!$A$2:$G$10000,AGGREGATE(15,6,ROW($A$1:$A$10000)/(SUMMARY!$A$2:$A$10000=$C$3),ROWS($1:1)),COLUMNS($A:B)),"")
Formula 2:
=IFERROR(INDEX(SUMMARY!$A$2:$G$10000,AGGREGATE(15,6,ROW($A$1:$A$10000)/(SUMMARY!$A$2:$A$10000=$C$3),ROWS($1:1)),COLUMNS($A:C)),"")
For the formula, I have the following issues.
1. What is the function of @ in Formula 1?
2. What is the function of ” / “ in both formulas?
3. Why cannot the formula operate in the Excel test file?
The @ is not needed (but appears to have been added by OS 365 which I have) : just remove.
https://support.microsoft.com/en-gb/...2-b56b061328bd
the "/" is division in this part of the AGGREGATE function : use EVALUATE Formula to see how this works
ROW($A$1:$A$10000)/(SUMMARY!$A$2:$A$10000=$C$3) which returns a numeric value or #DIV0 error which AGGREGATE ignores
The formula in your file does work if entered correctly
The highlighted part needs to be the same in each NAME column so as to evaluate to the correct columnPlease Login or Register to view this content.
This shows the disadvantage of not organsing data in a simple columnar format i.e. as per the SUMMARY sheet. Nor does scrolling across mutiple columns make it easy to read or to compare the data.
Last edited by JohnTopley; 03-21-2024 at 03:54 AM.
Thanks, JohnTopley
I have issues with the two different sheets, column “A” is the entry ( no use) in the Master plan 202403 re.4 (ben9108) file, and I don’t understand why the columns ($A:B) are the same in both files.
1. In Excel test re.1 file,
Jan 2026 Sheet, Cell B4
=IFERROR(INDEX(Summary!$D$4:$I$10000,AGGREGATE(15,6,ROW($A$1:$A$10000)/(Summary!$D$4:$D$10000=$B$2),ROWS($1:1)),COLUMNS($A:B)),"")
2. In Master plan 202403 re.4 (ben9108) file,
Jan 2024 Sheet, Cell C6
=IFERROR(@INDEX(SUMMARY!$A$2:$G$10000,AGGREGATE(15,6,ROW($A$1:$A$10000)/(SUMMARY!$A$2:$A$10000=$C$3),ROWS($1:1)),COLUMNS($A:B)),"")
Columns($A:B) evaluates to a value of 2,Columns($A:C") evaluates to a value of 3, etc so this is simply a mechanism of generating the index (number) of the column used in the INDEX formula.
Learrn how to use EVALUATE FORMULA.
Last edited by JohnTopley; 03-25-2024 at 09:19 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks