Autofill formula for flashcards

[Marco-Kun]

Hello everyone,

I tried making flashcards in Excel. The first tab has the front and the back of the cards.
The second tab has the template for the flashcards. I added 3 examples on the first tab.

I have difficulties adding the formulas referring to the input sheet. Whenever I copy the formula, it doesn't give the output I want.
I could manually put the formulas for a page or 2, but it would be a pain to do this for a lot of pages.

Is there an easier way to refer to the input sheet, so when I copy the templates for more pages, the formulas refer to the correct input cell.

I hope this makes any sense. :P

Thanks in advance,

Marco

[JohnTopley]

Used a VBA macro (not least because you can't copy formulae into merged cells)

Please Login or Register  to view this content.

Click RUN on "Input" . The output is sheets "Front" and "Back"

[Marco-Kun]

Dear John,

Thank you for your reply.
The formula you used actually works, right? As soon as I copy the cell with the formula to another cell, it gives me the right output, the next input in line.
EDIT: Actually, nevermind. I think this only works for the first page, as soon as I copy another set of Front and Back cards, it doesn't work.

As for the VBA, that works too, except that the cards on the back of the flashcards needs to be in reverse order, or else they won't align with the cards on the front when you print double sided. That brings me to the next question, can I print them double sided when they are on different tabs?

Kind regards and thanks for your time,

Marco

[José Augusto]

Hi Marco

I think you do not need to have 8 rows and four columns for each flash card.
I also think you do not need to have the front and back on the same sheet.
If so, you can use the following formulas:
For sheet front:

Please Login or Register  to view this content.

For sheet back:

Please Login or Register  to view this content.

See the file Flashcards.xlsx for clarification.

[Marco-Kun]

Dear José,

Thanks for your reply.
Yeah, that's an option too, to just make the cells bigger. Thanks for that.
As for the formulae, now the cards have the same 3 cards on each row; "Example 1a, Example 1a, Example 1a".
As for the back, they need to be in reversed order, like I mentioned in the last post, for printing double sided, so they align.
Can I do that when they are on two different tabs?

Kind regards and thanks for your time,

Marco

[JohnTopley]

Change this line in VBA

ws3.Cells(rr, c) = .Cells(r, "B")

to

ws3.Cells(rr, 10 - c) = .Cells(r, "B")

[JohnTopley]

Reprinting double side: usually works by printing 1,3, 5 etc and then re-inserting in printer to print 2,4 ,6 so having separate sheets worked. Never tried it with Excel!

[José Augusto]

Dear José,

Thanks for your reply.
Yeah, that's an option too, to just make the cells bigger. Thanks for that.
As for the formulae, now the cards have the same 3 cards on each row; "Example 1a, Example 1a, Example 1a".
As for the back, they need to be in reversed order, like I mentioned in the last post, for printing double sided, so they align.
Can I do that when they are on two different tabs?

Kind regards and thanks for your time,

Marco

Yes you Can
Use this formula to back sheet

Please Login or Register  to view this content.

To print you can group both sheets.

[Marco-Kun]

Thanks for your reply John and José,

@John
The change in VBA works. Still not sure how to print it double sided.
The first A4 page on the Back sheet needs to be printed at the back of the first A4 page of the Front sheet.

@José
The formula works for the back now; the front formula shows the same result though.

Kind regards,

Marco