Sunday, September 30, 2012

Import data from one sheet to another

Question:

( by Frank Bernard )

I am building a expense report. I also just downloaded an app that I can keep track of my expenses.  This app exports csv files and I would love to be able to copy and paste this data in to on of the sheets in my google document. With that I would love for the spreadsheet to sort everything out. I have different expenses that I have on another sheet that I separate in to different tables according to the expense.  I was wondering if there is a way to filter that data to the according table.  To be more specific with my answer I have two sheets. CSV file come with the headers Category, Source, Expense, Date, and Amount.  The other sheet I want to filter the data has only the headers Source, Expense, Date, and Amount. No Category. The table already contains that and there is no need for it. I have spent countless hours trying to figure this out with no luck. If somebody could let me know if this can be done and how I would be very grateful. If it can't then I know to stop wasting my time.  Thank you in advance.


Solution:

Here is the screenshot of Sheet1:




Now in Sheet2 type the following formula in Cell A1:
=ArrayFormula('Sheet1'!B:E)

It will populate your Sheet2 with Columns B to Column E.


Here is the screenshot of Sheet2:







I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,
Kishan,


2 comments:

  1. Hey thanks for that. That answers half my question, I guess I should have read over my question and been a little clearer on the next part. So I have another sheet where I have different tables depending on the category it should go to that certain table. I don't know how to post screen shots so I'll try to paint a picture best I can. Sheet 1 has different tables separated in to different categories. One would have let's say category "a" on one row. Next row would be divided in to 4 different coloumns labeled source, expense, date, amount; followed by data underneath. Sheet 2 would contain row one being a header with 5 different coloumns labeled category, source, expense, date, amount; followed by data underneath. So if I have 5 different tables labeled a, b, c, d, e I want all category a to go to table a, b to b, ect, leaving the category coloumn out. Sorry if this seems complicated but I'd be extremely grateful if I could make this happen. Thanks for your help and thanks in advance.

    ReplyDelete