Task - If a cell on a row contains "text" then enter data from various points on new work

Greetings all, i have a new project and im pretty stuck on best way to do what i need to be done.

Basically in sheet "Audit i will populate data in various fields. once completed i need a report.

Sheet 2 "report" Contains a couple of headings that i wish to automatically populate with the data from sheet "Audit" If cell D contains N or NO which signifies non compliance i want for each instance of this to be entered in Report sheet copying over the relevant information to each box and go down the page accordingly.(as a plus if it adds borders even better)

So for example: row 3,5,7 in my example file would be NO for non-compliant and i would then want the report sheet to contain going down all 3 clauses their particular requirement info, details of non compliance and category. (See Sheet "sheet 3" of what it would look like if working right)

Example Book.xlsx

If anyone can help me i would be most appreciative as i have spent hrs trying various things using functions and it seems i need some kind of macro to perform the task...

Kind Regards
Dave.R

Hi,

See the file.

WOW and i mean WOW such a good response and so fast!! made me feel really stupid lol

Thanks to both of you for replys. the 1st file seemed to be difrent to the second file but im not sure how these were made or how they work?

the 1st file seems to use drop down lists on AUDIT sheet which is displayed on my Report sheet except if i change a value in AUDIT sheet from N to Y or visa versa it doesnt update report sheet.

The second sheet does exactly what i wanted but i dont understand how it is done, Maybe you can explain to me so i can use this going forward in other projects.

In any case second file is what im going to use as it works straight out off the bat!

Cheers Guys or gals! /respect

Hi,

By first sheet are you ref. to my file?

I was referring to the other one but on further investigation i figured your one out also and it also works perfectly albeit formatted differently. 2 working results to same question AMASSING.

Would it be possible to in the Report sheet add results from 2 Audit Sheets from same work book in same way? so for example if i had 3 issues in sheet 1 and 5 in sheet 2 those would be displayed together on report sheet? and have those formatted to have a box around them?

Regards

Dave.R

I would suggest to compile all AUDIT Sheet into one main sheet and then form a REPORT.
Or,

Why don't you use Pivot Table?

please if anyone can help with this.

need to make EFB report sheet on to New Example report to reference data from audit sheet a sdescribed in previous post, im really stuck on this. been learning all about vlookup, index , pivot tables and iferror and can not for the life of me see how to get the result from EFB file to my new example, it just keeps failing...

Wait for some time, will see how best I can help.

i will i got all weekend lol, spent 2 days trying to get to stage you guys helped me get to. so close but so far...
appreciate anything ya can do.

See the file, it uses VBA, I had placed a button on report sheet. Any time you update any clause on any audit sheet and come to report sheet and press the button the data will be updated. Also note with this solution if you add more Audit sheets, those sheets will also be taken care of.

il take a look at this when i get home i appreciate your efforts.

is there a reason why this time you used a macro rather than formulas like in your 1st file and EFB version?

In my experience in Excel (which is not very much) for creating such reports where various sheets are involved Formula are not capable of looping through sheets.

VBA has a upper hand. The code I had used is very simple not complex just two loops and few IF's. Go through it, apply it on your original file and if you face any problem than write back.

New Example 2.xlsm

Had a good look at what you did and it works fantastically i think your a rock star. I can see why you chose to do it that way rather than in cell formulas.


Would it be possible to apply the same code to this new file? so that with data in right place looking like my Report sheet does now? i thought there might be a way to either create the format like i did in the vba creation part like it does when it labels cell "Clause" or somiething or it could be programmed to ignore the format i created and start the report below it rather than clearing each time button is pressed.

Additionally- where i placed compile report button could the VBA be adjusted to check for a tick box of some kind or a cell with a number or word in it to compile report based on that requirement?

For example if i wanted to compile full report of all sections then i "tick a box" for full or enter word full in a cell. summarily if i wanted to just compile 1 specific section i could tick section 7 box or enter 7 in a cell?

i was thinking i could have say 8 tick boxes labelled next to compile button that i could tick then compile to perform that function or perhaps one cell to enter FULL, or 1 or 7 for example then hit compile...

Again any help appreciated.

Regards
Dave.R

Can you wait on this for a day or two.

Of course i can mate! its the weekend, enjoy it!
At least the weather is ok at the moment. BEER TIME!

