- last edited on
I am working with a data set that looks at the funding sources at an organization. There are multiple sheets, and I need data from the main sheet to automatically go to certain sheets based on set criteria. I think combining the VLOOKUP and IF functions matches what I'm trying to do, but I keep receiving all sorts of errors.
I am attaching a sample datasheet to show the variables I am working with. It is a bit of a confusing task, so I've outlined exactly what I need to do:
There is an "All Proposals" work sheet. In it, we document funding from internal and external sources. We also document whether the proposal received funding or not. These are the two key variables that we have separate work sheets for. The work sheets are:
In these separate worksheets, I need a formula for each cell so that the entire proposal row will be pulled in IF the funding source is (internal or external for the respective work sheet) and IF funding was received (only for the funded work sheets). This row includes other information about the proposals (such as who is running the research study, when the project will be taking place, who exactly is funding it, etc).
I would appreciate the help!
03-21-2018 11:04 PM
You can try this.
(for all external proposals submitted)
=IF('All Proposals'!$B2="External",HLOOKUP('External Proposals'!B$1,'All Proposals'!$B$1:$S$10,$A2,0),"")
copy the formula in all cells
(only external proposals that received funding)
=IF('All Proposals'!$B2="External",IF('All Proposals'!$C2="yes",HLOOKUP('External Funded'!B$1,'All Proposals'!$B$1:$S$10,$A2,0),""))
copy the formula in all cells
Just replace the word"External" with "Internal".
03-21-2018 11:06 PM
03-23-2018 12:25 PM
Thank you for the response! I copied the formula you provided, and adjusted it in the "External Funded" spreadsheet. It returned an empty cell, though, and I'm not sure why. I extended the formula into the entire first row and nothing came up.
Here what the actual spread sheet looks like, and the formula I used. Maybe I referenced the wrong cells?
03-23-2018 12:56 PM
Looking at what you are doing I wonder why you want to have 5 separate worksheets when everything you want is already on one. It would be simpler just to use filters on your "All Proposals" worksheet. By choosing the appropriate filters on the TypeofFunding and Funded columns you get a sheet with exactly what you want.
03-28-2018 01:17 PM
We previously made these separate sheets because we regularly report these lists of grants and their amounts, and it is more convenient to have them as separate sheets for this purpose.
We also have graphs that are linked to these sheets that show the total requested and total funded amounts. Even so, it might be less tedious to have a "Total" cell for each condition on the main spreadsheet and then link this entry to the graphs. If there is no direct reference formula for entire rows on separate sheets, it might be easier to do it this way.
04-17-2018 05:25 AM