• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 533
  • Last Modified:

Excel Variable Row Number

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)
  • 3
2 Solutions
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.
RADCOMPAuthor Commented:
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: http://www.techonthenet.com/excel/formulas/vlookup.php)
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
RADCOMPAuthor Commented:
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!
RADCOMPAuthor Commented:
I figured it out on my own. No additional information is needed.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now