PDA

View Full Version : Fetch Data automatically in a single cell



krishna2221
10-26-2013, 11:03 AM
hello forum members.
i have truck running data in km book sheet and outward book sheet as attached. i created a pivot table with sorted data in sheet8. next to pivot table i want to fetch data of column 1 in km book sheet after filtering and sorting data based on vehicle number and km the vehicle ran. i did one myself manually but its too complex to do. i also recorded macro for reference what to filter. after filtering i just entered data in cell i wanted to. but i want a vba code for this. how will i be able to know where my trucks operated between the two kilometeres in pivot data table. PLease help...

rollis13
10-26-2013, 01:13 PM
Cross-post:
http://www.mrexcel.com/forum/excel-questions/735274-fetch-data-automatically-single-cell.html
Fetch Data automatically in a single cell (http://www.excelguru.ca/forums/showthread.php?2290-Fetch-Data-automatically-in-a-single-cell)http://www.vbaexpress.com/forum/image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABEAAAAQCAYAAAGHNqTJAAAACXBIWXMAAA7DAAAO wwHHb6hkAAACr0lEQVR4nH1TS0xTQRQ9M320r7U/CoGilCCIWB4KXcjChV24wS1xRcLSpS6MJsQVK0ggoC7c6Mboho3iJ6CJG6KJ0RWihj/StCnFSin2S9v33jjv9QME8SSTO3Pn3M/ce4eA4/6MxBhBPxl9Ic3KedUvGAnI0BCos0dShPa 80o6wUA07suVyyy0sYtkvACThYBqyt2dMByuFBpbc8jvlZjc6w4X1dqejr WIpmEWq0ZeFqtoCebqVu7qTKqyOcyoIEl9apmb3VkUciliz40PPvUy1wNBAU1WVbBQESsLkQgjE 5J3V0XOuf 5FYx/yULo4lwdwzuJiOszgxUuZTNw/de5qhhCK4QWKyAxc6wvUnQdJYh/JPuhxue9D6ta0wPGAysEk5VCX6FTuxUSA9mOm8zsDEcRkwnTLyRRvw9/YOh1NtDt3XiJZB7U91OX5c/Hit8xvJcAu0 uy4pJWjrskEwmoVYdO9jxWrla0KXLZ0iFKUAwe4iVJELFQLjeTpqKNKJPEStI5vB1GONUCZpT0z uqogEFF2nJzn6/JzibspT/AO6MvN9qSoaFlD2VF6xLcN sTRoc K4KD2pqzk94Gvrg0mw/cupjoKSxWLwHQLh f1iTkxLvWKVddonXaFbmQ9gUI91UIaBmLC GC1mMv7K 6jZI10X7Rlk5WipbGld2qoFiGaK35t5/Uy4xakWERabQT8HljMQxqak2TMtXn OriO1dzR6Mi7zVdzXNhh4uTVjldejyGVc8EqRm2sbC3NuDyghR3wUs3Hy6nLb7YiCeFSBUQTqPU Wyyp3QO9d fKOKsSa0hq1sSsbBPprMjP8wGc5amT9J4c1mfCKALH9pmadlcij28GTHiMmsDLrqczguq4PQRz0 o4ghVa7OxveMWH/u7KH3r/yDGGLvxF30tKs p7Df9AAAAAElFTkSuQmCC
Fetch Data automatically in a single cell (http://www.excelforum.com/excel-programming-vba-macros/964056-fetch-data-automatically-in-a-single-cell.html)http://www.vbaexpress.com/forum/image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABEAAAAQCAYAAAGHNqTJAAAACXBIWXMAAA7DAAAO wwHHb6hkAAACr0lEQVR4nH1TS0xTQRQ9M320r7U/CoGilCCIWB4KXcjChV24wS1xRcLSpS6MJsQVK0ggoC7c6Mboho3iJ6CJG6KJ0RWihj/StCnFSin2S9v33jjv9QME8SSTO3Pn3M/ce4eA4/6MxBhBPxl9Ic3KedUvGAnI0BCos0dShPa 80o6wUA07suVyyy0sYtkvACThYBqyt2dMByuFBpbc8jvlZjc6w4X1dqejr WIpmEWq0ZeFqtoCebqVu7qTKqyOcyoIEl9apmb3VkUciliz40PPvUy1wNBAU1WVbBQESsLkQgjE 5J3V0XOuf 5FYx/yULo4lwdwzuJiOszgxUuZTNw/de5qhhCK4QWKyAxc6wvUnQdJYh/JPuhxue9D6ta0wPGAysEk5VCX6FTuxUSA9mOm8zsDEcRkwnTLyRRvw9/YOh1NtDt3XiJZB7U91OX5c/Hit8xvJcAu0 uy4pJWjrskEwmoVYdO9jxWrla0KXLZ0iFKUAwe4iVJELFQLjeTpqKNKJPEStI5vB1GONUCZpT0z uqogEFF2nJzn6/JzibspT/AO6MvN9qSoaFlD2VF6xLcN sTRoc K4KD2pqzk94Gvrg0mw/cupjoKSxWLwHQLh f1iTkxLvWKVddonXaFbmQ9gUI91UIaBmLC GC1mMv7K 6jZI10X7Rlk5WipbGld2qoFiGaK35t5/Uy4xakWERabQT8HljMQxqak2TMtXn OriO1dzR6Mi7zVdzXNhh4uTVjldejyGVc8EqRm2sbC3NuDyghR3wUs3Hy6nLb7YiCeFSBUQTqPU Wyyp3QO9d fKOKsSa0hq1sSsbBPprMjP8wGc5amT9J4c1mfCKALH9pmadlcij28GTHiMmsDLrqczguq4PQRz0 o4ghVa7OxveMWH/u7KH3r/yDGGLvxF30tKs p7Df9AAAAAElFTkSuQmCC

p45cal
10-26-2013, 02:30 PM
and http://www.techsupportforum.com/forums/f57/fetch-data-automatically-in-a-single-cell-737930.html
http://www.thecodecage.com/forumz/microsoft-excel-forum/214394-fetch-data-automatically-single-cell.html

snb
10-26-2013, 02:40 PM
and
http://www.thecodecage.com/forumz/microsoft-excel-forum/214394-fetch-data-automatically-single-cell.html

krishna2221
10-26-2013, 06:41 PM
a

p45cal
10-27-2013, 02:23 AM
aWhat's that about?

The code to unlock the vba code for the above file does not seem to be unlockvba!
I'm not a member of the forum where you have stated this password and so cannot download the file from there.

p45cal
10-27-2013, 05:32 AM
OK, I got the password to work. See attached where there's a Macro4.
It's very basic. It will go wrong; there's a fair bit of hard-coding and many assumptions made, but since "now please help me.....the matters a little urgent... " it should get you started.
Clicking the button on Sheet8 will run it.
It doesn't delete the prior results, only overwrites them , so if the previous pivot table used more rows than the newer one, the extra rows of results won't be deleted.
I've added code to the pivotTableUpdate event handler to run the macro, so you don't have to click the button every time the pivot changes.