Lookup values two rows every time - macro

English
, , ,
#1

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

#2

"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…

#3

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.)

#4

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”.

#5

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.)

#6

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.

#7

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.

#8

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

#9

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

#10

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?

#11

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
#12

@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: How To Ask Questions The Smart Way

#13

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.

#14

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.

#15

@librebel:
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.

#16

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

#17

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.