Populating a formula in a large spreadsheet 
Author Message
 Populating a formula in a large spreadsheet

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?



Thu, 13 Oct 2005 18:24:15 GMT  
 Populating a formula in a large spreadsheet
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
has a row part, ie the 1.  Each of these can be treated entirely separately when it comes to making it absolute or
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?



Thu, 13 Oct 2005 18:33:39 GMT  
 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
>---------------------------------------------------------
-------------------




- Show quoted text -

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?

>.



Thu, 13 Oct 2005 18:39:29 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Need Formula to sum cells in large spreadsheet that are not in sequential order

2. Populating a List Box with a large list

3. How can I populate a spreadsheet from Outlook?

4. populating spreadsheet with access data

5. Using VB 6.0 interface to populate a spreadsheet

6. Populating Excel spreadsheet from 97 table

7. Populating Spreadsheet Control

8. Help needed: How to populate the spreadsheet cell with a dropdown list

9. Populate Spreadsheet using excel file?

10. Large Catalog Reference Spreadsheet Causes Excel to Run Out of Memory


 
Powered by phpBB® Forum Software © phpBB Group