linking tabs on a spreadsheet anticipating adding lines and adding missing data

**dkilbourn** · 03-30-2012, 01:05 PM

Hi!
I am working so hard to create a spreadsheet of information for my organization that has complete information on grants that we have received or are anticipating receiving. One work sheet needs to be organized by Program and Foundation name - with columns that identify potential reporting deadlines. Not all foundations have the same number or type of deadline, so many of the cells in each row are blank.
I want to be able to link this to a simple spreadsheet organized by date of deadline. The master spreadsheet will constantly be changing - as grants change etc.
Can i create a system of links that draws data (date, program, foundation) - only when there is data - and adds it to the deadline spreadsheet?

I will attach a small sample of what I am talking about.

I know how to set up links between tabs for existing data - but what I am picturing is being able to tell it - "when E5 has data I want it linked to the last row of tab 2 - along with E1, and A2"

Possible?

Also I'm open to changing the format of these two spreadsheets. I'm new to this!

Thank you!!sample linked worksheet.xlsx

**Dennis7849** · 04-06-2012, 11:19 AM

What you are asking for is very common(at least in this forum).

The way most Excel databases work is;
1. On a sheet, you collect and manage records. These should be very inclusive(all related info on one line for each record.
2. Some columns and cell get calculated using worksheet functions.
3. The autofilter helps a great deal in filtering these records. It's a great tool.
4. Macros are used to do things like make reports. Like deadline reports.

**JBeaucaire** · 04-06-2012, 02:42 PM

I think you need to amend your sample workbook so your DEADLINES worksheet shows the information the way you want it to appear when automated, then highlight the cells you want to automate, clarify which cells are used to do the lookup. That might make it simpler to suggest formulaic answers.