Applying formula with merged cells

[abdulhaque]

Hi all,

I'm trying to apply my formula =IF(A2 <> "", LEFT(A2, LEN(A2) - 5), "") & " " & C2 to subsequent rows. I have merged cells in one of the columns, which doesn't work. Image 1 shows how it works currently. The formula is in column B. I need it to work like in image 2. Would I need to use VBA? If so, can I get some help please?

Image 1
https://i.imgsafe.org/3612148e88.png

Image 2
https://i.imgsafe.org/3613b0395b.png

Thanks

 

[jorismoerings]

Hi,

Try this but be aware. It's a given fact merged cells cause more issues than they ever solve.

Book1
	A	B	C
1	Pool Name	Rule Name	Member
2	Pool Name 1 Pool	Pool Name 1 Member1	Member1
3		Pool Name 1 Member2	Member2
4		Pool Name 1 Member3	Member3
5	Pool Name 2 Pool	Pool Name 2 Member4	Member4
6	Pool Name 3 Pool	Pool Name 3 Member5	Member5
7		Pool Name 3 Member6	Member6
8		Pool Name 3 Member7	Member7
9		Pool Name 3 Member8	Member8
Sheet1

Cell Formulas
Range	Formula
B2	=IF(LOOKUP("zzzzzz",$A$1:A2) <> "", LEFT(LOOKUP("zzzzzz",$A$1:A2), LEN(LOOKUP("zzzzzz",$A$1:A2)) - 5), "") & " " & C2

 

[abdulhaque]

Thanks it worked providing there's a pool name populated for the current rule name rows, otherwise it extracts the previous pool name and joins it with the current members. I guess that's the best that could be done using default functions. Much appreciated.

Quick question, how did you post the worksheets and formulas in the post above?

 

[AliGW]

Quick question, how did you post the worksheets and formulas in the post above?

Have a look at the link to the ForumTools in my signature line.