Another Excel query

mksysb

Gers wrote: »

Perfect! Thanks so much, I thought that I'd have to password the spreadsheet / worksheets so am very happy.

Yes, I don't think anyone would make changes maliciously. I'm doing this for volunteers to claim mileage expenses, most are on the older side and used to filling in paper claims. We are trying to make life easier for both who have to submit claims and those who have to process them.

Huge thanks.

Are you expecting all the volunteers to go out and buy excel just to claim back mileage? Why not do it in LibreOffice which everyone can get for free?

Gers

mksysb wrote: »

Are you expecting all the volunteers to go out and buy excel just to claim back mileage? Why not do it in LibreOffice which everyone can get for free?

Very strange question! Why would you think I have such expectations?

mksysb

Gers wrote: »

Very strange question! Why would you think I have such expectations?

Because you are writing a spreadsheet for other people to use.

Gers

mksysb wrote: »

Because you are writing a spreadsheet for other people to use.

You are ascribing expectations to me without any basis.

Heedtheadvice

Well said Gers.
I had been thinking along similar lines about that assumption.
It may well have been true but there was nothing to indicate as such!


Given your described use of the workbook can I point you out to a possible alternative way of presenting your spreadsheet to your users?


I am assuming here that there is one sheet (or a sheet per volunteer) to be completed with information (data entry) and that the remaining sheets are for summary benefit (your analysis)?


If so then the sheets can be split into two (or more) workbooks. The volunteers have access to a workbook for data entry and the other workbook is for your sheets '2' and '3' ( those that have the formula in their cells C2). There is no reason that these sheets cannot link instead of to their own sheet '1' they link to the volunteers workbook(s) sheet'1'.



That way you divorce the entry and 'calculation' workbooks reducing risk of your calculation sheets becoming corrupted, intentionally or otherwise or deletion. You could even fully protect that latter sheet by limiting users access and adding your own password.


Linking across workbooks is similarly relatively easy as linking between sheets of the same workbook.
Assume they are called volunteer.xlsx and myworkbook.xlsx respectively.


Open both workbooks. Volunteer workbook has named range (cell c2) called 'personsname'.

In myworkbook in cell c2 type "=" and then switch to volunteer workbook and select cell c2 and hit return (or switch and hit f5 to select by names allocated to cells as previously described). Once return has been pressed myworkbook cell C2 shows the contents of volunteer workbook cell and the formula show which workbook that the data comes from i.e. =volunteer.xlsx!personsname.


You could thus, if so desired have separate workbooks for each volunteer.

Gers

What a lovely and fulsome answer Heedtheadvice, many thanks.

Your idea is very clever and I may well adopt it at a later date. For now the volunteer spreadsheet has four workbooks as some do many many journeys. They will be exporting their own worksheets to a pdf and emailing to another person.

The 'other' person is not at all computer savvy and prefers to deal with paper. As she will be resigning in the next 14 months it won't be good to force any change on her. The volunteers are a different kettle of fish, many are well able to access and use such spreadsheets and will be given some one-to-one tutition on using it if needed.

I don't appreciate passive-aggressive posts which question my intergrity, especially from someone who did not offer any solution to my query and was just being nosey (best view) or a clever d ick (worst view).

To those who responded with really good help - many thanks!