Choosing entries, which will spit out an array of answers.

[managingcrap]

I have a big problem and don't know how to do it.

I have more than 10 tabs for each vendor (I only included two vendors on the attached sheet). My sheet 1 is just for entries. I want functions that will spit out results as noted on the Results page.

Using the given result example (pertaining to Sheet 1), if I put Dave on C3 for name, Whole Wheat on C4 for Item, and Yes for Category on C5, I want the results (on the Result tab) to extract the information from where the answers are.

If two items are chosen, let's say the items are placed on C4 and D4, then, the corresponding outputs for the C4 will be on row 4 of the Result tab and the outputs for D4 will be placed on row 5 of the Result tab.

I know I'm asking a lot. I have more than 10 tabs with each names. I don't know what needs to be done.

Thank you.

[Glenn Kennedy]

I don't understand what the purpose of Yes/No choice in C5 is? What result do you want if NO is selected?

[managingcrap]

If I selected the NO option then, the entries on columns B to F of the Result tab will not appear. If YES, then the entries on columns B to F of the Results tab will appear.

Sorry for not clearing things up. Thanks.

[Glenn Kennedy]

I do enjoy your problems... I think that this is what you need. At the moment, it will only look down as far as row 20 on the target sheets. How far down should I make it go?

IN ADDITION, I would be very tempted to use data dependent dropdown lists to reduce the possibilities of errors on your selection sheet.

By the way, did you look at the solution I suggested to your other recent problem. I don't remember hearing back from you.

http://www.excelforum.com/excel-form...egory-and.html

[managingcrap]

It is what I need and you are so genius.

The entries will expand. Right now I have around 30, but each quarter I do add five or so new entries. I assume I only need to change an entry within the function to make the function work.

I wanted to have a drop down done as well. I forgot to add that to my initial statement. What I mean is, If I choose Delan on C3 on Sheet1, then a drop down list for Delan will appear. If for example I choose Dave, then a drop down list pertaining to Dave will appear. There might be a way to do that but I have no idea how.

Also too, can you please explain to me what the function is/does or how it works? I still have to explain this to my coworker and not knowing what happens to this function will make it difficult for me to sell them the idea to use whatever I'm doing right now with this.

Thanks again so much.

[Glenn Kennedy]

I have adjusted it so that it looks from row 12 to row 100.

How does it work? It has three parts.

1. A basic Index-Match, which does the work, returning the data that matches Dave and wheat and 2015 and gives you the answer.

2. INDIRECT. This is needed to tell Excel which worksheet to look on. If you select Dave in the top sheet, it only looks in his worksheet.

3. SUBSTIUTE. There may be a neater way of doing this, but it's there to make copy/paste easier, as I copy the formula to the right, it looks into the next column on the source sheet for the answer...

I've now added the data dependent validation. The "Master lists" are on Sheet 1. I'll need a little more explanation about why you have the product lines appearing twice on each named sheet and whether or not you'll need to move the lower half of the data down by adding more rows (I need to know that NOW, 'cos it'll mess up the formulae.

[Glenn Kennedy]

Back again. I have made the third one a bit simpler & (maybe) easier to follow.

[managingcrap]

Sorry for late response. I was out all day yesterday.

The reason that I have same entries on top and bottom is that the entries on top are for budget section for those items. The one below it are designated as actual amounts for those specific items.

I did try to put numbers on the the portion specificall potato for example and the function did not have any confusion. I mean it still took the results from the bottom section. Should it be working like that?

Thanks.

[Glenn Kennedy]

Yes. It's OK. It starts looking at row 12 and stops at row 100. If you need any more rows at the top, we should build them in now and adjust the formula while it's still (reasonably) fresh in my head!!

[managingcrap]

I assume I just need to change the 12 and 100 just in case my bottom section starts from a different row. I'm think that my max would be around 500 (just to be safe). lol.

Also too, how in the world does the data validation on Sheet1 work. It's so cool. I mean I looke the Item (Data validation) and saw formula on the data validation window but it seems that it connection to the columns on the right but is really not linked. I tried to add another tab for a different name. I also changed the items on that new tab and copied those item names and put them on column R of sheet1 and tried to see a drop down for C4 of Sheet1 and did not see one. I guess my question, is how does the function work on the data validation for items. I like for the entries on columns N to R on sheet 1 placed in a new, separate tab.

I think I know what's going on with the function. One last explanation I need:

