Excel Variable Row Number
Posted on 2012-09-12
So I'm fairly new to Excel, at least in regards to doing complex formulas so I'm not even really sure how to ask this question. I'm just going to state my problem and desired solution, hopefully someone out there can point me in the right direction cuz this is giving me a royal headache.
My problem is this, I need to make an interactive calendar of sorts to help with scheduling various tasks. What I want do is put a schedule code onto a cell on Sheet1 (Eg 1MonA would be the first Monday of the month, time slot A) and then have Excel add the name of that task to my "calendar" on Sheet2.
Sheet1 only has two important columns I'm worried about, A and D which is Name and Code respectively.
Sheet2 contains a simple grid where Sun-Saturday goes across Columns B-H and the week number (And time slots) go through Rows 3-22.
So, I put my date code into any given cell on Column Sheet1!D, Sheet2 then would then add the name of the event (Column Sheet1!A of the same row number as the data that was just entered into C) to the appropriate slot on my calendar.
Example: A5 = Maintenance, D5 = 1SunA
I would like for the name found in Sheet1!A5 to be put into the proper cell on the calender, in this case it happens to be Sheet2!B3. (The grid never changes, only one task per cell) It is my hopes that if I fill the calendar with tasks I can simply add a time slot E, update the code for those rows and bam I just expanded the max number of tasks. (Currently with Week 1-5 and Slot A-D I get a max of 140 unique tasks)
Final product: Sheet2!B3 = Sheet1!A5 (Maintenance)
The task list is in alphabetical order on Sheet1 (Important for my organization) so as I add or remove tasks the row number will change.
I assume this should be possible to do using only formulas but I'm baffled at what combination of IFs ANDs INDIRECTs etc I need to use. This seems like it would be much easier in VBS but I don't have time to teach myself yet another scripting language. (Not that I'm proficient in any one language either.)
I've given myself another headache just trying to word this out for you guys so please bare with me if I goofed up some of my logic. I've got no problem giving more information or even sharing a test spreadsheet with someone if needed. (Might make understanding the whole big picture a bit easier)