Creating a Vertical List of the Titles of several Vertical Lists

**ShadowBMe** · 07-02-2013, 04:07 PM

I can manually enter in the references for each of the lists titles, but I was hoping that there is a much easier way.

Here is a working representation of what I am doing if copied into Excel.

Quantities		List Title 1	List Title 2	List Title 3
=IF(C1="","",C1)	=COUNTA(C2:C51)	List 1 Item 1	List 2 Item 1	List 3 Item 1
=IF(D1="","",D1)	=COUNTA(D2:D51)	List 1 Item 2	List 2 Item 2	List 3 Item 2
=IF(E1="","",E1)	=COUNTA(E2:E51)	List 1 Item 3		List 3 Item 3
=IF(F1="","",F1)	=COUNTA(F2:F51)	List 1 Item 4

As you can see from the example, it can become tedius for 25-50 lists writing that first column. I feel that there should be a much easier way to do this. I had thought about writing a function to do this for me but I wanted the formula so that the contents of the first list updates as new lists are entered.

**dipique** · 07-02-2013, 04:34 PM

I'm not sure I understand, but would it help to use the Address formula?

Address(1,1) = $A$1

I'm assuming that the issue is number to letter conversion.

Dan

**ShadowBMe** · 07-02-2013, 05:42 PM

The first point of clarification: A1 and B1 are merged cells. So "Quantities" is the name of that list containing A2:B5 with A2:A5 being the names of the lists that follows in C1:F1 (F1 being currently blank) and B2:B5 showing the count of how many items are in those other lists.

Now the issue I am bringing up is that I want to know if there is an easier way to put the formulae that are in A2 and B2 to fill up the list. All I can come up with so far is that I have to copy A2 and B2 and paste it to A3:B5 and then go through each and every one of those cells changing each of their references to the other columns. This is very annoying since it works references to be relative to the shift in where it is copied, meaning that A3, instead of stating: =IF(D1="","",D1), copies to state =IF(C2="","",C2) and A4, instead of stating =IF(E1="","",E1), states =IF(C3="","",C3).

I hope that cleared things up to be the least painful possible.

In all reality, I can just copy them and go through each cell changing the references as I had described, but it becomes tiring in the fact that I am working on a larger scope like A2:B101, so if there is an easier way to do this, I would be grateful.