Hope you had a good weekend?
Have you had a chance to look at this again for me?

Yeah I did had a good weekend, now back to my home town for some leave days. Will see now.

Sorry to bother you guys again, but if any chance somone could take a look at this again for me as im needing it to be right as soon as possible and my tinkering is getting me nowhere.

much appreciated.

Hi,

Will give it a short tonight.

Regards,

did ya have a chance to look buddy?

Hi,

Sorry I could not got time to look at it, may be this weekend I will confirm with a code, just to make sure, you now want it now conditional, adding new line below is not a problem, but if some clause is turned No to Yes that row has to be removed or just edit in the report?

Regards,

the code you have is fine for consolidating the data from those other sheets.... when i hit the button it needs to compile that data in the same way but formatted like i have done so it looks better.

one way i though of doing it was to have code to ignore the top 6 lines which i have already created columns, formats and labels, but ideally if possible i would like the code to generate that formatting and labeling.. for this project it would be great and the code would be able to be used for other projects going forward...

perhaps also if possible you could add some comments as to what does what in the code?

Again i appreciate all the help you can give me..

Regards
Dave

Ok, But I did not understood one thing the headers on sheet Report from row 1 to 6, do you want that 2 generated from code or they will be static and data should be populated from row 7?

No. 2) There are some columns on report sheet that dont have data on other sheets like Column F,G,H & I on report sheet, where these columns will get the data?

Regards,

sorry for delay in reply,

to answer your question: Ideally yes i would like the code you made to turn a blank clear sheet and format the top 6 rows they way i have shown with the data that is consolidated in your code starting from row.

(if this is not possible then like you mention the top 6 rows could be static and the code could display results from row7 with the text format and size etc defined in code)
--------------I would prefer if possible for whole thing in code a si could then use in many projects going forward that use similar formats-----------

to answer your second question: yes there are some headings that have no reference to data. This is because once the form is created (including consolidated data from other sheets as a result of "n" or NO") this would mean the auditee would need to complete manually entering information in columns F to I

If its at all possible where the code enters info in the columns could it draw a border around all cells from A to I? where data is present?

Again thanks for all your help any more questions please feel free to ask.

Cheers

Hi again sorry to spam i was just wondering if this last post made sense?

Hi,

Sorry I was ILL. See the file, hope this will meet your requirement.

Regards,

ahh i hope its not too serious, dam office flu going about its like pass the parcel with germs LOL, i will look it over today with excitement

The sheet now works an absolute treat thank you so much it is really workable now.

can i ask if it is easy enough to do a few more things? mainly formating.

1) add an item number if there is text in the clause cell next to it?

2) cells in column F,G,H,I and their cells below row 7 to have the text wrap turned on.

3) columns A,B,E below row 7 to have a text size of 16

4) Column C text size 12 under row 7

5) columns D,F,G,H,I text size 14

Im sure there is probably a realy easy way to do this but i dont want to muck up what i think is wicked code by your self


With the above done is it possible to add another feature to your code?
specifically where A2:C2 is currently merged and i wrote some stuff in the box i was thinking.

Could there be say 8 tick boxes placed in there that determine which sections are compiled in to the report?

Example Tick box 1 , Tick box 2 , Tick box 3 up to 7 tick box 8 = ALL
so if 1 and 2 are ticked then consolidate data from only section 1 and 2 sheets. if box 8 ALL is ticked report on all 7 sections sheets.? if tick boxes are selected and 8 also return an error message or something..

this would be cream on the cake and well worthy of me to worship the ground upon which you walk

Hi,

See the file, it take care of all the formatting. As far as the last tick box thing is concerned, I doubt it will take some time and next week I am busy with some official work so will not be able to devote time on it.

Hope you will understand.

Regards,

Originally Posted by misrasomendra

Hi,

See the file, it take care of all the formatting. As far as the last tick box thing is concerned, I doubt it will take some time and next week I am busy with some official work so will not be able to devote time on it.

Hope you will understand.

Regards,

I totally understand work is always priority etc and i appreciate all your help..

I will just try and see if i can learn more and figure something out....one option is to just leave all sheet sections clear and consolidate report after a section is audited, this will only report from that section. then if i needed entire audit of all sections just consolidate entire book all 7 sections.

i just figured if i had a check box system it may be more use full in long term...

Again thank you for your help..