list of data to be populated into a predefined report template without VBA

I want help from you guys
i have an excel sheet extracted from the application where the data sheet is there but i have also a report template where the data from the sheet needs to be loaded into the report template
how can i do it without VBA

sorry for the delayed reply. I was long time away with sickness.
I have a new problem with the excel sheet which is attached.
there is a data sheet and it requires that a date field which is in string format, the name is "actual date for drills" field. The report should pick up the date and check the latest date for the condition if the same vessel as selected in the report sheet and the year in the report sheet
It should return in the cell where it is assigned january,february and so on, the corresponding matching date but unfortunately all the time I am getting error. I tried different formulas to achieve this but no success.
do you have any advise with your expertise
appreciate your feedback
thank you for your help and assistance.

yes you are right. Somehow since the date format in the data sheet is in text format, i cannot display it from that sheet into the report sheet.as you can see in this attached the formula, all the remaining part looks ok except the date extraction.

further to above, I am looking forward for your assistance to area (number of boats):
I have a scenario where there is a two column in the sheet named data.each column has values like "true" or "false". now based on the case that it should check the maximum of the date and matching the cells of main sheet, it should check if the two columns in data sheet has a text "true" then it should display in the main sheet cell as "1,2" or if in the two columns of data sheet if column 1 is true, then display "1", if column 2 is true , then it should display "2".
i tried the formula but it is not working

I cannot do anything in the data sheet because this is updated or reset everytime the data is extracted from the application.So any changes that i can do is in the report sheet.

Sorry it didnt work fine. I am always getting one date as 08/12/2023 if I select for example AFIF and year 2023. actually it should give me in area december that value and there is no date in the dat sheet for august

I am attaching the latest sheet again after extraction
i used a different concept by using dynamic range and it seems working now.

but I have got another problem for the next part

in the number of boat area of the report sheet.

I am trying to get the value as below
I created dynamic range for the column No1 and No2 and they are Boolean values from the application so in excel it shows TRUE FALSE in these columns.
If dynamicNo1 is 1 and dynamicNo2 is 1, it returns "1,2".
If dynamicNo1 is 1 and dynamicNo2 is 0, it returns "1".
If dynamicNo2 is 1 and dynamicNo1 is 0, it returns "2".
If none of the above conditions are met, it returns "NA".
my formula I used for this is as below, but it is somehow not working for this. kindly request your assistance. you can also find this formula in the excel cell under December

=IFERROR(
INDEX(
IF(
AND(N('SAFETY REPORT v2.0.XLSX'!dynamicNo1)=1, N('SAFETY REPORT v2.0.XLSX'!dynamicNo2)=1),
"1,2",
IF(
AND(N('SAFETY REPORT v2.0.XLSX'!dynamicNo1)=1, N('SAFETY REPORT v2.0.XLSX'!dynamicNo2)=0),
"1",
IF(
AND(N('SAFETY REPORT v2.0.XLSX'!dynamicNo2)=1, N('SAFETY REPORT v2.0.XLSX'!dynamicNo1)=0),
"2",
MATCH(
MAX(
IF(
(MONTH(DATEVALUE(LEFT('SAFETY REPORT v2.0.XLSX'!dynamicC,10)))=12)*
(YEAR(DATEVALUE(LEFT('SAFETY REPORT v2.0.XLSX'!dynamicC,10)))=VALUE($AD$2))*
('SAFETY REPORT v2.0.XLSX'!dynamicAG=$J$2)*
('SAFETY REPORT v2.0.XLSX'!dynamicC<>""),
DATE(1900,1,1)+DATEVALUE(LEFT('SAFETY REPORT v2.0.XLSX'!dynamicC,10))-1
)
),
DATE(1900,1,1)+DATEVALUE(LEFT('SAFETY REPORT v2.0.XLSX'!dynamicC,10))-1
)
)
)
),
"0"
),
"NA"
)

thank you very much for your kind effort and solution. the first formula worked for BI15 but the second formula is not working. I am finding while evaluating it is only checking $R$2:$R$15 but there are 20 rows and so when I select the vessel "AFIF" it shows value as NA

you can check in my sheet with the formula by evaluating.

