Tricky problem with column reference in formula

**judasdac** · 08-14-2009, 01:10 PM

Hi All,
I'm trying to revise a salary payments workbook that isn't set up so well and I'm struggling with finding a good way to enter payments from an old sheet onto a new sheet. The values (for example) I want to appear on the new sheet are in range C2:AQ2 on the old sheet. Instead of going left-to-right, I want them to appear from top-to-bottom.

The first formula I wrote was just a simple:

Please Login or Register  to view this content.

. I've not been able to figure out how to copy that formula down with the column reference changing. I've tried an index match formula,

Please Login or Register  to view this content.

which is not pulling in the right information.

I think using the INDIRECT function may work best, but I can't quite get that down.

Any ideas? I wasn't able to attach the sheet, as the upload failed. Thanks in advance.

Cheers,
Tim

**NBVC** · 08-14-2009, 01:15 PM

Not sure what your named ranges are referencing...but generally you would use something like

=INDEX(SHeet2!$C$2:$AQ$2,,ROWS($A$1:$A1)) copied down... replacing only the reference to the SHeet2!$C$2:$AQ$2

where

**protonLeah** · 08-14-2009, 01:32 PM

Suppose you want to put the values in column B; select Column B, enter the formula below in the formula bar.

Please Login or Register  to view this content.

Commit the formula with Ctrl+Shift+Enter

**judasdac** · 08-14-2009, 01:41 PM

Thanks for the quick reply, but I'm still a bit tangled up. I get a #ref error after row47.

I have two sheets. The sheet I'm trying to populate is Sheet1 while the data is coming from a sheet called OTPerPayroll.

For each PayDate, there are four types of salary payments:
8/10/2007 Salaries
8/10/2007 Fringe
8/10/2007 Differentials
8/10/2007 Overtime

This data is will be in a range that extends from D4:AY171. Currently, I'm only working with overtime, and that begins in D7.

The formula I'm using now is: =INDEX(OTperPayroll!$C$20:$AQ$20,,ROWS($A$7:$A47)). And that seems to work. However this formula, =INDEX(OTperPayroll!$C$32:$AQ$32,,ROWS($A$7:$A51)), results in a #ref error.

I'm sure I'm doing something incorrect, but I can't figure it out. I really wish I was to upload the worksheet.

**NBVC** · 08-14-2009, 01:43 PM

it is because you have pulled down the formula in more rows than there are columns in your indexed range... Ie. there are only 41 columns in range $C$32:$AQ$32 so you can apply the formula to a max of 41 rows..