• 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.

Automatically updating formulas when a new sheet is created

S

soul1974

New Member
Morning all,

I am new to using Excel and I need some guidance.

I am using Office 365 on a Windows 11 PC.
I have attached a sample of the worksheet.

I have an incoming register and I need to create data packs from it.
I need to create a sheet(report) for every row in the register, with specific data from that row. However, every time I duplicate the sheet, I have to manually update it.

Every time I duplicate the report I want to populate the required cells automatically. so if I duplicate report 1 then I would need report 1 (2) to populate the info from the line PF-02 and so on and so on

As you will see in the worksheet the first couple of reports I had to manually insert the cells I required copied across to the cells.

Is there a way to copy the sheet and have the formulas update automatically?



Thank you in advance for your assistance
 

Attachments

  • Incoming Visual Inspection Register Test.xlsx
    117.3 KB · Views: 2
Hello Soul

Try this!

Code: 
=IFERROR(INDEX('Incoming Register'!$B$2:$E$100,MATCH($A2,'Incoming Register'!$A$2:$A$100,0),COLUMN()),"")
 
Monty said:
Hello Soul

Try this!

Code: 
=IFERROR(INDEX('Incoming Register'!$B$2:$E$100,MATCH($A2,'Incoming Register'!$A$2:$A$100,0),COLUMN()),"")
Click to expand...
Thanks for the replay I appreciate your time, the code does not work for what I require.
I require every time I duplicate the report that it pulls the information from from the next line in the register.
For example, Report One is for PF01 then I require certain information from that Row
if I duplicate the report for PF02 then I require the data from that row and so on and so on
 

soul1974​

Could something like this sample work for You?
Usage:
# Fill used data to Incoming Register
# Press ( left-top-corner) [ Create PFs ]- button to get those copies (without formulas)
 

Attachments

  • Incoming Visual Inspection Register Test.xlsb
    57.3 KB · Views: 5
vletm said:
HI Vletm,
Click to expand...
I am incredibly grateful for the assistance you provided me with regarding my problem. Your help has been invaluable, and I cannot thank you enough. It is worth noting that out of the eight forums I reached out to, yours was the only one that provided me with any meaningful assistance.

Your expertise and attentiveness have made all the difference, and I am truly impressed. I will certainly recommend this forum to anyone who needs help in the future.

Once again, thank you for your help, and I wish you all the best.

Thanks again,
vletm said:

soul1974​

Could something like this sample work for You?
Usage:
# Fill used data to Incoming Register
# Press ( left-top-corner) [ Create PFs ]- button to get those copies (without formulas)HI Vletm
Click to expand...
 
Hi,
Thanks for the your comments and well notes I have removed my self off of the other forums and I have posted that I got help by you on this forum.

Please note, this is the first time I am posting asking for advise since I am not at all good with vba.

My apologies if I offended any one.

Regards
 
vletm said:

soul1974​

It was one thing which You too should known before the 1st posting.
Eg if You skip the last step, then those thread are still open/unsolved
... others still try to find their solutions for You.

As I wrote, it was a sample.
There could do some modifications that it would be more useful.
Did You have an master idea - how to use it?
Click to expand...
HI,

Thanks again.

I have closed all open threads.
as for a master idea, I am working on it.
 

soul1974​

#4 reply's file has at least one information don't update (8th rows).

I tested some other way to take care this case.
I'm still wondering .. why do You would like to have so many sheets?
I tested to do 2nd sample version, which has one 'Repair Report'-sheet for all datas from 'Incoming Register'-sheet.
... a sample is always a sample ...
 

Attachments

  • Incoming Visual Inspection Register Test.xlsb
    59.1 KB · Views: 4
vletm said:

soul1974​

#4 reply's file has at least one information don't update (8th rows).

I tested some other way to take care this case.
I'm still wondering .. why do You would like to have so many sheets?
I tested to do 2nd sample version, which has one 'Repair Report'-sheet for all datas from 'Incoming Register'-sheet.
... a sample is always a sample ...
Click to expand...
Hi
Thanks for the sample file. I require a report for each line and then I populate photos taken on site.
 

soul1974​

... hmm?
You could get a report for each line from something like this.
You could save Your report as PDF too.
What is Your point?
>> Did You post solution to every Forum?
>> Did You check each Forum's Rules?
>> Reply #12 above
 
# You soul1974 wrote I have closed all open threads.
# Soon You got a note about EF
#My image is that You have not gotten the solution - so far, 'mine' is a sample.
... but if You want to continue with EF, then I'll stop or
You should do as You wrote about closing.
Please reread this Forum's Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
Almost same kind of rules will be everywhere.
Yes, everyone starts somewhere ...
eg in this Forum it should start from link New Users - Please Start Here
... and learns too.

