Formula for changing numbers

[KWayland]

Does anyone know how to use a formula for a changing number?

The example is a basic!

The most up to date value would be from the most recent event. I am making this spreadsheet for a shared drive so I will not always be the person updating it. This means I am entering the formula but leaving it blank so that when these events occur other administrators can enter the info in. This means the options chosen on each row will be blank and when someone else uses the spreadsheet they may select different options

In the example, flyer 1 is used at events 1, 4 and 5 but not at events 2, 3 or 6. To get the most up to date number of stock for Flyer 1 you would have to deduct the number used at event 1. e.g now only 90 remain. Thus I want want 90 to appear in C5 but I don't just want to automatically populate this cell from E2 as this may not be the option that was put it by someone else.

As these are drop down boxes in my actual sheet I want to input a formula that tells excel that to check the sheet for previous usage of this flyer and if no usage is present, populate from the table in L-M, otherwise populate from the stock remaining as a result of the last usage.

Is this possible?

[etaf]

in N2 i have put the formula
=M2-SUMIF($B$2:$B$7,L2,$D$2:$D$7)

which will give the stock left

you could use that in E2 -so you get a running total

and so you can use any flyer - perhaps modified with a vlookup - if that helps
=VLOOKUP(B2,$L$2:$M$4,2,FALSE)-SUMIF($B$2:$B$7,B2,$D$2:$D$7)

also for the stock level in C2
=VLOOKUP(B2,$L$1:$M$4,2,FALSE)

see attached

[KWayland]

Thanks - but none of these do exactly what I need

[etaf]

ok, misunderstood the requirement

[KWayland]

No problem - i'm not sure that what I want is possible.

[KWayland]

OK,

I've been chatting to someone in the office and I don't know if this makes what I want clearer.

I want Excel to self-update.

i.e. if flyer 1 started with 100 copies and we used 10 it would leave 90 remaining. In the same example (already posted) I want this number (90) to be inputted into C4.

I don't want to code it cell for cell and I'm adding the formula and then leaving it blank for others to populate. This means I need a formula that searches the sheet for other usage of the flyers - if no previous use is found it populates from a table BUT if other usage is found it uses the remaining stock. I also want to ensure that if left blank the cells do not produce an error message.

[etaf]

i thought that was the result i provided - looking at the stocklevel
=VLOOKUP(B2,$L$2:$M$4,2,FALSE)-SUMIF($B$2:$B$7,B2,$D$2:$D$7)
this looks up the flyer used from the list in L-N - it needs to know the starting stock level
and then
it takes away - all the flyers of that type used in column D

[KWayland]

When I put that into my document it didn't work :S
It gave the output "#N/A"

[etaf]

is this a different document - you would need to change some of the entries to make it work somewhere else

#N/A - probably means it cannot find the lookup

can we have a little more detail

[KWayland]

it is a different document but I am changing the cell refs accordingly.

Please find attached a more detailed doc.

Please see the formula currently in column C as this resolved previous issues of errors showing up.

Your method works in part but it doesn't work entirely for some reason :S

[etaf]

this is the starting stock - "name of item and in the drop down

AH - A4 100
CLM - A4 100
VCCA 100

and copied into column C

column D is the amount used

then you need to use the
=C3-SUMIF($B$2:$B$30,B3,$D$2:$D$30)
to find all the quantity left

it will look across the entire sheet - so does not give the status after each event - assuming they start in order
is that what you need

So B3 was event
VCCA and 50 used - then total left = in E3 = 100 - 50 = 30
if B4 was 20 used then E3 and E4 will update to 30
are you trying to show a running total in the order on the sheet ?

[KWayland]

Thank you so much for your time on this by the way.

I am not trying to show a running total.

I'm trying to input the formula but leave the spreadsheet blank as I will no be the one entering the information.

This means that I can't copy the information into C as it needs to be left blank. In this sheet the stock numbers of all the booklets are the same but on other sheets (not included) the stocks number varies. I also cannot assume that these will be inputted in order as they are just a log of what has been used and different events might use different handouts. This means it could end up being:

AH 100 50 50
VCCA 100 10 90
AH 50 15 35
CLM 100 20 80

This isn't in order and as I wont be populating the spreadsheet, I am just creating/formulating it, I cannot know in which order they will be added.

[KWayland]

Also I just tried what you suggested and It only works for one of the three flyers. If I put it a different flyer the number stays at the reduced number of the different handout

[etaf]

C is taken from L and M
if noone is entering the starting stock level , then not sure how it will work

In this sheet the stock numbers of all the booklets are the same but on other sheets (not included) the stocks number varies

- where is that info of the starting stock going to be held ?

Also I just tried what you suggested and It only works for one of the three flyers

did you use the example I posted - which had all the formulas added

and will start blank - if no item selected

[KWayland]

Sorry i wasn't very clear.

The table in L and M will be there and the numbers are correct.

What I mean by i'm not populating is that i'm leaving it blank and it will be filled out in a month or two's time by someone else. so I do not know in which order they will input the flyers used as it will vary.

I copied:

=C3-SUMIF($B$2:$B$30,B3,$D$2:$D$30)

into C4

[etaf]

=C3-SUMIF($B$2:$B$30,B3,$D$2:$D$30) - that should be in E ?

have a look at the version I posted in #11

[KWayland]

OMG - I can't believe I put it in the wrong cell!

Thank you so much!! You have fixed my problem!!

I apologise for taking up so much of your time!

Thank you!

[etaf]

no problem , you are welcome

to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.