• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formulae to pick up month/timing of purchase

R

rajkenya1

Member
Hi all

I am trying to develop a spread sheet for items bought on one coloumn which includes the timing of when the item was bought. In the rows i have my months and start and end dates of the month. I want to create a formulae in the row cell which will pick up the month the item was bought and place it in that cell. If not the correct month then the cell remains blank.

See attached spreadsheet for example.
Will appreciate your comments.
 

Attachments

Hi Somendra

Thank you very much for the prompt reply and solution to what i was looking for.

Really appreciate your time and effort into providing an answer.

Kind regards

Raj
 
Hi

Sorry to bother you again. I have a follow up question.

Attached in the simple spread sheet there is a fixed asset purchase schedule which has a few purchases made at different times.

Is it possible in cell F20 to put in a formula which looks at the item and the date and then finds the date range from the schedule above and enters the amount in the correct month. I am assuming a vlook formulae is required or something. Is it something you can help with.

Cheers
 

Attachments

Hi Somendra

I am working towards a fixed asset and depreciation schedule. Therefore say i have many purchases of computer equipment in a year on different months. I want cell F20 to pick up the asset value and correct month from the scheudle of fixed assets timing above it all on one line i.e (cells f20 to q20). This is incase i change the month of purchase the spread sheet is automatically updated.

Hope this makes sense friend.

Cheers
 
Dear Somendra

Are you a genius of what? Wow thats one complex formula. Well done!!!

Thanks once again genius.

Kind regards

Rajeev
 
@rajkenya1

Thanks for your kind words. There is nothing genius in the formula, its just the knowledge of different formulas you should have and knowledge of applications in situations like this.

May be somebody can come up with a smaller version of the formula.

Regards,
 
Thanks Somendra

But it must have required a lot of thought. Well done anyway.

I am just trying to understand it fully, how come you have inserted 12+Match and 13+Match in the formula. Is there a significance to this number. Sorry if i sound so dumb?

Are you able to theorise the formula for me to understand it better. I also note that there is no absolulte cell reference to the amounts in the timing schedule yet they are correctly picked up in the month they occur. Is this magic or am i missing something in my understanding? lol

Thanks buddy
 
@rajkenya1

12 & 13 are the row numbers from 1 where you had timing and values.

Now, the formula is simply looking at the row with a matching item say computers and returning all dates for that item on which you have put some amount. Next it is comparing those dates which are falling in any month and lastly it is getting the amount.

Hope this makes sense, otherwise write back.

Regards,
 
Dear Somendra

Thanks for your reply and easy to understand explanation. Apologies for the late reply to acknowledge.

Thanks for all your help once again.

Kind regards

Raj
 
Hi Somendra

Just a quick question regarding the rows.

I am assuming you mean on row 12 is where i had the timing and row 13 the value. What i wanted to know is what if the timing and value information is on another sheet that what number would i insert instead of 12 and 13.

Hope you understand what i am saying


Somendra Misra said:
@rajkenya1

12 & 13 are the row numbers from 1 where you had timing and values.

Now, the formula is simply looking at the row with a matching item say computers and returning all dates for that item on which you have put some amount. Next it is comparing those dates which are falling in any month and lastly it is getting the amount.

Hope this makes sense, otherwise write back.

Regards,
Click to expand...
 
Just put number of rows from row 1. Say timing is on row 15 and amount on row 16, than put 14 in place of 12 and 15 in place of 13.

Hope that make sense.

Regards,
 
Hi Somendra

I dont think the formula would work if on a different sheet.

Have a look at the attachment and let me know.

Refer to sheet "Fixed assets register and depn" and formula in cell e18. The sheet where i have my assumptions is called "inputs general" and look at cell c104 within that sheet. So the formula should reference that sheet.

You will also see a broader model i am working on.

Thanks


Somendra Misra said:
Just put number of rows from row 1. Say timing is on row 15 and amount on row 16, than put 14 in place of 12 and 15 in place of 13.

Hope that make sense.

Regards,
Click to expand...
 

Attachments

You must log in or register to reply here.
Back
Top