(IF(Sheet1!$C$5="Yes",INDEX(INDIRECT("'"&Sheet1!$C$3&"'!"&(LEFT(RIGHT(CELL("address",E1),3),1))&"12:"&(LEFT(RIGHT(CELL("address",E1),3),1))&"$100")

Can you please explain to me what's goin on here? I see word "Address" and thing like left, right, etc.


Thanks.

[Glenn Kennedy]

I assume I just need to change the 12 and 100 just in case my bottom section starts from a different row. I'm think that my max would be around 500 (just to be safe). lol.

Yes.

(IF(Sheet1!$C$5="Yes",INDEX(INDIRECT("'"&Sheet1!$C$3&"'!"&(LEFT(RIGHT(CELL("address",E1),3),1))&"12:"&(LEFT(RIGHT(CELL("address",E1),3),1))&"$100")

It's making the column increase by one, as the cells are copied over. In the "Category" headingmt eh formula had to be a bit more complicated to deal witht eh YES and NO options. The extra stuff is to leave A-E blank if No is chosen and for it NOT to return a 0, if there's no X in the box.

Data validation isn't too difficult. Can you upload your attempt at adding another sheet. I'll taker a look at what you did and then "talk" you through the process.

[managingcrap]

Here's the attachment. I'm trying to replicate what you did on the first two item sets because I have more than 10 tabs (names) to work with.

In addition, I'm trying to put the data set on the columns to the right of Sheet1 to another tab so all these data sets are in a different place.

Thanks.

I assume I just need to change the 12 and 100 just in case my bottom section starts from a different row. I'm think that my max would be around 500 (just to be safe). lol.

Yes.

(IF(Sheet1!$C$5="Yes",INDEX(INDIRECT("'"&Sheet1!$C$3&"'!"&(LEFT(RIGHT(CELL("address",E1),3),1))&"12:"&(LEFT(RIGHT(CELL("address",E1),3),1))&"$100")

It's making the column increase by one, as the cells are copied over. In the "Category" headingmt eh formula had to be a bit more complicated to deal witht eh YES and NO options. The extra stuff is to leave A-E blank if No is chosen and for it NOT to return a 0, if there's no X in the box.

Data validation isn't too difficult. Can you upload your attempt at adding another sheet. I'll taker a look at what you did and then "talk" you through the process.

[Glenn Kennedy]

OK. I have moved the validation lists to another sheet.

To add a new "user": do exactly what you did on sheet NATE.

Let's say the new user is Fred. Then add Fred's name to the next available cell in column B of "Master". Then add the list of stuff to the next available column in Master (column F).


NOW YOU NEED TO CREEATE A COUPLE OF NAMED RANGES. Select the top cell in that column (F1). Then click on the box that contains the cell reference (screenshot on Master). Overtype F1 with Fred and hit enter. If it has worked, when you will see the word Fred in that box when you select F1. Then select column F (click mouse on column header). Select that box again (it will say F1 again and overtype with FredCol.

Now... it will all work (with a bit of luck)... If you make a mess of it, hit CTRL and F3 and delete any named ranges that are goof-ups. If you find it easier, you can add named ranges from that dialogue box, to. Just use the same syntax as for the earlier ones.

So... you add another user and let me know how you get on!!

[managingcrap]

It worked. You are genius. A quick question: When I go to the cell C4 of Sheet1 and click the Data Validation on Data menu tab, I see this function:

=OFFSET(INDIRECT(SUBSTITUTE($C3," ","")),0,0,COUNTA(INDIRECT(SUBSTITUTE($C3," ","")&"Col")),1)

I wonder if you made this or if this is automatic. I would love to know how this function works. Thanks. Sorry for asking to many questions. I'm trying to learn and become better with excel.


I also tried to delete columns A-F from the Results tab and the results on columns G and to the right got messed up. What's with the function that does that?

This is my last question: What's with the (INDIRECT("'"&Sheet1!$C$3&"'!"&(LEFT(RIGHT(CELL("address",H1),3),1))&"12:"&(LEFT(RIGHT(CELL("address",H1),3),1))&"$100")
I see a bunch of lefts, rights, and cells, with precise values within such function. I wonder how it works?

Thanks.

[managingcrap]

Here's what I did with the spreadsheet. I changed two things. On the first spreadsheet, I added two columns after column F of each tab. The other one, I deleted columns A-F of the results Tab. Then, things got messed up. I know i did something wrong.

Thanks.

[Glenn Kennedy]

It's late evening here & I've had enough for a cold, wet Monday. I'll reply by the time you wake up tomorrow!! Basically, if you had deleted the cells and not deleted the rows, all would have been OK. By deleting the rows, the INDEX-MATCH got messed up. Why did you want to delete the categories?

[managingcrap]

It's late evening here & I've had enough for a cold, wet Monday. I'll reply by the time you wake up tomorrow!! Basically, if you had deleted the cells and not deleted the rows, all would have been OK. By deleting the rows, the INDEX-MATCH got messed up. Why did you want to delete the categories?

Unfortunately, I was given the wrong information. I was just told that no two contractors provide the same spreadsheet with the same format. This makes the function you made me not useful. Sorry about that.

I'm attaching the real format of the contractor's information (though I changed all the info but the format is still the same). I think what I need at this point is the contractor's name, the description and the amounts per year. It's ok if you don't work on this anymore. You have done more than enough and I appreciate what you've done with this work.

Thanks.

[Glenn Kennedy]

Nearly there. I had a day job to deal with!! However, i see that there is a difference in the layout between ContactorA and ContractorB. I have it set up for ContactorA at th emoment. Will the formats really be different? If not, which one is correct?

[managingcrap]

Whatever you just did is absolutely on the spot. Thanks again for helping me with this one.

Thanks. You're the best.