when I select "Cartagena express" it works because it is within the row 15 of this columns. I checked anything after the row 15 is returning this was the same what i was facing also...

thanks for your advise.

May be is it the dynamic column which is defined , so may be the offset is not working for my excel. is there any alternative which I can replace for offset in name manager namely INDEX or something..

thank you for your advise.

also I can see that the second formula not working when one of the column is completely blank as below; just to inform that these columns are coming from application as the result of checkbox selection where it is selected or not selected.
So in the excel it is BOLLEAN but whatever I try I cannot pick the value for the blank cell.
thats why I need your help.
No1 No2
FALSE
TRUE
TRUE
TRUE TRUE
TRUE TRUE
TRUE TRUE
TRUE
TRUE TRUE
TRUE TRUE
TRUE TRUE
TRUE FALSE


TRUE TRUE
TRUE
TRUE TRUE
TRUE TRUE
TRUE
TRUE TRUE

thank you very much
So offset was the problem yes it seems working for AFIF now.
but the problem still remains that if any one of the column is blank i.e., nothing there in the cell, then it is not working.
there needs to be something in the cell to make it work. for example rio de Janeiro express has a issue where colum R has TRUE value but the column S is blank so the formula dont work.

thank you it is working fine.

I wouldl like to have an additional help regarding lowered date here with the same condition as above, I need to retrieve the date from Column Q.

thanks for your advise.

thank you very much. This formula worked too thank you again for all the help. Now i need to build the complete excel sheet with these formulas.

thank you I will surely appreciate to give feedback.. but I have to stop by again asking your help with this.
I got a problem with the case that suppose there are chances that a particular vessel e.g., AFIF has more than one activity in the same month. suppose from the data sheet if there are two records and i need to show just below the cell again e.g., below AI15, the next date or any date, there is no order of date for example one date showing as 2023-12-08T00:00:00 for a selection AFIF and year 2023 and also showing another date 2023-12-15T00:00:00, then how to get the seond date if required based on the formula you provided as below
=AGGREGATE(14,6,Data!$D$2:$D$20/(MID(Data!C$2:C$20,9,2)+0=8)*(LEFT(Data!C$2:C$20,4)+0=VALUE($AB$2))*(Data!$AJ$2:$AJ$20=$J$2),1)

thank you for your advise.

this formula is not giving the result

i am attaching the latest with the cases highlighted in red in the data sheet


thank you for your advise.

sorry for the delayed reply. this works for AFIF but when I select the other vessel with red highlight it dont return anything even if there is value for that.

thank you for the solution, now all done.

I want to reopen this case with a different issue
all the formulas are working while I use the calculated excel but once it is linked to the report in the application and again downloaded then formula is intact but the formula is not working.
I am thinking it is something to do with the dynamic name used in name manager.

I am not sure how can I fix this issue when every time I download or the user download the formula is not working for the excel.

attached is the latest file for your reference where the formula after download from the application not working.

kindly request your advise how to fix this issue about dynamic formulas used

thank you, it seems that was the issue I just put random date then it was showing correctly. But I am thinking I have the check if dynamicC<>"", why that factor is not working.
kindly advise.

I have again challenge in solving one part in the excel shet I need to define values in the rows 25 and 26 of the report

the condition is as below.

