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

Set contents of two columns into 1
Hi - trying to figure out a formula that combines two columns into one long column, maintaining contents in multiple rows. Noting that the number of items in each column will change as these are each using the index/distinct to summarize a separate sheet. For example: Thanks for any help!
Unparsable formula; Sheet summary SUMIF formula
Can anyone help why this formula is coming up as unparseable? I'm creating a sheet summary, where it totals the Estimated GP (Est. GP) column if the Status column has the "Quoted" drop-down option is selected. Formula i've used: =SUMIF(Status:Status,"Quoted",Est. GP:Est. GP) Thanks a lot!
INDEX/MATCH Most Recent Entry
I am trying to pull from a sheet that is essentially a list of new records that are entered weekly. To keep things clean I have a helper sheet that just lists the project name once and the rest of the columns pull in through an INDEX/MATCH. I am trying to have that helper sheet pull in only the most recent entry. There is…