Ask Your Question

Lookup values two rows every time - macro [closed]

asked 2017-07-08 10:50:06 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Hi guys, i used to use EXCEL, now i have libreoffice - needles to say i am confused. Could you help me with this : I have a list of infos sorted in rows, and there should be created a table in distinct sheet for every row. Example names of the pupils, their marks, their age and birthdays.... I need a sheet for every pupil. I need to get info from D2 in the list to $M$3 in pupil sheet then for the next pupil i need to get D2+2 (D4) from the list to $M$3 in pupil sheet.

I would appreciate, if the macro will create new sheet, rename it with pupils name, lookup like 4-5 infos (all +2) and run some vlookups and other formulas. Thnks, N

edit retag flag offensive reopen merge delete

Closed for the following reason too subjective and argumentative by niabride
close date 2017-07-10 21:44:39.581611


"I need a sheet for every pupil. " Would you mind to tell why?
You seem to be a teacher, and I am a retired teacher who used Calc for collecting and evaluating data on sets of individual pupils ... And I never had a reason to create a sheet per pupil. Most of the use I made of spreadsheets in the context would have been extremely aggravated if I had entered data (marks e.g.) into sheets not containing a copy of the list of the complete set.
For print-outs I had extra sheets...

Lupp gravatar imageLupp ( 2017-07-08 14:31:35 +0200 )edit

Missed to tell:
I never had a reason to resort to "macro" programming for this kind of task.
There were some sheets, of course, reporting based on data from source sheets. This exclusively formula based.

(I also neither used filters nor pivot-tables in the context.)

Lupp gravatar imageLupp ( 2017-07-08 14:35:48 +0200 )edit

Thanks guys, but the pupils were only examples. There is a reason to submit every sheet separately. There are much more infos on the new "pupil sheets", and it must be printed one by one. I have 180 sheets to create and these 5 infos needs to be copied, because they are sources of another bunch of formulas that are active and fill the rest of the empty cells on the new "pupil sheet".

niabride gravatar imageniabride ( 2017-07-08 15:17:15 +0200 )edit

I still think there are misunderstandings concerning "good practice" in working with spreadsheets. I surely cannot help you in the way you expect. And will not be able to do it for you lacking enough time and positive understanding as well. Good luck!

(There are zillions of bad "solutions by Excel" probably. These mostly based on VBA "macros" as far as I can see, this way trying to ensure incompatibility with free software.)

Lupp gravatar imageLupp ( 2017-07-08 15:33:48 +0200 )edit

missed to tell: if you never had a reason to use sheet per pupil, nobody cant have a reason? i am surprised to hear this from a teacher. Please, if you cant answer, do not check in.

niabride gravatar imageniabride ( 2017-07-08 15:39:08 +0200 )edit

I didn't post an answer but a comment to get a clearer view of your problem.
I will do so also in the future without asking for you permission.

"if you never had a reason to use sheet per pupil, nobody cant have a reason?"
Of course not. There are, however, a few decades of experience with spreadsheets including some applications mimicking databees in a sense. You needn't assume I'm just quacking. I intended good advice. Anyway you are free to ignore it.

Lupp gravatar imageLupp ( 2017-07-08 16:07:07 +0200 )edit

Even if good intention, you haven't helped a bit. If the whole story behind my request is necessary, I apologize, I thought this is a place where you can ask for solutions, not for chat.Your decades of experience are driving you to come out with reason why it shouldn't be done instead how can it be done. I am seeking for somebody, who is capable to tell how it can be done. Thank you for your intentions, I will be glad to see you another time

niabride gravatar imageniabride ( 2017-07-08 16:55:54 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-07-08 13:34:46 +0200

m.a.riosv gravatar image

I think it's not a very good idea split data with a sheet per pupil, specially copying data from a source table.

The better it's that seems you have all data in a table and there are tools to work with it.
·Filters are one, allowing send the filtered data to other sheet. Applying fiters
·But IMO the best option it's the Pivot Table, can filter the data and show it on a vertical or horizontal form and make some calculations on fields like count, sum, average. Pivot table

edit flag offensive delete link more


thanks for your help. Could you please help me with something else : give me an example of a macro which - by every turn - will increase row value for selected cell?

niabride gravatar imageniabride ( 2017-07-08 15:20:08 +0200 )edit

answered 2017-07-09 09:19:32 +0200

librebel gravatar image

Aright, here's an example to give you a start:

Sub Example_Calc_Macro()

    Dim oSheets As Object, oSheet As Object, oNewSheet As Object
    Dim oSource as Object, oTarget as Object

    REM get all Sheets from the current Calc document:
    oSheets = ThisComponent.Sheets

    REM get the current Sheet:
    oSheet = ThisComponent.CurrentController.ActiveSheet

    REM To insert a new Sheet at the end:
    oSheets.insertNewByName( "PupilName", oSheets.getCount() )
    oNewSheet = oSheets.getByName( "PupilName" )

    REM Copy cell D2 from the old Sheet into cell M3 in the New Sheet :
    oSource = oSheet.getCellRangeByName( "D2" ).getRangeAddress()
    oTarget = oNewSheet.getCellRangeByName( "M3" ).CellAddress
    oSheet.copyRange( oTarget, oSource )

    REM etc.
End Sub
edit flag offensive delete link more


@librebel: As I highly appreciate you contributions I would like to explain my intentions commenting on the OQ. You may look into this recent thread in another forum to see clearly what I try to avoid when answering questions asking for a "macro" e.g. There the OQer thanked for my "Why not simply save the doc with the amputated sheet?" as the solution for his needs.
See also:

Lupp gravatar imageLupp ( 2017-07-09 10:52:45 +0200 )edit

Thank you for your comment and links @Lupp,

When sometimes a user is asking for too many things in one question, in that case i take the liberty to handpick and address only one of the things requested. Then the user can start from there, and make an attempt to figure out the rest by themselves ( by searching fora such as this one ), and then perhaps later post an additional question when they get stuck again.

librebel gravatar imagelibrebel ( 2017-07-10 01:53:46 +0200 )edit

Also since most problems can be solved in several different ways, notably WITH macros or WITHOUT macros, it is up to the personal taste of the user which solution they want to adopt in their case.

While the original questioner might eventually decide to adopt another answer than mine, there are still other users who could stumble upon it and take anything which they find useful from there.

librebel gravatar imagelibrebel ( 2017-07-10 01:55:31 +0200 )edit

Let us file this under "peaceful dissent". I just cancelled a related (already sketched) comment on another answer by you (question by ggatlanta).
My underlying experiences as a teacher (> 40 years) and as a contributor to forums as well look, however, too convincing to me to change my attitude insofar.

Lupp gravatar imageLupp ( 2017-07-10 10:14:51 +0200 )edit

Thank you for your intentions.I have done it manually, it took me only 7 hours.

niabride gravatar imageniabride ( 2017-07-10 21:31:33 +0200 )edit

Question Tools

1 follower


Asked: 2017-07-08 10:50:06 +0200

Seen: 88 times

Last updated: Jul 09 '17