#ref error when updating P&L multiple periods

Hi there, just new to DataDear. I’ve ran multiple period P&L which then, through some mapping, links to a summary P&L using a sumif formula. When I go to refresh and/or add a new period all my sumif formulas turn to #ref’s. Does anyone know a work around for this?

Thanks,

Phil

Hi @philipk5510,

Welcome to the DataDear community and thank you for using DataDear.

Can you clarify whether you are you using the Profit and Loss - Multiple Periods or the Profit and Loss - Multiple Periods {new} report? Can you also clarify if you are using the Periods as rows functionality?

Hi David,

Thanks for the reply. I am using the P&L - Multiple Periods (new) and am not using the periods as rows functionality.

Thanks,

Philip

Hi @philipk5510

I would suggest using the Period as rows as this should resolve your problems related to loosing the formulas. If you still have problems, I suggest you attach the file (you may remove any sensitive information) to this thread so we can have a look.

Hi Neville,

I got this sorted with a work around in excel. One other query though. When you run the P&L - Multiple Periods (new) without periods as rows there is a space at the end of the month column. So for example my period column heading has: ACTUAL MTD 2020-07-31 and after the 31 when i press F2 there is a space after the 31? Any reason why this is happening? Hope that makes sense.

Hi @philipk5510
Yes DataDear adds that space at the end of the column name so best to ignore it when building your reports.

Hi there,

Thanks for the response. Is there any way DataDear can get rid of this or is it required from your side?

Thanks,

Hi @philipk5510

Yes that is used by DataDear during the refresh cycle and hence cannot be changed. Is it impacting the report you are building?

Hi,

It is slightly yes. Excel formulas won’t work if there is the space at the end string of text as it’s essentially adding an unnecessary character count. Is there a possibility of you guys removing this?

Thanks,

Hi @philipk5510

I believe this can be solved with a simple LEFT formula. Taking your example above, the header you want amended is "ACTUAL MTD 2020-07-31 " and from this you need all characters except for the space at the end. So you have 22 character in total and need the first 21. Lets assume that this header is in Cell A9. You can type in =LEFT(A9,21) this will return the header you need without the space at the end.

Hope this solves your problem. If this is unclear please feel free to let us know.

1 Like

Hi there,

Yes, this will work thanks,

Phil

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.