[ms office - 2016] Excel formula - is this even possible?

[MatthewL]

Imagine you have a list of dates and you enter readings and figure is generated based on the previous figure.

 

You then have another sheet with a date from and date to (might need to change this and use based on rows) then 2 figures can you enter the date on the first sheet then look up the second and if its in between them dates use the figure there to do a calculation?

 

Question is what formula would I need as the one I have used in the past for something slightly similar doesn't work in the way I want.

[Steve21]

Not entirely sure what you mean in your wording of it, but if you mean just using a lookup to check what range a date is and returning that value to do another formula then yes that's just a simple lookup like below (Did it on one sheet for screenshot purposes but would work between two etc):

 

 

If I misunderstood what you meant, or you have an actual example let me know and will take a look

 

Steve

 

Steve

[Ditto]

@Steve21, if I understand the challenge correctly, this will do what @MatthewL requires. It's worth adding if you make your B3 value 24/01/2022, it returns the correct answer as it is between the dates, not just matching the start date column.

[MatthewL]

Thanks, that seems to do what I need, need to do a bit more work but think I can do what I need with that.

[MatthewL]

A slight variation to the above, if I have a list of equipment with a date and the cost associated in one sheet, on another if I select that piece of equipment and put a date in can I get it to bring the price in if that fits between the date?

 

So I select my piece of equipment that is in C2, I put the date in D2 of when I am issuing it and in J2 it returns the cost.

In another sheet I have date-start,date-end,equipment,cost Might need to put the equipment as first column but that is not an issue.

 

So the overall aim would be to populate the cost when valid to the piece of equipment been issued based on the pricing in the other sheet that way the true value of what equipment was purchased for it kept.

[andy_b]

Can you mock up a demo, I don't really understand what you are asking for?

[MatthewL]

Uniform.xlsxUniform.xlsx

 

I will explain what I want to happen. Maybe not the way to do this but I need a simple solution for a simple person!

 

The workbook will open on the summary sheet each time using a macro then you can select what you want.

 

Staff will contain a list of staff members and their ID number which will then be used for a drop down in the issue log.

 

Lists is for the drop downs.

 

Costs will include the date from and when certain items cost and its value.

 

Stock new and used are to enter stock levels of equipment. Stock total is a sum of the other 2.

 

Issue log is where the main work will happen, you will select number from drop down it will then populate the name, select equipment type, the date, size, qty issued, reason from the list, cost would then be calculated based on date and cost sheet, finally select which stock used.

 

The summary will then total the equipment and stock, just looking for an easy way to bring the staff numbers over left to right from the staff list, ideally I'd like a column in staff list if they are active or not then update the populated list based on that, if not I will swap equipment and staff over.

 

It's mainly the costs I'm struggling with then once that is done I will put a column in the staff list to show who is costing the most.

 

Bit of a favour to a mate, but needs to be easy to manage once the formula's are all in place so he can just add staff, equipment and costs and I can format nicely for him.

 

Hope that makes sense any questions ask.

[Steve21]

Try:

 

=INDEX(Costs!D$2:D$100,MATCH(1,IF(D2>=Costs!A$2:A$100,IF(D2<=Costs!B$2:B$100,1)),MATCH(C2,Costs!C$2:C$100,0)))

 

Pasting it into Issue Log -> J2 (Drag down)

 

Think that'll work if I understood you correctly. The only issue is it'll always default back to the top if it doesn't match anything, so you'll probably notice the Hi Viz Jacket shows the wrong value because you have no dates set on Costs, but that should fix it once you do

 

Steve

[MatthewL]

The above worked if I entered it with CTRL, SHIFT & ENTER forget the name for that.

 

Also costs needed to be sorted by start date and equipment for it to work.

 

Thanks for that I think I can work with that.

 

Any idea how get values from vertical to horizontal?

[Steve21]

The above worked if I entered it with CTRL, SHIFT & ENTER forget the name for that.

 

Also costs needed to be sorted by start date and equipment for it to work.

 

Thanks for that I think I can work with that.

 

Any idea how get values from vertical to horizontal?

 

Delete the empty cells, and try in Summary (G1):

 

=TRANSPOSE(Staff!$A2:$A46)

 

(46 based on your current columns at least)

 

Steve

[MatthewL]

Ah works now, I know where I went wrong with that, again CTRL, ALT & Enter and it works.

 

Cheers