Excel - Help Please! Drop Down and Data Filling

Solved/Closed
CatherineGPR
Posts
4
Registration date
Thursday February 19, 2015
Status
Member
Last seen
March 5, 2015
- Mar 4, 2015 at 07:23 PM
 CatherineGPR - Mar 13, 2015 at 01:27 AM
Good morning/afternoon.

I was hoping you would be able to assist me with a query please.

Currently my team is using 2 spreadsheets, one that lists a publication with a figure.

Another sheet we use to report on items we get in a publication and manually fill in the corresponding figure.

Is there a way I can use an excel function to have these 2 sheets speak to one another, firstly by offering drop down options for publications and secondly for the figure to be automatically filled in the next column when the publication name is chosen from the drop down.

Thank you for your assistance.

Catherine





1 reply

cmarzahn
Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
7
Mar 5, 2015 at 08:58 PM
And good afternoon to you...

I think I have an idea what you want. A couple of questions. Can the spreadsheets be in the same workbook? Are there likely to be multiple placements in the same pubs?

Here's a link to a sheet on DropBox that should give you an idea of where you might go...

https://www.dropbox.com/s/7ku36tb2l8lca74/PR%20Book%20Test.xlsx?dl=0

Hope that helps.
0
CatherineGPR
Posts
4
Registration date
Thursday February 19, 2015
Status
Member
Last seen
March 5, 2015

Mar 5, 2015 at 09:20 PM
Good afternoon cmarzahn,

Thank you very much, this is exactly what I am after, it is very helpful. I was not sure if VLOOKUP was the correct function but it appears it is. :)

How make the drop down items on the Pubs with Lookup tab?

Thank you once again, this will save our team lots of time!

Catherine
0
cmarzahn
Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
7
Mar 7, 2015 at 10:57 AM
VLOOKUP is one way to solve it but it does have limitations. If you don't have an exact match or if the value occurs twice may require some additional work.

To add more lookup cells just copy it down the colum. Be careful of the range of data on the first page changing and not updating formulae on the other page. A better way to do it might be to use range names for the List.

I've included a link to "RevA" which illustrates that thought.

https://www.dropbox.com/s/kawrvk4edivpnkm/PR%20Book%20Test%20RevA.xlsx?dl=0

Enjoy!
0
Than you once again! It is such a help
0