Text Formula

B

Barbara

I have a workbook with multiple worksheets. I want to enter a formula in
certain cells that will automatically put whatever text I put in a certain
box into all the worksheets. For instance, if I type in the Agency Name, I
want it to go into that cell in each worksheet. Can anyone tell me how to do
that?

Thanks
 
A

akphidelt

Hilight all the sheets holding down the SHIFT key.
Then type in the formula where you want it... and then click on another
sheet to undo the shift action.
 
B

Barbara

Thank you, but what do I use for the formula?

akphidelt said:
Hilight all the sheets holding down the SHIFT key.
Then type in the formula where you want it... and then click on another
sheet to undo the shift action.
Click to expand...
 
A

akphidelt

Say you are on Sheet1 and the Word is in cell B1
After holding down the SHIFT key and selecting all the sheets

Type in the formula = Sheet1$B$1
Hit Enter

Then to select a sheet hold down the CTRL key and select a sheet to take off
the sheet grouping.
 
S

Shane Devenshire

Hi,

When you say "put in a certain box" what do you mean by box? Is this a cell
in the spreadsheet or an input box on a user form in VBA, or do you have a
Control Toolbox "text box" in your spreadsheet, or do you have a Form "Label"
in your spreadsheet?
 
A

Alojz

Do almost the same: highlight all sheets, then insert the formula in last
sheet, for instance in B1: =Sheet1!A1, hit enter, click on some other sheet
to undo shift action.
In all sheets, B1 cell will display the content of A1 cell from Sheet1. The
trick is, u have to start inserting on the different sheet when referencing
the given cell together with link to proper sheet while clicking on cell u
want to be referenced.
 
A

akphidelt

That is not true at all. You can do all this on sheet one... just reference
the sheet to itself.
 
B

Barbara

I mean cell (not box - sorry!).

Shane Devenshire said:
Hi,

When you say "put in a certain box" what do you mean by box? Is this a cell
in the spreadsheet or an input box on a user form in VBA, or do you have a
Control Toolbox "text box" in your spreadsheet, or do you have a Form "Label"
in your spreadsheet?

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire
Click to expand...
 
A

Alojz

U are correct, unless u prefer combination of typing and clicking. I prefer
clicking when referencing to avoid mistyping.
 
A

Alojz

Let me clarify further. I prefer going to other sheet, then insert equal sign
(or plus sign instead equal) then click on tab Sheet1 and choose cell A1. I
got the sheet part of reference automatically. Sure, I can start in Sheet1,
too. In B2 I insert equal sign (or plus sign) then click on other sheet tab,
than back on tab Sheet1 and then cell A1. Same result, more clicks :)
 
B

Barbara

Okay, I finally figured it out using your help and one more step. I did it
sheet by sheet instead of holding down ctrl - never could make that work.
Then the formula to keep the text is: =T('name of the worksheet'!D2:K2)
That worked. Thank you for all of your fast responses!
 
S

Shane Devenshire

Hi Barbara,

After all that, I don't understand what your formula is doing. I'm not sure
why you are referencing a range. The formula you show only return a single
value for viewing purposes so it might be better to use =T(D2) and just copy
that to the right.

Now lets go back to the question of how you enter that formula in cell A1 of
all the sheets. You really should learn these basic techniques, they are
very useful.

1. Select all the sheets you want the formula in. You can do that two ways
- if the sheet tabs are all adjacent you can use the Shift key, if the sheets
are not all adjacent you can use the Ctrl key.

a. For the Shift method click the sheet tab of the first sheet you want
formulas in. Hold down the Shift key and click the sheet tab of the last
sheet you want formulas in. Start type your formula such as =T( then
click on the sheet tab of the sheet where the cell (box) you want to
reference is and click that cell (box) and press Enter.

b. For the Ctrl method click the first sheet tab you want to have the
formula in. Hold down the Ctrl key and click, one at a time, all the other
sheets you want to include. Start type your formula such as =T( then click
on the sheet tab of the sheet where the cell (box) you want to reference is
and click that cell (box), D2, and press Enter.

You could type the whole formula once you have the sheets selected, rather
than pointing and clicking the sheet tab and then the cell (box). If the
cell (box) is in Sheet1 then the formula would be =T(Sheet1!D2)
 
Top