Line Item Transfer to Another Sheet using Formula's

I'm working on a project and am hoping for some advice from the community of experts!

I have a sheet that serves as an ongoing list of projects and information about those projects - once a decision is made to either fund the project or deny the project, I'd like that line item to appear in another sheet. My main sheet would be the ongoing list and then as decisions are made the line item would go to a "funded" sheet as a new line item or a "denied" sheet as a new line item.

Is there a way that I can do this?

Find more posts tagged with

Sheets

Healthcare

Formulas

Conditional Formatting

Project Management

Cell linking

Enhancement Request

Comments

L_123

You want zapier. Just use the free account. That is the easiest solution to this problem. Cross sheet lookups have a lot of issues. While you could build something of that nature using an autonumber column as an index, the main sheet would always have to hold all of the information, and if anything on the main sheet got messed up/deleted you would lose the information on the other sheets. Zapier will create independent rows very easily. If I post the URL the mods will have to approve my comment, so just google zapier and it should be the first result.

Jim Hook

Hi Kp,

Depending on what you plan to do on the funded and denied sheet you might be able to use two Smartsheet reports to view the lists. A report can't do any calculations but it would be simple to have an ongoing view of the funded and denied projects. The funded project list report would scan the main sheet and bring in only the rows for sheets that are funded. You can pick which columns of the main sheet to display, sort them, etc. Same for the denied projects. Reports can be shared with others as needed.

I don't know of any way to link conditionally to a second sheet.

Paleolinda

use the VLOOKUP function. It copies a cell from one sheet to another. It's under "insert" "cell linking"

Jim Hook

The problem with linking or simply using VLOOKUP is that without generating some "index" values on the main sheet the funded and denied sheets need to be as long as the main sheet and will have have a lot of gaps. I have used VLOOKUP with cross-sheet references to bring in a list of projects that meet certain criteria on another sheet but first I had to setup a column or two on the main sheet where it would identify each funded project row, for example, with a number that increased each time a project became funded. Then, VLOOKUP on the funded sheet would scan the entire column (not just a range of cells) on the main sheet to bring in the funded project data. Setting up the index values using formulas that reference entire columns allows the main sheet to be edited as needed or just go on forever. For example when funded projects close they can be removed from the main list, without having to do anything but change their status to something like "completed". Likewise denied projects can be removed from the funded sheet by changing their status to "archived" to keep the denied sheet from getting too long.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…