Help with a formula for a key system.

[himatvarsani]

Hi All,

I am creating a key management system for our properties.

On the first tab 'Key Sets': I list all our properties with the number of sets, I give the key number as a reference to that property and if that property is sold I can always replace it with another address with that key number. The column of Set Left is where I am struggling to put the right formula...

Set left: how this should work is that it looks up from the second tab called 'Key tracker', it should looks at the whole table with data (when entered), and if the key number from the table column 'Key Set tab' matches the table column Key Number on 'Key Tracker tab' this should minus from set left (Key Tracker->No of set taken: e.g if you take 2 sets - its should minus 2 from Set left), and then if you returned it should add it back on to Set Left (Key Tracker ->No of Set Return). I am still a novice using formulas on excel so pardon me if this is a simple formula.

I hope this makes sense.

I hope that I have attached the file correctly.

Please feel free to ask any question, I really appreciate your assistance.

Thank you.

[mike7952]

One way

Please Login or Register  to view this content.

[D.Lovell]

EDIT: Ignore this, look at my next post down

In cell D10 and copy down.....

Please Login or Register  to view this content.

This is what you mean right?

[D.Lovell]

Apologies this is what you want actually....

In D10 and copied down.....

Please Login or Register  to view this content.

[himatvarsani]

Thank you Mike and D.Lovell. I have used D.Lovell formula this work and its fantastic.

On another note. I am using =VLOOKUP(E4,'Key Sets'!$A$10:B18,2,FALSE), so on tab 'Key Tracker' when I enter the key number on the 'Key No' row the address automatically appears. Now lets say the property in Key No 1 has been sold and I replace this with another address this will also change the historic address, how can I amend the formula so that I can keep old address.

Thank you

[D.Lovell]

You are welcome

To retain the previous address you must actually store it somewhere.

You only have it entered into the column on the key sets tab and the other tab is looking it up from that, so if you change that, it is gone.

Therefore a modification to the design would be required to achieve this.

[himatvarsani]

Thank you again. I have an idea of what I can do.

I was wondering, I am a novice using excel formulas as I don't know what all the formulas and what it allows you to do? How can I learn more and start improving on my knowledge?

[D.Lovell]

There is tons of learning material out there, I believe this maybe a good place to look for links: https://www.excelforum.com/excel-new...additions.html

Your design may benefit from a 3rd sheet with key number down column A, and then the column headers from B onwards as Address 1, Address 2, Address 3 etc...

It would be possible to keep the key tabs sheet looking the same by looking up the most recent address from the other sheet, and then work out a way to retain the previous address on your main data input sheet.

If you need help with further formulas, please feel free to ask again when the design layout has been altered to allow for this.

My suggestion is only one such example of how you could go about it, hopefully if you understand the logic of what I am saying, you can lay it out the way you want it, with this taken into account.

Regards,