Another Excel query

Gers

I'm compiling a spreadsheet which has four worksheets in it which contain formulas for mathematical functions (simple ones!)

For one of cells on worksheet one (C2) I want to have the text (someone's name) automatically added to the same cell on the next three worksheets.

What function do I select for this please? Is it possible?

Many thanks

davidwatts

I'd just type = in C2 on sheet 2, click on cell C2 on sheet 1 then click the tick symbol next to the formula bar. Then the contents of the former cell will = the contents of the latter cell. Repeat till done on each sheet.

Gers

[STRIKE][/STRIKE]

davidwatts wrote: »

I'd just type = in C2 on sheet 2, click on cell C2 on sheet 1 then click the tick symbol next to the formula bar. Then the contents of the former cell will = the contents of the latter cell. Repeat till done on each sheet.

[STRIKE]
Haven't got a green tick - am using Excel 2016. I was so pleased to get your reply (still am) even though it's not working for me.[/STRIKE]

Found the tick, wasn't green, but it only sort of worked. It put the formula in rather than the text

='Page 1'!C2:G2

Sorted it - had to change the format of the cell from text to general! Thanks so much for pointing in the correct direction.

Heedtheadvice

I assume that is a typo? ='Page1'!C2:G2 ?...... Rather than ='Page1'!C2 i.e. The cell not the range?


It is also a good idea to give sheet page1 cell c2 a name. It makes the cells in the other sheets easy to fill in and shows a descriptive name in the formula rather than just a cell reference.



Say you call cell c2 on sheet 1 "personsname" then
For each cell you want to show the same name just type = then press f5 you get a list of the cells you have named (there might be name2, name3 etc in other cells) select the one you want press return (or OK) and return again. Done.



Sheet2 cell c2 formula is then =personsname, as are the equivalent cells on any other sheets that you 'point' to c2 with the same method.



A lot quicker to do than to type how to do it!!


Many a way to skin a cat in Excel...just pick the most useful!

Gers

Heedtheadvice wrote: »

I assume that is a typo? ='Page1'!C2:G2 ?...... Rather than ='Page1'!C2 i.e. The cell not the range?

No, that was a direct cut and paste from the worksheet. Once I changed the formatting of the cells everything worked.

Thanks for the alternative method, I didn't know that cells could be named so will explore that.

DoaM

As said, C2:G2 will look at the range of cells (C2, D2, E2, F2, G2). If you only want the value of C2 from Page1 then drop the :G2

(Just because it may currently work, doesn't mean it is right).

rmg1

It may have automatically come up with C2:G2 if the cells are merged.
I'm not saying they are, but that's the only thing I can think of if the OP is simply clicking on the relevant cell.

Gers

rmg1 wrote: »

It may have automatically come up with C2:G2 if the cells are merged.
I'm not saying they are, but that's the only thing I can think of if the OP is simply clicking on the relevant cell.

Yes, they were merged cells. I've unmerged them and made one cell bigger instead.

Think it's all working well now. I'm still trying to find a way to made the cells with formulae in uneditable without password protecting the spreadsheet. Most of the people who will use this are liable to just 'delete' info and thus lose the functionability.

NaughtiusMaximus

Gers wrote: »

Think it's all working well now. I'm still trying to find a way to made the cells with formulae in uneditable without password protecting the spreadsheet. Most of the people who will use this are liable to just 'delete' info and thus lose the functionability.

You can choose to protect some cells in the worksheet but not all:

1 - highlight all cells you want the users to be able to change>right click>format cells>untick locked
2 - review>protect sheet

The users should then only be able to change those cells you selected in step 1.

If your only concern is people who don't know excel accidentally overwriting formulas as as opposed to maliciously changing them, you can protect the sheet without setting a password. The users can still unprotect the sheet but if they only have very basic excel skills as your post suggests, they probably won't know how to.

Gers

NaughtiusMaximus wrote: »

You can choose to protect some cells in the worksheet but not all:

1 - highlight all cells you want the users to be able to change>right click>format cells>untick locked
2 - review>protect sheet

The users should then only be able to change those cells you selected in step 1.

If your only concern is people who don't know excel accidentally overwriting formulas as as opposed to maliciously changing them, you can protect the sheet without setting a password. The users can still unprotect the sheet but if they only have very basic excel skills as your post suggests, they probably won't know how to.

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.

NaughtiusMaximus

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.

No problem.

I do this all the time at work on the assumption that anyone who has sufficent excel skills to know how to unprotect a sheet will also know not to overwrite formulas. The assumption hasn't failed yet.