Excel - Can it be done without VBA?

Martynt74 · 15 Mar 2017 at 22:38

I'm trying to create an example tool in Excel and can't work out how to get it to do what i want and i think VBA may be required but i'm not great with it.

Heres a screenshot of the base file.

I have a monthly period. With a set of parameters on the left

Review Period
Lead Time

I have done a formula, not very clever but it works and my brain isn't functioning to do it better. Which says if the review period is every x months then stick an "R" into the review row.

I then say that

-Opening stock - is based on a predetermined figure to start and then equals the previous months closing stock.
-Issues in Month - is entered as forecasted
-Receipt in Month <---What my issue is with
-Closing Stock - is the sum of the above
-Order Placed - this says if the top row = "R" then calculate "Max Value" less "Closing Stock"

What i am trying to do is factor in the lead time for the "Receipt in Month" row. To say if i say an order is placed, then in x number of months enter the order into the receipt field. I know in VBA you can move the cursor around a specific number of cells and paste data but that doesn't feel like the right solution.

As mentioned i'm tired and not slept a lot so i'm happy to accept i've not set this form up very well.

I suppose using my parameters of 3 monthly review and 1 month lead time i'd expect it to look as follows

File here
http://www.filedropper.com/13examplemin-max20170313-mt

Any help would be much appreciated.

EDIT - I'm now wondering if i can use Index/Match to get it to do this but using the Lead Time to specify the row i want to pull data from.....or i'm clutching at straws and should just go to bed!

Martynt74 · 15 Mar 2017 at 23:02

Well i've got something working. Have used the Offset function, which i guess is what i was getting at with VBA

=IFERROR(OFFSET(I7,4,-$B$3)*1,0)

Then looked 4 rows down and then used the column reference to be -"whatever is in Lead Time"

Because it then pulled in "Review to Order" depending on the entry i had to multiply by 1 and use an iferror.

An absolutely shocking workaround which i'm sure you'll cringe at but it'll do for the time being!

smogsy · 16 Mar 2017 at 08:50

you didnt say which version of execel as newer versions use C#

Martynt74 · 16 Mar 2017 at 09:04

Ah. I'm in 365

Never knew that. Does that kind of make VBA redundant then?

gpuerrilla · 16 Mar 2017 at 21:18

For developing I think he may be on the line of "apps for office" but first I've heard regarding c#

smogsy · 17 Mar 2017 at 18:12

https://msdn.microsoft.com/en-us/library/ee342218.aspx
http://csharp.net-informations.com/excel/csharp-excel-tutorial.htm

they changed it as W10 was not going to support VB so had to use another language. HOwever VB users got annoyed VB support was put back into W10

gpuerrilla · 19 Mar 2017 at 20:50

smogsy said:
https://msdn.microsoft.com/en-us/library/ee342218.aspx
http://csharp.net-informations.com/excel/csharp-excel-tutorial.htm

they changed it as W10 was not going to support VB so had to use another language. HOwever VB users got annoyed VB support was put back into W10

Ahh that makes sense smogsy.

smogsy · 19 Mar 2017 at 22:08

yup, unfortunately in my line of work little things like this comes up & sad and remember it!

james.miller · 22 Mar 2017 at 11:57

c# was never going to replace VBA?

Office programming in visual studio is a separate thing entirely. MS have never been in any hurry to dump VBA which is supported in every version of office.