in the sheet LIFEBOATMANOEVER I have a O column, based on which if there is TRUE value then it should retreive the matching row from column C of this sheet
below is my formula but dont work
=IFERROR(
IF(
MAX(
IF(
(MONTH(DATEVALUE(LEFT('SAFETY REPORT v2.0.XLSX'!LBMANOVERC,10)))=12) *
(YEAR(DATEVALUE(LEFT('SAFETY REPORT v2.0.XLSX'!LBMANOVERC,10)))=VALUE($AD$2)) *
('SAFETY REPORT v2.0.XLSX'!LIFEBOATAE=$J$2) *
('SAFETY REPORT v2.0.XLSX'!LBMANOVERC<>""),
IFERROR(
INDEX(
'SAFETY REPORT v2.0.XLSX'!LIFEBOATO,
AGGREGATE(15, 6, (ROW('SAFETY REPORT v2.0.XLSX'!LIFEBOATO)-ROW('SAFETY REPORT v2.0.XLSX'!LIFEBOATO)+1) / ('SAFETY REPORT v2.0.XLSX'!LIFEBOATO<>""), 1)
),
DATE(1900,1,1)
)
)
)=0,
"NA",
MAX(
IF(
(MONTH(DATEVALUE(LEFT('SAFETY REPORT v2.0.XLSX'!LBMANOVERC,10)))=12) *
(YEAR(DATEVALUE(LEFT('SAFETY REPORT v2.0.XLSX'!LBMANOVERC,10)))=VALUE($AD$2)) *
('SAFETY REPORT v2.0.XLSX'!LIFEBOATAE=$J$2) *
('SAFETY REPORT v2.0.XLSX'!LBMANOVERC<>""),
IFERROR(
INDEX(
'SAFETY REPORT v2.0.XLSX'!LIFEBOATO,
AGGREGATE(15, 6, (ROW('SAFETY REPORT v2.0.XLSX'!LIFEBOATO)-ROW('SAFETY REPORT v2.0.XLSX'!LIFEBOATO)+1) / ('SAFETY REPORT v2.0.XLSX'!LIFEBOATO<>""), 1)
) + DATEVALUE(LEFT('SAFETY REPORT v2.0.XLSX'!LBMANOVERC,10)) - 1,
DATE(1900,1,1)
)
)
)
),
"NA"


)


