Editing one item in a text and number string if Sheet 2 is altered

Hi guys

I am working on a large worksheet where multiple sheets affect each other and I've hit a snag.

In one spreadsheet I have a series of unique numbers beginning with the letter 'E' that populate one of the columns. In sheet two I have two columns that are populated with numbers.

I want the letter 'E' to update to 'C' when cells in BOTH columns in sheet two are populated.



I've created a Test spreadsheet to demonstrate what I'm after and what I've done so far (image attached)

I don't necessarily want to use the SUBSTITUTE formula or really anything in columns L and M, it just seemed the closest formula to do what I need.

Thanks in advance for your help.

printscreenTEST.png

Hi Bo_Ry

Thanks for your help, but this isn't exactly what I'm after. What I want is for the 'E' in the unique number string to update to C, but in the formula supplied, the cell which the formula is inputted in returns 'C'

So what I want is for cell C2 to return C/01/0012 instead of E/01/0012 when Cell Sheet2!C2 and Sheet2!D2 are populated with anything greater than 0

Is there any way of doing this?

Hmm, it returns C/01/0012 but only in the cell I input the formula in, not in cell C2

If cells C2 and D2 = 0 then the number string still needs to read 'E', so cell C2 on sheet 1 should only read 'C' when the cells in Sheet 2 are populated

Is cell C2 a formula, or text? If C2 is text, and cannot be a formula, you will likely need to use VBA to alter that cell.

It might help to post a sample workbook, with example data and desired results. Click on Go Advanced, then Manage Attachments to upload a file.

Hi

Sorry if I haven't been clear enough

The cells in sheet 1 have to be populated with the unique numbers beginning with either E or C, they can't be formulas because they are raw data.

They need to respond to the cells in columns C and D in sheet 2 being populated, but it has to be both cells. If only one cell is populated, the data in sheet 1 shouldn't change.
It also needs to respond only to these two columns, as there will be a lot of data in sheet 2, and shouldn't respond if cells A and B in sheet 1 are populated either.

I've made a quick test spreadsheet which should give you an idea of how I want it to look. Sheet 1 has the raw data, with columns A and B being populated with other information.
Sheet 2 has the populated columns.


Test.xlsx

If you can use a formula in column D (or another column) in Sheet1 to provide the results you're looking for, this would work:

Please Login or Register  to view this content.

Put this in D2 (or another column in row 2) on Sheet 1, and then drag down. This checks to see if the columns C and D in Sheet2 are both populated. If they are, it removes the first character from the string in column C and replaces it with a "C". If both columns in Sheet 2 are not populated, it just returns the string in column C on Sheet1.

If you cannot use a formula in a column on Sheet1, and want the results displayed in column C on Sheet1, you'll need VBA to do it. Let us know if this is the case.