Creating a cross sheet formula that would use "converted as a column formula" functionality.

mike.thorpe17421

I am trying to create a formula as follows that links to a cell in another sheet. The formula below works in one sheet which is adding 3 months onto the [Planned Local Regulatory Submission Date] and I have "converted as a column formula".

=IFERROR(IFERROR(DATE(YEAR([Planned Local Regulatory Submission Date]@row), MONTH([Planned Local Regulatory Submission Date]@row) + 3, DAY([Planned Local Regulatory Submission Date]@row)), DATE(YEAR([Planned Local Regulatory Submission Date]@row), MONTH([First Commercial Sales Date (Best Case)]@row), DAY([Planned Local Regulatory Submission Date]@row))), "")

What a want to do is create the same formula referencing another sheet {England SSOT Range 2} which is adding 3 months onto the cell for row 3 in the {England SSOT Range 2} sheet which works fine when looking for a specific individual cell.

=IFERROR(IFERROR(DATE(YEAR({England SSOT Range 2}), MONTH({England SSOT Range 2}) + 3, DAY({England SSOT Range 2})), DATE(YEAR({England SSOT Range 2}), MONTH({England SSOT Range 2}), DAY({England SSOT Range 2}))), "")

The only way I can do this for various rows is to create individual formulas for each individual cell. Does anyone know of a way to create the cell that converts as a column formula? If I do this for the cell that works the same value comes up in each cell. I have tried adding @row after {England SSOT Range 2} followed by closed brackets but this doesn't work.

Any help much appreciated.

Ipshita

Hello Mike,

I know of one way you can convert a column formula into a cell formula. So after you have created your formula and converted the column in to a formula column, select the particular cell where you have the formula and right click on it. There is an option at the very end that says, convert to cell formula. May be that can work in this case.

Try and let me know.

mike.thorpe17421

Hi Ipshita.

That works fine when the formula references a cell in the same sheet but as mentioned above. When you are referencing a different sheet, if you convert this cell to a column formula the same result appears in every cell. i.e. In the sample above {England SSOT Range 2}, the "Range 2" is a cell and not a column.

Thanks anyhow

Regards

Mike

mike.thorpe17421

Does anybody else out there know if this is possible?

Paul Newcome

Are you able to provide some screenshots for reference?

mike.thorpe17421

Thanks Paul

This is the cell I am trying to populate and create a "Convert to Cell Formula for the others". The source sheet is "England SSOT"

When using a single cell the formula works fine as the range is a single cell as follows. This is from the "England SSOT" sheet.

If I use "Convert to Cell Formula" it populate every cell with 30/12/24 which would be the formula for the cell detailed above. "6 months after 30/6/24.

Thanks for your help Paul.

Regards

Mike

Hollie Green

Is there a column you can reference that would have a unique value on the same row as your date that you are referencing. Smartsheets doesn't work like excel and automatically change to the next row down when converting to a column formula. You would need to reference the columns and match the data up. I typically use an Index/Collect or an Index/Match formula. There are advantages and disadvantages to this feature.

mike.thorpe17421

Thanks for your feedback Hollie. I created a new column [Reimbursement Dossier Submission Date England] that was a link to every row in the corresponding column on the other sheet. I then created the following formula that looked at the new column which had the right dates in it from the other sheet. When adding the formula to the linked date in this cell it came up with a blank value as the formula had the IFERROR formula, as follows. [Reimbursement Dossier Submission Date England] being the column name that it looking to add 6 months to this date.

=IFERROR(IFERROR(DATE(YEAR([Reimbursement Dossier Submission Date England]@row), MONTH([Reimbursement Dossier Submission Date England]@row) + 6, DAY([Reimbursement Dossier Submission Date England]@row)), DATE(YEAR([Reimbursement Dossier Submission Date England]@row), MONTH([Reimbursement Dossier Submission Date England]@row), DAY([Reimbursement Dossier Submission Date England]@row))), "")

You mention using an Index/Collect or an Index/Match formula. There are advantages and disadvantages to this feature. Can you provide an example of this?

Thanks again for your help on this, much appreciated.

mike.thorpe17421

Hi @Hollie Green Were you able to provide any samples of how to use the Index/Collect or an Index/Match formula for my problem?

Cheers

Mike

Hollie Green

Hi Mike haven't had access to the computer in a bit. Below is an example of an Index/Collect function. I use it most often.

=IFERROR(INDEX(COLLECT({Submission Date},{Unique Column on same sheet as submission date},reference unique@row on same sheet as formula),1)

What the formula should do is pull in your submission Date based on it matching the unique value. The 1 is needed to make sure it pulls in the submission date.