INDEX and MATCH or LOOKUP formula.

I need a formla that will automatically display the date to the corresponding person in "F" column (Date recieved column starting at "F7")after I manualy input the data into "I" column (Date colum starting at "I7")

The first 3 entries in the cells "F11", "F12", "F13" in the DEMO file are examples of what data I need appear.

I did my best to get a formula, but I am stumped.

TIA

Hello Dave, long time no see.

try in F11 and copy down

=INDEX($I$11:$I$25,MATCH(B11,$J$11:$J$25,0))

This does not include error checking for missing values, so you'll see #N/A if the name can not be found. Let me know if you want that handled differently.

cheers

Dave, I'm not quite sure I follow the latest attachment.

If you have manually inputted data, it would be best to organise it as one contiguous table, with columns for Name, Date, Amount.

Then, on a separate sheet, build a pivot table off that data and filter by month. That would be the most efficient way. It does not require any formulas, just a few clicks. The only requirement is that your data entry table is contiguous, i.e. there are no blank rows or columns.

I've copied your "manually entered" data into Sheet 3 and built a pivot table. You can set the filters on the fields to show just one particular month in one particular year.

If you want to include account number and code in the pivot table, you can insert columns for these values in your manual data entry table. You will need a master table somewhere, where you store name against account number and code. In your manual data entry table you then perform a simple lookup to fill these columns and you can include them into the pivot table.

see attached (without the account number and code)

Dave,

keep data entry and data reporting separate.

Have your manual data entry on one raw data sheet. Use that raw data table for your summarising and lookups.

For printing and reporting purposes, have a reporting sheet laid out with however many tables next to each other, gaps and all, by simply referring to the data in the raw data sheet.

Don't mix data entry and data presentation/reporting on one sheet.

Dave,

if you have data on sheet Raw, let's say 100 rows, and on sheet Report you want to show that data in groups of, say, 10 rows, then use a formula like

=Raw!A1

copy down to another nine rows and copy across for as many columns as you need.

Then skip a row and continue with

=Raw!A11

etc.

Agreed.

If you can describe a logic behind the pattern, it will be possible to come up with a formula that does the table populating without major interaction from your side.

Maybe time for a new thread: "I have 1500 rows of data and need to display them in nice, little, separated tables of 15 rows by 3 columns, 5 tables across, 10 tables down, separated by two rows and two columns of blanks." Or some such.

There is a single formula for that, which can be copied across and down. You just need to come up with the rules.

See what I mean?

Teylyn, I have posted two files that I need to have setup in that way. Let me know if you don't understand where the data needs to be.

In DEMO 4 file, I have colored the area yellow that needs to have formulas. DEMO 3 is pretty straight forward.

And what do I do with these?

What is the source? what is the target? where is the pattern? where is the data?

And please don't post files of half a MB with nothing in them. There goes my data plan cap again .... For nothing.

Sorry, I did realize it was that big, if I delete them will that help with the data plan cap? I am not doing this on purpose, so I will apologise again.

I will repost the first file because that is the one I am really needing at the moment.

I entered the formulas and you should be able to follow how it needs to flow. I am taking your advise of making a raw data sheet and have it automatically appear in the sheet that needs to be printed out. The pages go in order from 1 to 4 and so on. I hope this makes it clearer.

if I delete them will that help with the data plan cap?

LOL, no, but don't worry. If it gets too big, I won't download it.

waiting for you to upload...

Thanks Teylyn, I will be much more careful in future.

Here is that file.

As I said earlier, I entered the formulas and you should be able to follow how it needs to flow. The pages go in order from 1 to 4 and so on. I hope this makes it clearer.

do you really need columns F and H (P and R, respectively)? If you need more space between cell values, you can use formatting. Or are F and H required for some other reason?

OK. Let me think for a while. It's late Fri night. I'll be back but not very soon.

Hello Dave,

see attached for a formula that chunks up your data into bits of 30 rows with column title and two sets next to each other.

It's one formula, starting in C2, copied across to G2, leaving out H2:J2 and applying the same formula again in K2:O2.
I used custom formats for each column to suppress the zeros.

Looking at the last file you sent, I believe you want this for printing. What I don't understand is why you don't just print off the original list and set the print titles to be repeated on each page. It seems that the chunk of data in column K and to the right will go onto page 2 anyway.

Can you explain why you need this layout?

cheers