Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

reading values every 2 column

Hello all,


I’ve been through the formulas and functions directory to solve my problem but I couldn’t write the thing correctly using the references functions.


I have about 20 rooms (and more to come), and each room is provided with different water meters.

At the moment i read only one meter in each room. In the future i might have to read several in each room.


What I did is:

  • each column is a room; name of the room in the head row
  • Under the name of each room are 2 columns: 1 for the global meter of the room, one for the specific meter
  • rows are months


What I wish to do is:

read the value of specific meter in each room and work it with price unit to fond out the cost in the Cost Table.

In my case it means:

-go and read the value in the Specific Meter column and insert it with computation in Cost Table.

-do it again in the next room (C+2 column) and put it in the C+1 Cost Table.


I tried to use the LOOKUP function, ADDRESS, IMPAIR, etc, but I couldn’t find my way to extract a value every 2 column from one table and insert it in every column in a new table.


If anyone is familiar with this, I’m all ears…


Thank you very much for taking time to read me.


Lionnel


PS: Since 1 room can contain several meters, and therefore columns, It might be smarter to set Rooms in rows and months in columns ...?



User uploaded file

iMac, OS X El Capitan (10.11.3)

Posted on May 6, 2016 2:05 AM

Reply
Question marked as Best reply

Posted on May 11, 2016 4:40 AM

Hi lionnel,


Your merged cells as column headers are probably complicating things. Numbers does not do well with merged cells.


If your meters were labeled Global1, Meter1, Global2, Meter2 these would be easy to search for. You might need to do Meter1.1, Meter1.2 if multiple meters in a room.


I like using INDEX MATCH for my lookup needs. If you have specific questions aabout applying this to your actual report table, just give more specifics as to waht you need.

User uploaded file


quinn

7 replies
Question marked as Best reply

May 11, 2016 4:40 AM in response to lionneldeparis

Hi lionnel,


Your merged cells as column headers are probably complicating things. Numbers does not do well with merged cells.


If your meters were labeled Global1, Meter1, Global2, Meter2 these would be easy to search for. You might need to do Meter1.1, Meter1.2 if multiple meters in a room.


I like using INDEX MATCH for my lookup needs. If you have specific questions aabout applying this to your actual report table, just give more specifics as to waht you need.

User uploaded file


quinn

May 11, 2016 3:12 AM in response to SGIII

Hello SG,


As promised I'm back to you.

Problem solved. Thanks.


The solution you suggest works fine after a while: not really easy to understand the INDEX way of working.

Sorry i can't check the green box since it does not appear anymore. But your answer solved my problem.


I use this INDEX rule quite often now, but still kind of randomly, even after browsing through the user guide. It is quite tricky sometime.


Few more questions...

Here is something I don't understand though I tried it different ways:


Row 11 Table 2 is the one where I use INDEX;

The collection i reference to is in blue in Table 1. In this collection, only impair columns (red numbers) are concerned.

Inside INDEX i locked the first and last cell of the blue collection (row and column)

The result in Table 2::B11 is: red triangle. It says: "the formula includes a number that is not into the range of valid values"

But, when i highlight idependently the arguments inside INDEX, the result is:

-13 for the collection item

-6 for the Row (LIGNE in french)

-2 for the Column


It is a bit confusing.

It all acts as if it would extract the value from Table 2::B11, where there is nothing except the calculation I'm trying to do (whatever is my cell collection). So, Error of course.



User uploaded file



2- I've noticed that the SUM in the Footer Row follows or not depending on the way you add rows in the table:

  • adding a row in the midle of previous ones by ⌥ + ↓ or ↑ : the SUM takes it into account
  • adding a row just before footer row by ⌥ + ↓ : SUM does not add this row unless you specify it
  • adding a row by the return key just before the footer row: SUM does add this row.

Am I wrong here, or is this the way Numbers works ?. Because it can be very confusing when you thought SUM was doing the job by the time you were adding rows (unfortunately the wrong method).


3- As for Table 1 and Table 2 in my example, it is supposed to be growing in the future adding more and more rows. Is there a trick I need to know to make sure it works, rather than figuring out one day that the cells taken into account are not the good ones due to shiffting of the collection for example ?


Thank you again.

If you know how to click the "Solve my problem" check box, i'll be more than happy to do so.

Lionnel

May 11, 2016 4:55 AM in response to t quinn

Hello t


Your solution is also convenient to me. And quite elegant.

The thing is i can't find the MATCH function in french.

I've been scrolling through the References Functions: nothing this kind.

Maybe int the Text Function ?


From what i see it looks for a value (meter1) into a collection (pink collection) and the column number.

I assume that meter1 is an easy way of calling headers to be able to incremente when you pull it on the right: meter2, ...metern.


Thank you for your time.

Lionnel.

May 11, 2016 6:47 AM in response to lionneldeparis

Hi lionnel,


MATCH() i.e. EQUIV() returns the position in a collection so it is made for satisfying row-index or column-index in INDEX(). I use the combo nearly everywhere you might use the LOOKUPs. I like it in your situation because it will deliver correct results if you add columns (say when you add meters) without needing to be modified. Glad it helped.


quinn

reading values every 2 column

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.