Cell Reference with Letters

**dreicer_Jarr** · 02-08-2010, 09:52 PM

I need to do over 50 sumif formulas. They will look like this:

Sumif(range,value1,SheetX$A$1:$A$1000)
Sumif(range,value2,SheetX$B$1:$B$1000)
Sumif(range,value3,SheetX$C$1:$C$1000)

How do I continue this trend without re-typing the "SheetX$D$1:$D$1000", "SheetX$E$1:$E$1000", etc., etc., etc.,

Can I use some sort of indirect forumla? I have a helper column with letters A-Z and beyond but am at a loss for how to use it? There has to be a way to reference this list to make this more efficient, right?

**Palmetto** · 02-08-2010, 10:16 PM

There has to be a way to reference this list to make this more efficient, right?

Consider using a Pivot Table and set the "value 1", etc. as the row item or Page header.

**DonkeyOte** · 02-09-2010, 07:21 AM

Formula wise, for sake of demo assume

a) first formula is in B1

b) second formula is in B2 (ie we're assuming you're copying down else you could just use relative column reference)

=SUMIF(range,value1,INDEX(SheetX!$1:$1000,0,ROWS(B$1:B1)))
copied down

the column used in the INDEX will increment from 1 to 2 to 3 etc as the formula is copied down
the use of 0 for Row ensures all rows are included

Adjust references as necessary.

NOTE: INDEX unlike INDIRECT is non-volatile (for more info. see link in sig.)

Cell Reference with Letters

LinkBack

Thread Tools

Rate This Thread

Display

Cell Reference with Letters

Re: Cell Reference with Letters

Re: Cell Reference with Letters

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions