Solved Help in identifying row number making it part of a formula

Microsoft Excel for mac 2011 - macintosh
March 18, 2020 at 18:00:13
Specs: Mac OSX 10.11.6
		A	B	C	D	       E	             F
1			        8		[Rough Start 08] - 	Rough Start
2						
3						
4			        6		[Blacktop 06] - 	Blacktop


If I have a number "6" under column C in row 4, and text "Blacktop" under column F. The formula under column E will automatically join "Blacktop" and "6" together as shown. How can I make the formula under column E to automatically extract the row number into the formula instead of having to enter the proper row number into the formula manually. I used to have place the formula in row 1 under column E, and then copied and pasted to other rows that needed it. But now, it's more likely I would need to place this formula on any rows. So with the formula such as "="["&F$1& " 0"&C1&"] - " doesn't work very well anymore. That's why I'm hoping to be able to fix the formula so it can be placed on any row under column E, and it will pick up the row number instead of using "1" in the formula. I know how to get the row number of a cell, but not knowing how to make that part of an input to a formula. Any help will be greatly appreciated.

message edited by uhjb


See More: Help in identifying row number making it part of a formula

Reply ↓  Report •

✔ Best Answer
March 21, 2020 at 18:37:28
Put this in E3 and drag it down. The Helper Column is no longer needed.

=IF(C3<>"","["&IFERROR($F3&LOOKUP(2,1/N($F$3:$F3<>""&F3),$F$3:$F3),$F3)&" 0"&C3&"] - ","")

I won't take credit for the LOOKUP portion of this formula, but that is the section that allows it to be used within Column E without the Helper Column. I think I understand how it works, but not well enough to explain it.

One warning: Based on what I was told by the person who helped me with it, it may slow down your worksheet. There is some array stuff going on inside the LOOKUP function which can slow things down.


message edited by DerbyDad03



#1
March 19, 2020 at 03:52:31
I can't test this right now but try this:

=INDIRECT("C"&ROW())

The ROW function will return the Row number of the cell in which it is used.

The INDIRECT function will turn a text string into a cell reference.

I have a question though...

Why are you locking the row of the F1 reference with a $? Isn't that what is preventing you from copying the formula from cell to cell?

Remove the $ and copy the formula directly from a cell, not from the formula bar and it will pick up the new row automatically.

Am I missing something?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#2
March 19, 2020 at 09:02:35
Thank you so much for your rapid response to my request. The second half of the formula (to do with column C) works perfectly. As for the first half to do with column F, I do need it to lock with a $. I'm not sure how to incorporate the INDIRECT into the first half of the new formula…

="["&F$3&" 0"&(INDIRECT("C"&ROW())&"] - ")
Not sure how to add the $ to go with the INDIRECT etc. to replace F$3 in the first part of the formula.

To explain what I'm trying to do here:
I'm trying to organize my ebook collection. For example when I have 20 books written by the same author. I would put all the sorted titles under column B, and then the series number under C (at this time, it should be all random and not filling out every row if it's not part of the same series). I would enter the name of the series under column F where the first row that has an input in column C before entering the formula onto the same row under column E. Once it's done, I would copy and paste the formula to any other rows that's has a number in column C to fill out the rest of the cells. When finish, I would have all the books from the same series with the series title and series number under column E. I hope I've explained that good enough. By locking column F would save time and only have to enter that once.


Reply ↓  Report •

#3
March 19, 2020 at 15:58:45
Something's not making sense here.

You said: "As for the first half to do with column F, I do need it to lock with a $" and "By locking column F would save time and only have to enter that once."

However, in your formula, you used: F$1

That locks the Row, not the Column.

Maybe that's your whole problem. If you want to lock Column F and let the Row change based on where you copy the formula to, you need to use $F1, not F$1.

That said, if you are always putting the formula in Column E and always referencing Column F, then you don't even have to lock Column F.

The F, if not locked with the $ (relative reference) will only change if you copy the formula from Column E and paste it in e.g. Column G or Z or AA. Unocked, the "F" will change to reference one column to the right of where ever you paste the formula. If you are always pasting (or dragging) the formula in Column E, the F will never change but an unlocked Row number will change to reference the current Row.

If that doesn't solve your issue, and you need more help with the INDIRECT function, let me know.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
March 19, 2020 at 17:15:40
    A	  B	C    D    E	                                          F
