Populating a formula in a large spreadsheet
Duh....
You are soooo right! I had a brainfart. Thanks
Quote:
>-----Original Message-----
>Any cell reference consists of two parts, ie a Row and a
Column reference. AN1 has a Column part ie the AN bit,
and it
Quote:
>has a row part, ie the 1. Each of these can be treated
entirely separately when it comes to making it absolute or
Quote:
>relative:-
>AN1 copied across columns will increment the Columns part
>AN1 copied across rows will increment the Rows part
>$AN$1 copied across columns will stay fixed
>$AN$1 copied across rows will stay fixed
>$AN1 copied across columns will stay fixed
>$AN1 copied across rows will increment the Rows part
>AN$1 copied across columns will increment the Columns
part
>AN$1 copied across rows will stay fixed
>--
>Regards
> Ken....................... Microsoft MVP -
Excel
> Sys Spec - Win XP Pro / XL2K & XLXP
>---------------------------------------------------------
-------------------
>Polite Request - It is very very much appreciated in
>text-only groups if you do not attach files - Thanks
>---------------------------------------------------------
-------------------
Quote:
>> I am building an excel spreadsheet to track leasing
>> information for assets and build a corporate budget for
>> capital expenditures.
>> Each row contains an asset with a commence date and an
>> end date for leasing. Each month there is a leasing
fee.
>> What I am trying to do is, show a lease amount for each
>> month of the lease for each asset and show a blank
prior
>> to the commence date and after the end date. E4, G4 and
>> J4 are absolutes as they reference the begin date, end
>> date and monthly rental.
>> Rows = Assets
>> Columns = (starting with AN1) months of year with a
total
>> column at the end of each year. This sheet will span
>> multiple years. I am using this formula which works
fine,
>> =IF(AND($E4<=AN1, AN1<=$G4), $J4, "")
>> so when I want to populate a row I can certainly paste
>> across the columns as AN1 will increment correctly. My
>> issue is pasting down a column. Even with an absolute
>> value, the reference to AN1 increments to AN2, AN3 etc.
>> AN1 is the date field that should be tracked as an
>> absolute down but across it should increment to AO1,
AP1,
>> AQ1...etc. Consider those to be like header columns.
>> Any Ideas?
>.