Actual Date of Drillj Company Lead Name Company Lead No. Count Create Date Debriefing Enforce Confidentiality Equipment back in place after drill Equipment ready for emergency use Form No. Form Template Name Form Template No. Freefall Lifeboat
2023-11-20T00:00:00 1 02/12/2023 "Debriefing carried out at the aft station, all questions and observations reviewed
" FALSE TRUE TRUE 9450404-2023-FT-Drill-MAN-REP-001 Lifeboat Manoeuvering Drill - Drill Report - Form Template FT-Drill-MAN-REP TRUE
2023-12-27T00:00:00 1 28/12/2023 Drill has been reviewed afterwards and actions have been discussed with everyone involved FALSE TRUE TRUE 9508902-2023-FT-Drill-MAN-REP-001 Lifeboat Manoeuvering Drill - Drill Report - Form Template FT-Drill-MAN-REP
2023-10-08T00:00:00 1 06/12/2023 Drill sequence reviewed in detail and single steps instructed. FALSE TRUE TRUE 9295268-2023-FT-Drill-MAN-REP-001 Lifeboat Manoeuvering Drill - Drill Report - Form Template FT-Drill-MAN-REP TRUE
2023-12-26T00:00:00 1 29/12/2023 Debriefing was carried out to the attending crew drill was concluded with a satisfactory result. FALSE TRUE TRUE 9777618-2023-FT-Drill-MAN-REP-001 Lifeboat Manoeuvering Drill - Drill Report - Form Template FT-Drill-MAN-REP
2023-12-07T00:00:00 1 08/12/2023 Crew had the possibility to ask questions on scene. All questions were well answered. FALSE TRUE TRUE 9613018-2023-FT-Drill-MAN-REP-003 Lifeboat Manoeuvering Drill - Drill Report - Form Template FT-Drill-MAN-REP
2023-12-12T00:00:00 FMS Manager O-FMS 1 17/12/2023 - Debriefing carried out after the drill. Drill was successful and no remarks. FALSE TRUE TRUE 9540120-2023-FT-Drill-MAN-REP-001 Lifeboat Manoeuvering Drill - Drill Report - Form Template FT-Drill-MAN-REP
2023-12-07T00:00:00 1 15/12/2023 "- Drill was carried out satisfactory acc. to ISM Emergency Plan.
- Participating crew members are well trained.
- Attention was also given to proper operation of the winch brake." FALSE TRUE TRUE 9708813-2023-FT-Drill-MAN-REP-001 Lifeboat Manoeuvering Drill - Drill Report - Form Template FT-Drill-MAN-REP
2023-11-24T00:00:00 1 05/12/2023 "- Drill sequence was reviewed and each action were discussed
- Importance of a rescue boat drill as a training for an emergency case was pointed out.
- Evaluation of performance whether the crew has sufficient knowledge, capabilities and skills for respective duties.
- Discussion held after launching test and further checks and maintenance were discussed and arranged.
" FALSE TRUE TRUE 9708825-2023-FT-Drill-MAN-REP-001 Lifeboat Manoeuvering Drill - Drill Report - Form Template FT-Drill-MAN-REP
2023-11-25T00:00:00 1 03/01/2024 "• Restricted visibility of the boat driver and importance of clear communication pointed out
• Importance of painter line pointed out.
• All questions were discussed and answered by C/O and Deputy Safety Officer.
" FALSE TRUE TRUE 9520041-2024-FT-Drill-MAN-REP-001 Lifeboat Manoeuvering Drill - Drill Report - Form Template FT-Drill-MAN-REP
2023-12-13T00:00:00 1 16/12/2023 see Rescue boat drill dated 13.12.2023 FALSE TRUE TRUE 9777620-2023-FT-Drill-MAN-REP-001 Lifeboat Manoeuvering Drill - Drill Report - Form Template FT-Drill-MAN-REP
2023-12-05T00:00:00 1 06/12/2023 "Following topics were discussed and questions of crew answered:
- Chronological sequence of lowering the boat via davit and releasing the boat in case of emergency
- Duties of boat crew members
- Maneuvering exercises
" FALSE TRUE TRUE 9343730-2023-FT-Drill-MAN-REP-001 Lifeboat Manoeuvering Drill - Drill Report - Form Template FT-Drill-MAN-REP TRUE
2023-12-06T00:00:00 1 28/12/2023 - C/O carried out de-briefing for the crew. Questions were asked and answered. FALSE TRUE TRUE 9613020-2023-FT-Drill-MAN-REP-001 Lifeboat Manoeuvering Drill - Drill Report - Form Template FT-Drill-MAN-REP
2023-10-24T00:00:00 1 04/01/2024 • Master discussed several points in efficient and safe launching of the boat as well as proper stowage of lifeboat every time it is removed from its stowage position. FALSE TRUE TRUE 9501344-2024-FT-Drill-MAN-REP-001 Lifeboat Manoeuvering Drill - Drill Report - Form Template FT-Drill-MAN-REP
2023-12-07T00:00:00 1 14/12/2023 Crew was debriefed, all crew was familiarized and all questions were answered. FALSE TRUE TRUE 9777606-2023-FT-Drill-MAN-REP-001 Lifeboat Manoeuvering Drill - Drill Report - Form Template FT-Drill-MAN-REP
2023-12-18T00:00:00 1 09/01/2024 Crew was reminded, that the person operating the painter line needs to keep it tight, in order for the line to have an effect on the boat. It was discussed, that persons should ideally be recovered in horizontal position to avoid cardiac arrest in hypothermic casualties. FALSE TRUE TRUE 9732333-2024-FT-Drill-MAN-REP-001 Lifeboat Manoeuvering Drill - Drill Report - Form Template FT-Drill-MAN-REP

I would request your assistance, so I reopened the solved case.

I dont wanted to create a new thread completely. Sorry for that.

Kindly find that in the attached excel sheet, I have the te rows which needs to oick date based on a column which is having TRUE in the column.
the condition is that if there is true, then respective date should pick up from the other column "Actual Date of Drills"

