Thread: Populate one spreadsheet from another

I am building a dashboard workbook for managing a business and want to automatically be able to move contracts from my "pipeline" spreadsheet to my "ongoing projects" spreadsheet (in the same workbook). Can I set up an ifs statement to say something like if the "status" column in the "pipeline" spreadsheet is equal to "completed", then add the value in the "Name" column to a row in the "ongoing projects" spreadsheet.

I would do this in Access but the "pipeline" spreadsheet pulls from Salesforce.com through their office integration widget that only works with Excel.

Is this possible to do in Excel?

Many thanks.

When you say completed do you mean just has text entered?

A simple way but probably not the best is =if('pipeline'!A1="","",'pipeline'!A1)
That will only show information if the cell it is copying from is non-blank.

I meant if it equals a specific value but I suppose I could add in an extra column in the "pipeline" sheet of blank and non blank cells to be populated based on the "Status" column.

I'm going to give it a shot, thanks.

The solution works except I have to code in the IF statement for every row in the "ongoing projects" sheet and then there are a lot of blank rows where the IF statement is false. So it looks like:

Project
AAA

CCC
DDD


GGG

Is there a way I can construct the sheet so it only gives me the true values? Like someway to create a table with the IF statement so it only returns the true values? (Like a query, I suppose).
Thanks.

Spreadsheet attached; it's just a basic sandbox right now.

It will be dynamic with the SFDC plug in but the input doesn't matter for right now, I can format the report that Salesforce outputs.

You see in the second sheet that I can just code an IF statement for each line but that's pretty ugly. I was hoping to create some kind of table that would let me condense all the ongoing project entries so it looks like:

Projects
AAA
CCC
GGG
FFF

Does that make sense?
Thanks.

I used the filter to sort out the blank values. Thanks for the suggestion.

I should just format a report to come from Salesforce.com that returns only completed projects. Damnit.

I appreciate the help, man. Looks much better now.

For archive purposes and anyone else interested in solving this problem:

I had to update the filter every time to add new projects from one sheet to the other. I set up a Macro to update the filter in the "ongoing projects" sheet and then a couple lines of VBA to run the code when the workbook opens.

For archive purposes and anyone else interested in solving this problem:

I had to update the filter every time to add new projects from one sheet to the other. I set up a Macro to update the filter in the "ongoing projects" sheet and then a couple lines of VBA to run the code when the workbook opens.

I also assigned the macros to pull from Salesforce.com and update the filter to a shape on the dashboard labeled "update".

Does salesforce.com change much? could you do a web query would that be simpler? Then manipulate the imported data.