Excel Variable Row Number

Posted on 2012-09-12
Last Modified: 2012-09-17
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)
Question by:RADCOMP
    LVL 7

    Assisted Solution

    Do you have multiple tasks ever assigned to the same time slot?

    If not you can use vlookup to populate the calendar.  E.G. for cell 1SunA on your calendar, you put in a vlookup to up 1SunA in sheet1 column D and if it finds it to put in value from column A.

    If there could be multiple tasks for the same timeslot you will need VBA.
    LVL 1

    Author Comment

    For simplicity I figured it would be easiest to only have one task per cell

    Right now the rows look like this:

    Week 1 A
    Week 1 B
    Week 1 C
    Week 1 D
    Week 2 A

    I figure I can add EFGHIJK as needed, if needed.
    I'll look into the syntax of vlookup, hopefully this is exactly what I'm looking for!
    Ok I looked it up, this seems to be 90% of what I need but how do I make the index number equal to the row number of the cell that contains "1SunA"?

    So far I have this


    I can't put a number in for X because it changes based on which row 1SunA was found on, this is pretty much where I was stuck before.

    (Maybe I don't fully understand VLOOKUP, I'm using this as reference:
    If I try it like this

    =VLOOKUP("1SunA",Sheet1!D,1) it returns #NAME?

    =VLOOKUP("1SunA",Sheet1!D1:100,1) returns #N/A

    Ah, I've gotten much closer, now my code looks like this:

    =IF(VLOOKUP("1SunA",Sheet1!D1:D100,1,FALSE) = "1SunA",X,FALSE)

    I need to get X to be equal to Sheet1!A&ROW(), ROW being the row number that 1SunA belongs to
    LVL 1

    Accepted Solution

    Posting a new comment because I solved it on my own.

    Thanks a bunch for the VLOOKUP direction it really helped.

    My final formula looks like this (And works)

    =IF(VLOOKUP("1SunA",Sheet1!$D$1:$D$100,1,FALSE) = "1SunA",INDIRECT("Sheet1!$A$" & MATCH("1SunA",Sheet1!$D$1:$D$100,-1)),FALSE)

    Woo, glad that's figured out!
    LVL 1

    Author Closing Comment

    I figured it out on my own. No additional information is needed.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now