• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

data from inserted sheets

R

Rhon Alvarez

Member
Hi Team,

Is there a way wherein data from inserted sheets would automatically be inputted on a summary sheet of the same file?
ex:
on summary sheet: under columns G & H - rows would be on a per day basis that needs data from row 42 (constant row) of columns F & I respectively for each date inserted as a separate sheet.

summary sheet row 3, columns G & H is dated January 01, 2018 - data is from Row 42 columns F & I of sheet 1.01, row 4 is January 02 and from 1.02 and so on...

file attached for further reference.

thanks in advance.
 

Attachments

  • SQI Template Orig JAN.xlsx
    304.6 KB · Views: 1
Hi,

See attached file with formula cell in red color.

Regards
Bosco
 

Attachments

  • SQI Template Orig JAN(1).xlsx
    286.2 KB · Views: 16
Hi Team,

Need help once again with the attached file. formula on the columns I and J of the Summary Sheet should be dependent on the column A and would appear only every Saturday of the week.
upload_2018-5-28_8-49-35.png

the issue with the current file is if the Saturday falls on an earlier part/row of the file, an error appears instead of the formula.

upload_2018-5-28_8-51-56.png

Thanks in advance.
 

Attachments

  • SQI Template Orig JAN.xlsx
    284.7 KB · Views: 2
Rhon Alvarez said:
Hi Team,

Need help once again with the attached file. formula on the columns I and J of the Summary Sheet should be dependent on the column A and would appear only every Saturday of the week.
View attachment 52524

the issue with the current file is if the Saturday falls on an earlier part/row of the file, an error appears instead of the formula.

View attachment 52525

Thanks in advance.
Click to expand...
Hi,

Unable to check the error, because your attached file is for Jan 2018, while your mistake made file screenshot is for June 2018. Unless you upload the file of June 2018 for checking.

Regards
 
Last edited:
Hi Bosco,

There is no file yet for June_2018. I just discovered the error since when I was supposed to create or ready the file for the incoming month of June, the error on the 2nd screenshot appear. As I've checked, it seems that the formula (MOD) is to refer if the current date is divisible by 7.

What I am trying to ask is, would it be possible that ONLY EVERY SATURDAY of the week will the formula appear and adds up from Sunday of the previous week (always a total of 7 days starting every Sunday and ending every Saturday).

Sorry for the confusion, hope I have it explained well. :) :) :)
 
Rhon Alvarez said:
Hi Bosco,

There is no file yet for June_2018. I just discovered the error since when I was supposed to create or ready the file for the incoming month of June, the error on the 2nd screenshot appear. As I've checked, it seems that the formula (MOD) is to refer if the current date is divisible by 7.

What I am trying to ask is, would it be possible that ONLY EVERY SATURDAY of the week will the formula appear and adds up from Sunday of the previous week (always a total of 7 days starting every Sunday and ending every Saturday).

Sorry for the confusion, hope I have it explained well. :) :) :)
Click to expand...

Then, your upload a file with around 3 weeks data, (which will cause error as mentioned) and together with the expected results

Regards
Bosco
 
Hi Bosco,

As you can see, all the other formulas were being read correctly. What I'm trying to look for this formula to appear on every Saturday of the reporting month. As I've mentioned, it seems that the formula (MOD) is based on the 7-day divisor and maybe that's the reason why when the Saturday of the month lands between the 1st and 6th row an error appears.

you may also check the other incoming months, screenshot below, which I tried to replicate in advance (Aug, Sep, Nov and Dec). All of which the error was on a Saturday that was between rows 1 to 6.

upload_2018-5-28_13-2-53.png

upload_2018-5-28_13-3-23.png

upload_2018-5-28_13-3-48.png

upload_2018-5-28_13-4-23.png

upload_2018-5-28_13-4-44.png
 
Rhon Alvarez said:
Hi Bosco,

As you can see, all the other formulas were being read correctly. What I'm trying to look for this formula to appear on every Saturday of the reporting month. As I've mentioned, it seems that the formula (MOD) is based on the 7-day divisor and maybe that's the reason why when the Saturday of the month lands between the 1st and 6th row an error appears.

you may also check the other incoming months, screenshot below, which I tried to replicate in advance (Aug, Sep, Nov and Dec). All of which the error was on a Saturday that was between rows 1 to 6.

View attachment 52531

View attachment 52532

View attachment 52533

View attachment 52534

View attachment 52535
Click to expand...

Maybe….

The revised formula in I3, copied across to J3 and all down :

=IF((MOD($A3,7)=0)*(G3<>""),SUM(OFFSET(G3,,,-MIN(ROW($A3)-2,7))),"")

Regards
Bosco
 
Hi Bosco,

I just copied the new formula into the file and it's now working good and as what we've just needed.

Many thanks once again and you guys really rock! :) :) :) :) :)
 
You must log in or register to reply here.
Back
Top