Shorten Long Formula

[fastcar]

Hi There,

I would like to try and shorten a long formula that I am using to check about 25 sheets for a match.

I have attached an example of what I would like to do with just 4 sheets but eventually this will expand to over 50 sheets plus.

This is just a snippet of the code I am using, this only checks TWO sheets and I currently have approx 25 and increasing

Please Login or Register  to view this content.

If you open the attached example it should explain my requirements in an easier format.

Thanks

[TMS]

To be honest, you are doing yourself no favours at all. Add an extra column for the date of the course and then put all the attendance data on one sheet. You can make it a Structured Table so you can sort it and filter it to your heart's content. And, if you want more of an analysis than just a sorted/filtered list, you can create one or more Pivot Tables.

[oeldere]

Since I totally agree with TMS, I made a structered table for you on sheet 1.

See the attached file.

[fastcar]

Hi Guys,

Thanks for the reply.

I may have not explained myself correctly. I only want to have to complete the sheets with "Att" (attendance sheets) and then have it fill out the "MATER" for me with the date that person completed the course. I don't want to have to enter information in more than once.

It would go like this.

1. I place the people on the attendance sheet on the relevant day.
2. I put a Y or N on the attendance sheet for that day to show if they turned up or not
3. If its a "N" then nothing happens
4. If its a "Y" then the date that person took that course is placed automatically in "MASTER" Column "D"

The problem I see is the more attendance sheets I have the longer the formula gets.

Hope this explains my request better

[oeldere]

Yes I understand, but if you add all data in the table, I create for you, you only have to fill that one.

1) You want to add data just once, so that will meet to your criteria.

Then you want to analyse the data (the people who followed the course).
You have the problem you have to look in many worksheets.

2) If you have 1 worksheet with all data in it, you only have to analyse 1 worksheet, which is much easier.

You can use e.g. filter, or pivot table to analyse the data.

With all data on differant sheets, we need to collect all data in one sheet, before we can analyse the data.

3) So learn from your "mistake" and learn the profit of a table (in 1 sheet).


I made the same mistake also when I started learning Excel, so you don't need to feel guilty.

[fastcar]

Hi Oeldere,

The problem is I need to print out the attendance sheets for signing but I think I get your view.

Thanks

[oeldere]

See the sheet with filtered data (filtered on Yes).

[TMS]

If you use oeldere's workbook/table, you can filter on an individual and/or date and/or attended ... print, repeat.