I tried to ask about my the latest question about that my the newest version.
... which has basically two sheets (+Your help sheets) for everything.
 
vletm said:
tried to ask about my the latest question about that my the newest version.
... which has basically two sheets (+Your help sheets) for everything.
Click to expand...
Sorry about that. The file didn't work for me. I'll delete it and try again.
 
What did not work for You?
# Incoming Register-sheet ... You'll fill needed informations.
# Select one of those rows and press left-top-corner -button to
... fill needed information
>>> press left-top-corner -button to go back to Incoming Register-sheet
...
Of course, there should be 'few' more things ... that's a sample!
 
vletm said:
What did not work for You?
# Incoming Register-sheet ... You'll fill needed informations.
# Select one of those rows and press left-top-corner -button to
... fill needed information
>>> press left-top-corner -button to go back to Incoming Register-sheet
...
Of course, there should be 'few' more things ... that's a sample!
Click to expand...
Thank you
 

soul1974​

... something worked?
My opinion is that there could do some features other way ...
eg those Remarks ... normal way
or You could see other way - which remark You'll choose.
The Repair Report seems to have two pages ...
... the 1st page's header can use for both pages.
You could get a PDF from it.
Ps. This is still a sample - there are missing functionalities.
 

Attachments

  • soul1974.xlsb
    69.3 KB · Views: 2
vletm said:

soul1974​

... something worked?
My opinion is that there could do some features other way ...
eg those Remarks ... normal way
or You could see other way - which remark You'll choose.
The Repair Report seems to have two pages ...
... the 1st page's header can use for both pages.
You could get a PDF from it.
Ps. This is still a sample - there are missing functionalities.
Click to expand...
Morning,
Thank you for the changes.
This is an exciting change. Would it be possible to select more than one serial at a time?
 
vletm:
What do You mean about more than one report at a time ?
Let's say that I require from 1 to 10 at the moment it only does the one you select. I have tried doing multiple selections, and it will only do the 1
 

soul1974​

... hmm?
... Your let's something ...

Do You try to explain that You require to modify more than one PF in time?
... no no no no no! ... one-by-one.

If You need at the moment ... required from 1 to 10 then ... You can press [ ReSet ]-button to 'clean' Incoming Register-sheet.
... then there would be PF-01 ... PF-10 left.
If You'll need more PF's ... then press [ New DRBM ID ]-button.

You can MODIFY those PFs one-by-one.
BUT ...
If You have same values in many PFs ...
then select 'master' PFs Serial Number and press [ Duplicate ]-button.
... >> follow instructions >>
then 'master' PFs values would paste to those selected 'yellow' PFs.
Note: 'Master PFs' has to have created PDF ... before it can select for it!
Note: To get PDF - You've to have folder, which name is PDF in same folder as soul1974.xlsb-file

Or
What is Your plan to do with multiple selection?
 

Attachments

  • soul1974.xlsb
    75.2 KB · Views: 2
vletm said:

soul1974​

... hmm?
... Your let's something ...

Do You try to explain that You require to modify more than one PF in time?
... no no no no no! ... one-by-one.

If You need at the moment ... required from 1 to 10 then ... You can press [ ReSet ]-button to 'clean' Incoming Register-sheet.
... then there would be PF-01 ... PF-10 left.
If You'll need more PF's ... then press [ New DRBM ID ]-button.

You can MODIFY those PFs one-by-one.
BUT ...
If You have same values in many PFs ...
then select 'master' PFs Serial Number and press [ Duplicate ]-button.
... >> follow instructions >>
then 'master' PFs values would paste to those selected 'yellow' PFs.
Note: 'Master PFs' has to have created PDF ... before it can select for it!
Note: To get PDF - You've to have folder, which name is PDF in same folder as soul1974.xlsb-file

Or
What is Your plan to do with multiple selection?
Click to expand...
Hi,
Thanks for all the help.

It takes too long only to do one report at a time. I would typically create all the reports at once. With this version, I can only modify one at a time.
I tried to view the code, and it is password-protected now.
 

soul1974​

Now I got image - what did You mean ...
BUT
... let see ...
How many reports can You see in one time?
> 'old-way' - one.
> 'this-way' - one

if You have done 1st PF-01 and after that You want to see PF-10
> 'old-way' - You gotta find PF-10 by scrolling right.
>' this-way' - You gotta scroll PF-10 by scrolling down.

if You have more PFs there ... as with Your original file ... PF-200
> above scrolling would be smoother with 'this-way'

If You have there 10 PFs ...
> 'old-way' ... You will have there 10 PF-sheets
> 'this-way' ... You will have there 1 PF-sheet

How could You modify more than one PF in one time? ... one
How could it take longer then? ... how?

The code is protected now,
because I did some quick tests about Your multiple selection wish.
 
You must log in or register to reply here.
Back
Top