Drop Down Menu result fills cell on other sheet?

[kae2346]

Hi,

I have attached an example of what I need help with.

On Sheet2 there are headings (see C4:K4) that match the drop down menu on Sheet1. I am looking to have the cost 3 columns to the right of the drop down menu (See AL2 & AO2 on Sheet1) show up in the corresponding column on Sheet 2.

So when I select a different option in the drop down menu on Sheet1, I'd like that amount to change to the corresponding column on Sheet2. The other cells in that row should remain blank if there is no value.

I think I need a formula in each cell in the row on Sheet2 under each heading (see C5:C19 on Sheet2) saying something like if the heading doesn't match what's in the drop down box then leave blank.

I don't know how to make a formula that pulls the cost into the correct cell on Sheet2 after matching the drop down menu selected on Sheet1 to the corresponding heading on Sheet2.

I am using my Mac now to post this so I hope the attachment from Excel (Mac version) will work. If it doesn't I'll repost form my work PC in the morning.

[pr4t3ek]

Can I please confirm that the your range that you would like to lookup is fixed to "AL:FF"

Thank you

[kae2346]

The range AL:AP on Sheet1 would match up with row 5 on Sheet2, AQ: AP on Sheet1 would match up with row 6 on Sheet2, and so no. I hope that helps.

Thanks.

[pr4t3ek]

look at the attached for row 5, if this is correct, look at the formula and you will then understand how to do this. you will just need to change the range for row 6.

[kae2346]

That does exactly what I need it to do. Thank you!!!!

[kae2346]

After looking at this, that works for that row on Sheet1. But on Sheet1 I will be entering data (up to 500 rows worth) and your solution doesn't seem like it will work as I fill in more data below row 2 on Sheet1. Sheet2 is based off vlookups for the rest of the information other than cost so I can print the expense report with all the data in the correct places.

So the correct question is how does this work if there are multiple rows of data in Sheet1?

[pr4t3ek]

Sorry, I assumed you would have had a look at the formula and then been able to replicate it for rows 6:19 when I said

look at the attached for row 5, if this is correct, look at the formula and you will then understand how to do this. you will just need to change the range for row 6.

Please find attached.for Rows 6:19, if you add more records, just drag the formula's down

[kae2346]

I apologize. I think I explained this wrong.

I understand that I need to change the formula for the appropriate cells on Sheet2, but that will still only ever give me the results from row 1 on Sheet1.

The data in columns A, B, and M on Sheet2 changes based off a vlookup using column A on Sheet1 as a reference so C5:K19 on Sheet2 should do the same on Sheet2.

To summarize, Sheet2 is used as a printout for specific orders based off the information entered in the rows on Sheet1, so the data on Sheet2 will change based on which ID (column A from Sheet1) is referenced in the vlookup.

[pr4t3ek]

Right, that makes more sense...

Unfortunetly, I do not think this is giong to be possible given you're data in Sheet1, can this be modified in anyway? Why not use an access database (at the least) to manage such records instead?

by the way, in sheet2, a:b vlookups will not work either. as vlookups only get the first instance of a 'find'...

apologies for not being able to assist any further,.

[JBeaucaire]

If you remove the non-working formulas from sheet2 and manually mockup the results desired, perhaps something will come to mind. I need to see the actual end goal with complex layouts like this.

[kae2346]

With pr4t3ek's help I seem to have solved this.

See attached.

If I change the drop down menu category on the first sheet it automatically puts the corresponding amount in the correct column on the third sheet.

Thanks for the help!!!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.