Which Worksheet is a Value in?

[danellis]

I'm in the process of buying my first home, and am trying to plan out what furniture I need, and when to buy it.

To this end I'm creating an Excel workbook for each room, with a tab for Months 1, 3, 6, 9, 12, 18, and 24, which obviously correspond to the time after occupation. Attached is the file for the Living Room.

I also have a "summary" tab which lists the various things I want to buy (TV, Sofa, Coffee table, etc.), how much they cost and a spec.

I'm using a VLOOKUP linked to the summary tab in each month to fill in all the other values having entered the item.

I would like to have a "reverse lookup" in the "Date Scheduled" column on the Summary sheet to tell me for example, which month I'm planning to buy the TV.

Any help would be greatly appreciated.

dJE
Daniel J. Ellis

Costings-Living Room.xlsm

[Glenn Kennedy]

I had to make a small adjustment to B1 and "A2" in each sheet, to make life simpler. This is one way of doing it. A word of advice, though. You have used merged cells all over the place.

Don't.

They make life VERY diffcult. They're OK (just about...) for headers that will NEVER be referenced in a formula. In general, though - don't touch 'em. They're the work of the devil, you know....

[newdoverman]

If you are looking for the month to purchase an item, use the Overall worksheet instead of trying to go to the month worksheet.

Click on the Formulas Tab, Name Manager, click New and add this definition in the formula bar:

Please Login or Register  to view this content.

Select cell Overall!K6 then click on the Data tab, Data Validation, Allow List, and enter in the formula field.

Please Login or Register  to view this content.

This creates a drop-down listing of the items in column A from which you can make a choice.

In Overall!L6 enter this formula:

Please Login or Register  to view this content.

Format the cell as mmmm yyyy by right clicking on the cell, choose Format Cells, Date, Custom and enter mmmm yyyy click OK.

This formula takes the value returned in K6 and returns the value in column L

Drop-down.JPG

[danellis]

Thanks, but I couldn't spot any differences between your version and my original (and no values in B1). What should I be looking for?

I'll bear that in mind about merged cells, what would you suggest using instead?

dJE

I had to make a small adjustment to B1 and "A2" in each sheet, to make life simpler. This is one way of doing it. A word of advice, though. You have used merged cells all over the place.

Don't.

They make life VERY diffcult. They're OK (just about...) for headers that will NEVER be referenced in a formula. In general, though - don't touch 'em. They're the work of the devil, you know....

[danellis]

If you are looking for the month to purchase an item, use the Overall worksheet instead of trying to go to the month worksheet.

I'm trying to find out which worksheet each item is listed in.

Click on the Formulas Tab, Name Manager, click New and add this definition in the formula bar:

Please Login or Register  to view this content.

What does this do? I couldn't see how it was referenced later.

Select cell Overall!K6 then click on the Data tab, Data Validation, Allow List, and enter in the formula field.

Please Login or Register  to view this content.

This creates a drop-down listing of the items in column A from which you can make a choice.

In Overall!L6 enter this formula:

Please Login or Register  to view this content.

Format the cell as mmmm yyyy by right clicking on the cell, choose Format Cells, Date, Custom and enter mmmm yyyy click OK.

This formula takes the value returned in K6 and returns the value in column L

Attachment 407141

[Glenn Kennedy]

Ha Ha. I attached the original. Try this...

[newdoverman]

Here is code that takes value in cell D4 and searches the workbook for that value and returns all occurrences of the value: The code is from excel-easy.com

Please Login or Register  to view this content.