Using a drop down box to fill a cell

**john dalton** · 03-11-2011, 05:54 AM

Hi,

I am trying to use a dropdwon box to populate a running stock cell which is dependent on which is the current period, the running stock is exreacted from the opening stock minus any stock issues and inclusive of any deliveries to produce the current stock level for each item please find attached a spreadsheet with the details on

**6StringJazzer** · 03-11-2011, 12:16 PM

Put this formula in C5 then copy down

=INDEX($F5:$IV5,,MATCH($B$1,$F$3:$IV$3,0)+3)

**john dalton** · 03-14-2011, 04:29 AM

Works great - next problem is I will be having 13 periods how can I adjust the formula to look at 13 cells set against the displayed cell.

**6StringJazzer** · 03-14-2011, 06:44 AM

Have you tried this formula after adding more periods? It has no limit for the number of periods. It looks in all columns possible in Excel 2003.

**john dalton** · 03-14-2011, 06:52 AM

yeah it works right across all periods cheers for the help -ps how do put these threads as solved?

**6StringJazzer** · 03-14-2011, 08:04 AM

Originally Posted by john dalton

...how do put these threads as solved?

See instructions in my signature
|
|
V

**john dalton** · 03-15-2011, 07:14 AM

One more quick question - i have the folloowing calcualtion in the good issued columns of each period

=SUMPRODUCT(-('Stock Movement'!$A$5:$A$9120=Data!$BU$4),-('Stock Movement'!$F$5:$F$9120=Data!A6),'Stock Movement'!$I$5:$I$9120)

When I sort the coloumns the part weher it says =Data!A6 which refers to the ID no. allocated to each stock item becomes muddle up ie. if the stock code in A6 is 001 and the sheet has been sorted it would say =Data!A215 it never seems to relate to the actual line reference, I have tried typing it as =Data!$A$6 but i would need to alter every calculation in each coloumn as if i copy it then it would say $A$6 all the way down