Nested ISBLANK Formula

Cheryl Collins

Hi

I want to be able to look up changes to e.g. employee name, Job Title etc in a sheet and update the any changes on a staff sheet. However, if for example, their job title hasn't changed I need the formula to refer to 'old job title' column. I have a unique identifier of the Employee ID so, wondered if an INDEX MATCH would work with a nested ISBLANK to get it to refer to the 'old' column. Would this work and if so, could someone help with building the formula out? Or any alternative suggestions would be gratefully received.

Thanks

Cheryl

Paul Newcome

Honestly I would suggest inserting a new column on the source sheet that would house the [Current Department] and use a basic IF statement in that. Then reference this new column in your INDEX/MATCH when pulling it over to the new sheet.

=IF([Department Change?]@row = "Yes", [New Department]@row, [Old Department]@row)

It can be done without the helper columns on the source sheet, but it involves A LOT more complexity.

What is the purpose of pulling this data over into a new sheet?

Paul Newcome

Are you able to provide some screenshots with some sample data (manually entered) to show what you are currently working with and what your desired outcome is?

Cheryl Collins

Hi @Paul Newcome

I have a changes sheet that looks like this:

I then want to be able to update my management list with just the changes. As you can see I can't just get my management list to look up to the new e.g. department as if they haven't moved department it will be blank and therefore I need it to refer to the old department column.

Hopefully that makes more sense. This will be true of Team, Location, Job Title and Name change too.

Thanks

Cheryl

Paul Newcome

Honestly I would suggest inserting a new column on the source sheet that would house the [Current Department] and use a basic IF statement in that. Then reference this new column in your INDEX/MATCH when pulling it over to the new sheet.

=IF([Department Change?]@row = "Yes", [New Department]@row, [Old Department]@row)

It can be done without the helper columns on the source sheet, but it involves A LOT more complexity.

What is the purpose of pulling this data over into a new sheet?

Cheryl Collins

Hi @Paul Newcome

Thanks for this, I can work with that. I will just have to remember not to import but to copy and paste the original file data!

I thought about helper columns and did start down that path but, found myself getting into a tangle!

Thanks

Cheryl

Paul Newcome

happy to help. 👍️