[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Excel Variable Row Number

Posted on 2012-09-12
Medium Priority
526 Views
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)
0
• 3

LVL 7

Assisted Solution

DIIRE earned 450 total points
ID: 38393395
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.
0

LVL 1

Author Comment

ID: 38393426
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
etc

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

=VLOOKUP("1SunA",Sheet1!A5:D100,X)

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
0

LVL 1

Accepted Solution

ID: 38393516
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)

0

LVL 1

Author Closing Comment

ID: 38404815
I figured it out on my own. No additional information is needed.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
###### Suggested Courses
Course of the Month19 days, 1 hour left to enroll