Make a link reference in a sheet to data in a report?

Angela Hollingsworth

Hello,

I want to link data from a report (a grouped sum amount) into a sheet with a reference link and/or add it to a formula. How can I pull data from reports to use in sheet formulas and/or rows/columns?

Thank you,

Angela

Scott Orsey

Hi. Sorry. This can't be done. Reports are just "views" and you can't use their values in a formula.

Instead, you'll need to reference the data in the underlying sheet. You can replicate the concept of summing up over groups by using the SUM formula in combination with the COLLECT function.

I hope this helps.

Be well

Angela Hollingsworth

Yes, it is a super bummer as I am working around not having "data shuttle" and needing major manual edits to my original data source that must be updated monthly.

We are still too small to be able to afford the data shuttle just yet - maybe in the future!

I will keep trying to figure out a workaround.

Also - trying to figure out a formula that will search data columns (name, expense) via a reference link to another sheet that will sum the total expenses by name and then be divided by another reference source link amount.

So far I can get a total on the column from the expense column from other sheet but I keep getting nonparse or invalid when I try to add more steps. Currently this only works to total all expenses =SUM({5330416-20240318-104443 Range 3}

This is my last attempt at adding steps to narrow by name: =SUMIF({5330416-20240318-104443 Range 3}, AND{5330416-20240318-104443 Range 4}, @cell = PERSONNEL@row)

Range 3 is expense column, range 4 is the Name column

Angela