Ask Your Question
0

[SOLVED] Retreiving data from different column every month [closed]

asked 2019-02-21 17:07:38 +0100

ballie gravatar image

updated 2019-02-21 21:46:59 +0100

Hi everyone,

I've created a worksheet in calc to manage my expenses. After trial and error I decided to add a matrix in my sheet with one column for each month, containing the amounts to be paid. I'm trying to get the amounts for the current month in the colunmn under "Amount", but I just cant seem to get the formula right.

Can anyone tell me what I'm doing wrong here?

I've added a sample file with sample (fake) amounts. The salary dates are (in my case) once every four weeks) So that's the reason that in january there are two paydates and I've entered a higher salary. Change the date to a week later, and that second date will appear in another month. The amount however is fake. Me real income is only for me to see ;)

To be complete: My Native language is Dutch, and so is my edition of LibreOffice. I'm running LibreOffice under Kubuntu Linux version 18.04

C:\fakepath\Voorbeeld Maandlasten.xls

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by ballie
close date 2019-02-21 21:47:24.703362

3 Answers

Sort by » oldest newest most voted
0

answered 2019-02-21 18:57:17 +0100

Opaque gravatar image

updated 2019-02-21 19:02:40 +0100

I do not understand what you want to achieve and do not get the idea behind the numbers in column "Maand" and why do you need in "Amount" numbers, which are already inr you matrix? The only thing my gut feeling tells me, that you have one serious problem, which is that you didn't fix the range in your vlookup ( which should read Z$6:AL$24 - see the "$") and second you might in fact searching for HLOOKUP the month in matrix and search down (instead of VLOOKUP and search to the right).

I assume you need something like this

=HLOOKUP(TEXT(TODAY();"MMM");AA$4:AL$18;ROW(A6)-3;0)

This to work requires you set the matrix headers to

=TEXT(D3;"MMM")

(example is for cell AA4). Hope that helps - please see my thoughts in C:\fakepath\Lasten.xls

edit flag offensive delete link more

Comments

Caveat, anything TEXT(...;"MMM") depends on the current locale, month names change with different locales, so the calculation would fail.

erAck gravatar imageerAck ( 2019-02-22 16:52:28 +0100 )edit

Thanks and sure - but in fact the point is HLOOKUP instead of OPs efforts with VLOOKUP

Opaque gravatar imageOpaque ( 2019-02-22 19:25:11 +0100 )edit
0

answered 2019-02-21 21:44:50 +0100

ballie gravatar image

Thank you erack and Opaque. I guess I just got tunnelvision in trying to get VLOOKUP to work. Never even looked at the other one.

I've incorporated Opaque's solution and it worked perfectly. So a big thank you to you...

edit flag offensive delete link more

Comments

Please mark @Opaque answer as accepted then. You have closed this question as answered, while no answer is accepted. Thanks in advance.

SM_Riga gravatar imageSM_Riga ( 2019-02-21 22:59:52 +0100 )edit

Also, please don't add Thank Yous as answers, which they are not. Use add comment instead. Thank you ;-)

erAck gravatar imageerAck ( 2019-02-22 16:49:37 +0100 )edit
0

answered 2019-02-21 19:09:49 +0100

erAck gravatar image

updated 2019-02-21 19:29:29 +0100

Three things:

  1. VLOOKUP() without a 4th argument (Sort order) or an argument not equal to 0 or FALSE expects the lookup range to be sorted, which it is not, the result then is arbitrary.
  2. Copying down the VLOOKUP() formula in C6:C18 you made the mistake to not use absolute row references for the lookup range (and probably some other editing error as well, looks odd), resulting in shifted references like =VLOOKUP(Z8;Z7:AL26;MONTH(TODAY())) in C8.
  3. VLOOKUP() looks up the given value in the first column of the given array/range, with your =VLOOKUP(Z6;Z6:AL24;MONTH(TODAY())) in C6 you always lookup Z6 in range Z6:Z24 and then return a value from another column. While this is ok (except the missing 4th argument 0 mentioned above) in this case, it is unnecessary. You want an offset of month within the matrix instead, so =OFFSET(Z6;0;MONTH(TODAY())) in C6 and copy that formula down.

Hope that helps.

edit flag offensive delete link more

Comments

Also, it probably would be better / easier to overview to use the titles like Insurance and Mortgage also in the planning matrix as first column under Maand instead of the lookup numbers, then a VLOOKUP() would even make sense again like =VLOOKUP(A6;Z$6:AL$18;MONTH(TODAY());0) in C6. Note the absolute $ row reference to be able to copy the formula down and 4th argument 0.

erAck gravatar imageerAck ( 2019-02-21 19:24:13 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2019-02-21 17:07:38 +0100

Seen: 65 times

Last updated: Feb 21