LinkBack URL
About LinkBacks
Hi I'm trying to populate a sheet with data from several sheets.
For example cell B3 contains 'Perth' in suburb sheet and I need to find the corresponding column name among several sheets and populate that column of data in my suburb sheet.
Find string and get column data
Hi I'm trying to populate a sheet with data from several sheets.
For example cell B3 contains 'Perth' in suburb sheet and I need to find the corresponding column name among several sheets and populate that column of data in my suburb sheet.
I don't see a worksheet demonstrating your scenario.
Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
as requested see attachment
I placed this macro into the sheet module for SUBURB:
Please Login or Register to view this content.
You need type anything (properly spelled) into row1 and if a match is found on another sheet it will appear in that column. If you delete anything in row1, that column will clear.
Thanks JBeaucaire, in the near future each city will be divided into Summer, Autumn,Spring and Winter. For example
A1 = Summer
A2 = London
B1 = Autumn
B2 = London and so on.
How can I pickup the data for London in Summer?
I don't know. That is always dependent on the actual layout of the data and the summary sheet.
If your needs change and you can't figure out how to edit the macro, then you'll have to post a new workbook with a new example.
Please See example 2. Could it be possible to search by multiple strings like London + Summer?
The same document and macro provided earlier works for this. Do not use two cells to type in that's not less work, it's more. Just type Summer London at the top of a column and it will retrieve the Summer London column titled the same way from another sheet.
I tweaked it just a little for elegance, but it's still basically the same.
I also added a sheet called LISTS where you can simply list your options and then they will appear on the summary sheet row1 in the drop down. Keep the list sorted.
I'm using this formula to =CONCATENATE(A1," ",A2) to have one header (London Summer). Can the list automatically pick up the headers in all sheets?
Use the sheet I uploaded, it's much simpler to make choices on the SUMMARY page row1 and it's simpler to maintain.
Looking at your last attachment,have you created the header manually.Originally Posted by JBeaucaire
It works well if the data set is small but I have heaps of data sheets. So the list can be pickup all the concatenated headers.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks