what lookup function should I use?

[suraiya]

Hello,

I am trying to create a tracker report using lookup function to create a list of each income gift recived for each reference number, I would like to group each gift by person and month also but I am not sure how. I have attached an example tracker of what I want it to produce, I want to be able to just paste the monthly data in and for each tab to automatically populate itself.
Is it possible?

Thank you

Suraiya

[Olly]

You can easily use a pivot table. See the attached.

[suraiya]

Thanks OllyXLS, I forgot to say without using a Pivot table.

[Richard Buttrey]

Wouldn't a Pivot table be much easier and also offer you a lot more flexibility in reporting by Reference, Name, Constituent ID, date, etc.

See attached

[Olly]

Thanks OllyXLS, I forgot to say without using a Pivot table.

Why don't you want to use a pivot table?

[suraiya]

One is because of the size of the file, as there will be over 20 - 30 difference references at a time, and secondly when a new reference is add wouldn't it be easier to just to enter the reference at the top and for it to populate itself.

[hemesh]

The names in your sheet are constant or may change.

[Olly]

One is because of the size of the file, as there will be over 20 - 30 difference references at a time, and secondly when a new reference is add wouldn't it be easier to just to enter the reference at the top and for it to populate itself.

Sorry, your reply doesn't make any sense to me. The pivot table solution works.

If you already know an easier way, use that.

[suraiya]

The Income Data sheet is contant, the other may change - ie each reference is sheet

[suraiya]

No worries OllyXLS, sorry if it doesn't make sense. Thank you though

[canapone]

Hi All,

in the attached file, I'm using array INDEX to filter first 999 records (rows 2-1000)

I'm a pivot table supporter and array formulas are not probably the most convenient way to get the lists.

All the formulas are to be confirmed with control+shift +enter: in the example you will see all the formulas embraced in curly brackets,

I hope that helps


-------------

Edit: 9999 rows

[suraiya]

Thank Thank you Canapone! Thats great

[canapone]

Hi,

thanks for your kind feedback. If you'd spot some strange outcome, firstly check if formulas are correctly confirmed (again: {formula}). Array formulas are often touchy.

Regards

[Richard Buttrey]

Hi,

It seems that you have perhaps not really studied the Pivot tables you've been given.

The size of the file is irrelevant, and in fact by using a separate sheet for each reference you're only making the file bigger than one with just the PT.

What could be simpler than just selecting a new reference from the drop down cell at the top of the PT?

Don't be put off by PTs if you've never worked with them. Invest half an hour of your time and you'll improve your efficiency 10 fold. I remember the first time I showed somebody how to create a PT, it took around 5 seconds, and when I demonstrated how to dice and slice it they were blown over. A reporting job that was taking them at least 2 hours they could now achieve in minutes.

[suraiya]

Hi Richard,

Thank you for your repsonse, I am happy Using Pivot infact use them on a daily basic. This Tracker is something I have just inherited from a collegue who produced it for a director who doesn't want the format to be change ! I agree Pivots are much more efficient and time saving! Pivot Tables rule, but I think case I was avoiding having a pivot a page!

Thanks Everyone!

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.