I write this formula it is not working.
=IFERROR(
IF(
MAX(
IF(
(MONTH(DATEVALUE(LEFT('SAFETY REPORT v2.0.XLSX'!LBMANOVERC,10)))=10) *
(YEAR(DATEVALUE(LEFT('SAFETY REPORT v2.0.XLSX'!LBMANOVERC,10)))=VALUE($AD$2)) *
('SAFETY REPORT v2.0.XLSX'!LIFEBOATAE=$J$2) *
('SAFETY REPORT v2.0.XLSX'!LBMANOVERC<>""),
IFERROR(
INDEX(
'SAFETY REPORT v2.0.XLSX'!LIFEBOATO,
AGGREGATE(15, 6, (ROW('SAFETY REPORT v2.0.XLSX'!LIFEBOATO)-ROW('SAFETY REPORT v2.0.XLSX'!LIFEBOATO)+1) / ('SAFETY REPORT v2.0.XLSX'!LIFEBOATO<>""), 1)
),
DATE(1900,1,1)
)
)
)=0,
"NA",
MAX(
IF(
(MONTH(DATEVALUE(LEFT('SAFETY REPORT v2.0.XLSX'!LBMANOVERC,10)))=10) *
(YEAR(DATEVALUE(LEFT('SAFETY REPORT v2.0.XLSX'!LBMANOVERC,10)))=VALUE($AD$2)) *
('SAFETY REPORT v2.0.XLSX'!LIFEBOATAE=$J$2) *
('SAFETY REPORT v2.0.XLSX'!LBMANOVERC<>""),
IFERROR(
INDEX(
'SAFETY REPORT v2.0.XLSX'!LIFEBOATO,
AGGREGATE(15, 6, (ROW('SAFETY REPORT v2.0.XLSX'!LIFEBOATO)-ROW('SAFETY REPORT v2.0.XLSX'!LIFEBOATO)+1) / ('SAFETY REPORT v2.0.XLSX'!LIFEBOATO<>""), 1)
) + DATEVALUE(LEFT('SAFETY REPORT v2.0.XLSX'!LBMANOVERC,10)) - 1,
DATE(1900,1,1)
)
)
)
),
"NA"
)

i HAVE UPDATED THIS SOLVED AS i CANNOT ATTACH ANY MORE FILES IN THIS AND i DONT KNOW HOW TO DELETE THE ATTACHMENTS

in the sample excel sheet attached i have sheet named LIFEBOATMANOEVER

this sheet has date column in C, and the vessel column in AE, then there is a column O which has either true or blank/

condiiton,
if the vessel matches the drop down in the report sheet with the column AE, and the Year drop down from report sheet matches the columnC of the other sheet, then it should also check if there is TRUE in the O column from the other sheet, it is matches it should return the date from matching row and put it in to a cell in report sheet.

it should also include cases if more than one date is there for the same month and it should be in row 26 of report sheet.
so for december month, the date filled will be for december and for november it should be november date and so on.

the rows that should be populated is 25 and 26 in report sheet. If any one cell works I can adjust the other sheet.for example for month of december.

thank you for your instructions, the attachment worked

I have a sheet named QOILSPILL, there are columns doe date in C(SPILLC),columns for vessel in AI(SPILLAI), other two column W(dynamic column-SPILLW)) and AC(dynamic column-SPILLAC)

condition

for this if there is a value TRUE in SPILLW then it should return a date to specific cell in report sheet
based on the case that the drop down for vessel and drop down for year in the report matches with the sheet QOILSPILL
1. dropdown for vesel matches with SPILLAI
2. dropdown for Year matches with year of SPILLC

Similarly if same other condition needs to be applied provided 1 & 2 are met and also SPILLAC is having TRUE value then should return the value of date in a specific cell in report sheet.

=OFFSET(QOILSPILL!$AC$2,0,0,COUNTA(QOILSPILL!$AC$2:$AC$1048576),1)

this is the same case but two different logic is required thats why I send it in two different posts

I was not sure if the excel could be uploaded or not

so I send it in two different posts

unfortunately when I am using this formula for month of December for the kuala lumpur express it is not working

Thank you it worked, it was my mistake that the dynamic column needs to be set with C reference also.

I want to ask assistance for the logic for the rows 126,128,130,132
in this logic there are column T in the sheet QOILSPILL based on the condition that this is having true value should be picking up the date also considering the same condition that as shown below
=IFERROR(AGGREGATE(14,6,DATEVALUE(LEFT(SPILLC,10))/((MID(SPILLC,6,2)+0=12)*(LEFT(SPILLC,4)+0=VALUE($AD$2))*(SPILLAI=$J$2)*(SPILLW=TRUE)),ROWS(BH$130:BH130)),"NA")

this should populate the row 132 based on the months

similarily there is row 130 which should populate the case when the coulmn W in sheet QOILPILL is true

also for row 132 which should populate the case when the column AC is true

thank you. Seems like it worked now..currently everything seems ok
I would make this one as same or do you prefer it to be solved.
because in the same excel sheet report something is coming up down the sheet which needs filling up.

I will raise a new case if needed since we are still working on the project not yet implemented in the application.