• 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.

VLOOKUP Question

T

tropical

New Member
I do a number of invoices each month that are based in part of billings from utility companies. The billings show the electrical meter or gas meter usage for various buildings. I use these readings to determine how much each tenet must pay for their share.

To do this I have worksheets for each of the utility companies. I add a new ROW for each month's entries. Once all of the utility company data has been entered, I have separate worksheets for each tenet, which draw from the utility company data.

For example, to get the total amount of electrical usage for a given month, I might have the following:

=MeterReadings!N15-MeterReadings!N14​

where "N15" is the current month reading and "N14" is the prior month reading.

After entering all of the Utility Company info each month, I must manually change each formula, such as to the following:

=MeterReadings!N16-MeterReadings!N15​

Instead, what I would like to do is write a VLOOKUP formula where the first article (DATE) would change based on the content of a specified cell.

For example, on tenet Bob's billing worksheet, I had already entered the date of the billing -- e.g. "March 2014". Thus, to obtain the correct data from the utility company workseet, I would like the first article of the VLOOKUP formula to automatically change to the current month, i.e. "March 2014".

Any help with this would be greatly appreciated!
 
Hi tropical,

A sample file with dummy data but having the same structure and desired output will be helpful for those who wants to answer your problem and will save a lot of your and users time.

Regards,
 
Not to sound condescending, but if I were able to create a sample file with dummy data and having the desired output, I would not be seeking help here. Are these Forum rules for posting my situation?

As simple as I am able to make it, I would like to:

a) I have some Tenet Worksheets (TW) that draw data from Data Sheets (DS) using VLOOKUP
b) The DSs are updated monthly so that the TWs can be updated
c) As soon as DSs are updated, the VLOOKUP links in the TWs would be automatically updated based on the date.
 
Hi ,

I think you have misunderstood the suggestion ; people who post questions in forums do so for various reasons , because they do not know some aspect of Excel , which if they knew it would help them resolve the problem on their own.

In your case , what you are looking for is probably some formulae ; when a sample workbook with inputs and outputs is suggested , what is meant is input data , and the desired output ; as simple as I am able to make it , if I know that you need 5 given 2 , I can suggest that you add 3 to it.

When detailing an Excel problem , the ideal would be to upload a workbook ; the next best thing is to talk very specifically quoting cell / column / worksheet references ; merely putting everything down in English not only means more work for you , it means more work for those who wish to help out.

If you say specifically that :

1. These are the cell(s) I am talking about

2. These are the formulae I have

3. These are the outputs I need

4. This is how the outputs are arrived at

putting all of the above into language that Excel can digest becomes that much easier.

Narayan
 
@tropical

I think you did not got my comment. When anybody say Sample file, than he/she is interested in looking the data layout, data distribution across sheets, data type, this helps in creating asolution.

Desired output are the values that you know should come, but unable to get, this helps others users to check their solutions.

Regards,
 
Hi ,

The first tenet of problem solving is :

Specify the problem , not the process of problem solving.

If you have a problem , detail the problem ; how to resolve it is the job of the person who understands your problem and thinks he / she has an Excel solution.

Please do not talk of VLOOKUP or any other Excel function ; tell us instead what you want done.

Narayan
 
Thank you for your responses. I apologize for the inconveniences. I did not think the question was that complicated. I will look elsewhere.
 
I am sorry you are going elsewhere. There are many brilliant excel helpers on this forum. A sample file with the layout you required would have made it so much easier for one of them to help you and those of us that are still learning and lurking may have learnt something too. :confused:
 
@tropical ,

Yes the first term of VLOOKUP() can be variables, and it is almost always set as variable, because it is the look-up_up value that changes time to time...Please elaborate further your problem.

Please see this file.
 

Attachments

  • Tropical_Sample.xlsx
    8.8 KB · Views: 4
You must log in or register to reply here.
Back
Top