2					
3		6	  ="["&F$3& " 0"&(INDIRECT("C"&ROW())&"] - ")	  Series Title
4					
5		3	  ="["&F$3& " 0"&(INDIRECT("C"&ROW())&"] - ")	
6		9	  ="["&F$3& " 0"&(INDIRECT("C"&ROW())&"] - ")	
7					
8		5	  ="["&F$3& " 0"&(INDIRECT("C"&ROW())&"] - ")	
9					
10					
11					
12		8	  ="["&F$3& " 0"&(INDIRECT("C"&ROW())&"] - ")	
13					

Sorry, I might not have explained clear enough. Let's assume that firstly I have the series number in column C. The empty cells under column C are books that are not part of a series and that's why they are blank. Next, I would enter the title of the series under column F on the first row that has a series number under column C. Then I would enter the formula under column E on the same row where the title was placed, which should give me the answer of "[Series Title 06] - ". I then copy and paste the formula in column E onto every row that has a series number.
I don't want to have to copy and paste the series title onto every row under F that has a series number under column C. I want to change the formula in column E so that I don't have to copy and paste the series title in column F for the remaining books in that series.
You have shown me how to insert the INDIRECT command into the second half of the formula. But I have no idea how to do the same to the first part including the "$" in order to lock the cell under column F. Basically I would like to have the F$3 in the first part of the formula replaced with a col. F + a locked INDIRECT row number.
Thanks in advance.

message edited by uhjb


Reply ↓  Report •

#5
March 19, 2020 at 17:56:57
OK, now I see what you are trying to do. Your use of the word "lock" in relation to Column F was confusing. What you really want to do is lock the Row of the Series Title but only until the next Series Title comes along and then you want to lock that Row until the next Series Title, etc.

If that's the case, it can be done fairly quickly if you don't mind using a Helper Column. A Helper Column is used to do an "intermediate calculation" to determine a value that can be used in another formula to get to the final result. There may be a way to get this done with a single formula (no Helper Column) but I couldn't come up with one right away.

So give this a try. You'll be dragging 2 formulas down. No more copying and pasting.

Chose a column to act as your Helper Column. You can always hide it later or move it out of sight if it bothers you. I'm going to chose Column L just because I want to.

Enter this formula in L3 and drag it down as far as you need:

=IFERROR(MATCH(VLOOKUP(F3,$F:$F,1,0),F:F,0),L2)

This will return the Row number of the Series Title from F3 in each cell until the next Series Title in e.g. Row 14, then it will return a bunch of 14's in each cell until the next Series Title, etc.

Try it, you'll see what I mean.

Then in E3, enter this formula and drag it down. It will use the Row numbers determined in the Helper Column to pull the Series Title from F3, then from F14, etc. but only if there is a value in Column C.

=IF(C3<>"","["&INDIRECT("F"&L3)&" 0"&C3&"] - ","")

Let me know if this method works for you.

message edited by DerbyDad03


Reply ↓  Report •

#6
March 20, 2020 at 23:01:44
It works like a charm. Thank you for all your effort and I do appreciate all your creative thinking. Keep safe and healthy.

Reply ↓  Report •

#7
March 21, 2020 at 06:10:42
Stay tuned. I've been working (with some help) on a single formula that can be placed in Column E, eliminating the need for the Helper Column.

Hope to have that by the end of today.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#8
March 21, 2020 at 18:37:28
✔ Best Answer
Put this in E3 and drag it down. The Helper Column is no longer needed.

=IF(C3<>"","["&IFERROR($F3&LOOKUP(2,1/N($F$3:$F3<>""&F3),$F$3:$F3),$F3)&" 0"&C3&"] - ","")

I won't take credit for the LOOKUP portion of this formula, but that is the section that allows it to be used within Column E without the Helper Column. I think I understand how it works, but not well enough to explain it.

One warning: Based on what I was told by the person who helped me with it, it may slow down your worksheet. There is some array stuff going on inside the LOOKUP function which can slow things down.


message edited by DerbyDad03


Reply ↓  Report •

#9
March 21, 2020 at 22:58:24
Absolutely awesome formula. I can't believe how good it works now, way better than my expectation. I did make a couple of small changes from C3 to C1 and F3 to F1, so I will always place the series title in F1 and drag down, regardless where the first number appears under col. C.
I can't thank you enough for you and "the other person" wonderful work. BTW, the speed was never a concern. It's still way faster than what I had to do before to complete the same task. Thanks again and stay healthy.

Reply ↓  Report •

Ask Question