Using cell data to update part of a string on multiple cells

**Caitlin29** · 07-10-2015, 02:12 AM

Hi,

I'm trying to find a formula or macro to update multiple cells with the text in one cell.

I currently have about 70 sheets with varying number of tables on each sheet, all with different titles, but including the same month and year in the title ie a title might be "Population of children 0-12 years, by state or territory: July2015"

I want to be able to update one cell on the first sheet with a particular date, eg cell A1 will be "Reported Month" and cell A2 will be "July2015" and then this date will populate into each table title on all sheets. So when I change it each month, I only have to change it in one place.

Can anyone help me with this?

Much appreciated!
Caitlin

**Trebor76** · 07-10-2015, 02:47 AM

Hi Caitlin29,

Welcome to the forum!!

If you enter the date as a string, use this formula to put the date from cell A2 of Sheet1 into a your heading example above (change the sheet name to suit):

="Population of children 0-12 years, by state or territory: " & Sheet1!A2

If you enter the date as a date, use this formula to put the date from cell A2 of Sheet1 into a your heading example above (change the sheet name to suit):

="Population of children 0-12 years, by state or territory: " & TEXT(Sheet1!A2,"mmmmyyyy")

Regards,

Robert

**skywriter** · 07-10-2015, 02:47 AM

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.

ViewPic

**Caitlin29** · 07-12-2015, 06:31 PM

That worked, just had to put ' ' around the sheet name.

Thanks so much for your help Robert!

**Trebor76** · 07-12-2015, 07:33 PM

That worked, just had to put ' ' around the sheet name.

That's because you have a space in the sheet name you're referencing.

I'm glad we got it solved for you

Robert

Using cell data to update part of a string on multiple cells

LinkBack

Thread Tools

Rate This Thread

Display

Using cell data to update part of a string on multiple cells

Re: Using cell data to update part of a string on multiple cells

Re: Using cell data to update part of a string on multiple cells

Re: Using cell data to update part of a string on multiple cells

Re: Using cell data to update part of a string on multiple cells

Thread Information

Users Browsing this Thread

Similar Threads

Use TRIM function to strip part of a string, replace data in stripped cell with result. VB

Update Part description in multiple files

extract part of string with multiple separators

[SOLVED] Identifying part of a string with multiple cases

How to match if cell contains some part of the string from other cells in other column?

Bookmarks

Bookmarks

Posting Permissions