Generate PDF with data from multiple sheets?

JPJPJP

I'm trying to create a project management system in Smartsheet, that has the ability to generate Invoice/Estimate PDFs. I already know how to generate PDFs with a single row, but is it possible to generate a PDF with data from multiple sheets?

I would need to get data from the parent "Projects" sheet (for client name, project info), the connected "Deliverables" sheet (the individual service items), and the "Rate Card" sheet (to get the pricing).

Can this be done with document generation feature?

jmyzk_cloudsmart_jp

Hi @JPJPJP

You can create PDFs with data from multiple sheets, but you must combine the data on a single sheet with formulas.

Project Sheet > PDF Sheet

Example:

[Project Name]=INDEX({Project Name}, MATCH([Project ID]@row, {Project ID}, 0))

[Client Name] =INDEX({Client Name}, MATCH([Project ID]@row, {Project ID}, 0))

Deliverable Sheet> PDF Sheet

You will typically have multiple deliverables or service items. In that case, you must create separate columns for those and use formulas like the following to get the data from the deliverable Sheet.

Example:

[Deliverable Name 1] =IFERROR(INDEX(COLLECT({Deliverable Name}, {Deliverable Sheet Project ID}, [Project ID]@row), 1), "")

[Deliverable Name 2] = =IFERROR(INDEX(COLLECT({Deliverable Name}, {Deliverable Sheet Project ID}, [Project ID]@row), 2), "")

PDF Data Sheet

https://app.smartsheet.com/b/publish?EQBCT=89771f4d84af4d9cb494ed6109ab8063

Project Sheet

Deliverable Sheet (Get Rate Data from Rate Sheet)

Rate Sheet

JPJPJP

https://community.smartsheet.com/discussion/comment/447326#Comment_447326

Thanks so much for this detailed reply! I'll give this a try. But question - I noticed you put all the deliverables as individual columns (up to 3), but that would mean with this solution there's a limit on how many deliverables a user can submit? We have projects that have deliverables as big as 50 deliverables sometimes. So I don't think putting them as individual columns works since we can't have restrictions on the number of deliverables?

jmyzk_cloudsmart_jp

Hi @JPJPJP

A fillable PDF invoice typically provides space for up to 10 items and requires their total entry.
However, configuring a PDF with numerous fields becomes highly time-consuming* when managing an invoice for 50 deliverables, each containing details such as name, description, assignee, rate, and amount. I recommend creating a separate 'Deliverable Report' or 'Deliverable Details' PDF using Smartsheet's standard print feature in a situation like yours.

(*It is possible to put more than 50 deliverables information in individual columns in a row as the maximum allowed column number of Smartsheet is 400. )

The revised demo solution would then pull the total amount from the deliverable sheet into the Projects Sheet and utilize the generate PDF feature to create an invoice. Additionally, it would produce a separate 'Deliverable Report' from either the deliverable sheet itself or a report derived from the sheet.

Project Sheets < Total Amount from Deliverable Sheet

https://app.smartsheet.com/b/publish?EQBCT=c53e2a5ac2504687800a7e7af4e21a5e

Deliverable Sheet < Rate from Rate Sheet, Project Name from Projects Sheet

https://app.smartsheet.com/b/publish?EQBCT=6c2caabe5daa4469b0c820b8a08f25d9

Deliverable Report created from a Report