Copying formulae within a table

[Reshaw]

Afternoon everyone

I am fluent in Excel 'traditional' formulae but I find myself having to interface with tables from other sources frequently so trying to get up to speed with the nuances...

I am currently trying to pull sales forecast information from a master lead sheet, its all very basic and I have got the following formula to work:

=IFERROR( IF( AND( LeadData[@[Lead Outcome (Forecast / Lost)]] = "Forecast", LeadData[@[Lead Name]] = ForecastedSales[@[Lead name]]   ),
                IF( LeadData[Forecast Close]    =    ForecastedSales[[#Headers],[Jan-17]],  LeadData[Weighted Forecast], 0),""),"")

I have a basic table "ForecastedSales" showing the next 24 months along the top row (headers) and the lead names down the left most column. So for clarity I have the above formula in cell B2.

When I drag the formula to cell C2 in the 'traditional' way, it updates for example the [Jan-17] part to [Feb-17] which is fine but it also changes all the column references to their adjacent values as well. I would have put $ in front of the cell references before to stop it from updating them when copying across but with the table references I can't figure out how to stop it doing it.

I apologies if this is a simple problem but I can't find any reference to this on Google, perhaps I am not describing it well.

Can anyone help point me in the right direction?

Thanks in advance
Robin

 

[Reshaw]

Re: New to Table formulas - little help needed when copying formulae within a table

I found a half decent guide online finally, in case anyone else has this same issue, please check out this link:

Absolute Structured References in Excel Tables | Excel Campus

Thanks
Robin