Create sheets from master sheet, by formulae

[welshgasman]

Hi all,

I am trying to bring the local Community Car Scheme where I volunteer as a driver, into the 21st century. At the moment everything is done on paper.

I have convinced the controller to try an Excel sheet to start with.

That was the reason for my question at Colour text depending on offset value as they want a paper version coloured as they do now.

Now I am think of ease of use, as they controller is not that conversant with computers.

I am thinking of a Master sheet as shown in that linked thread.
However I want to break it down to the first letter of the surname, and have sheets for A, B, C, D etc.
This is to emulate the address book they use at the moment.

Is there a way using formulae to create the character index sheets from the master sheet, or create the master sheet from the character index sheets.

Else I will have to try and create some VBA.

TIA

 

[MCLIFTO8]

PQ
PQ is the optimal solution. I believe you can create queries based on the master sheet that fit your criteria. However PQ (Power Query) is available in Excel 2013 onwards but your signature indicates you're using excel 2007.

Formulas
There's no way to make the sheet objects using formulas but if you were to make the sheets first (all 26 of them), then fill the sheet with the formulas you need to lookup the master sheet values you could use it as a reference sheet. This solution is very convoluted and purely to humour the idea.

To do that you will likely need an array formula to get the nth matching string from the master sheet and propogate the formula down your rows where n = row number.

I took a formula from ExcelJet but it needs a few modifications.

1. You need "vals" which is supposed to be an array of your surnames to be an array of the first letter of your surnames.
You do that with LEFT(surname, 1).

2. You need to account for the nth returned name so you get a unique name per row. You can do that with Row()-1, assuming you have 1 header row, modify this accordingly if you don't.

So starting with {=INDEX(return_array,SMALL(IF(surnames="firstl_letter",ROW(vals)-ROW(INDEX(surnames,1,1))+1),nth_match))}

we end up with

=INDEX(return_range,SMALL(IF(LEFT(surname_range,1)="surname_first_letter",ROW(A2:A8)-ROW(INDEX(surname_range,1,1))+1),ROW()-1))

remember that array formulas are enabled with ctrl-shift-enter.
Then drag down the formula.

I'd then use INDEX,MATCH or XLOOKUP for the subsequent columns if you need data referenced. You don't want this convoluted, complicated mess propagated over the entire sheet.

VBA
Lacking PQ I reckon just populate your names and data using VBA. Then if you need to modify 26 sheets you can make the modifications once in code.

 

[welshgasman]

Hi @*MCLIFTO8

Thank you for the reply.
We only have 2007, so no PQ.

A lot of that went over my head I must admit.
I'll work out a VBA way somehow, probably going from one sheet, to multiple sheets, one for each first character of the surname.
Still need to talk to the controller about data entry, I do know however that he wants one sheet for each letter.