Ask Your Question

Calc Question - Referencing Cells [closed]

asked 2017-11-07 00:05:12 +0200

Adelie Penguin gravatar image

updated 2020-08-07 22:40:22 +0200

Alex Kemp gravatar image


I would like to know how to create a formula to assist me with the following:

1) Firstly I manually enter data into columns 'A', 'B', and 'C'

2) I then manually type into column 'D' a written value, (eg, 'Income', or 'Petrol')

3) The value shown in either column 'B' or 'C', (depending on which is populated), is then automatically referenced in the appropriate column on the right. In other words, if I type the word 'Income' into 'D3', I will want F3' to automatically display the contents of 'B3'.

In practise I will have hundreds of rows, and about 20 different columns, so having the ability to type the name of the column in will save a lot of time, vs manually copying the numbers across.

Whilst I would probably understand the formula needed to achieve this, creating it from scratch is beyond my current capability...

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-08-07 22:41:12.193964

1 Answer

Sort by » oldest newest most voted

answered 2017-11-07 00:42:30 +0200

Lupp gravatar image

updated 2017-11-19 21:34:46 +0200

Best you use a combination of MATCH() with OFFSET() (or INDEX() probably) per row. See this attached demo.

By the way: I will not sign in anywhere to get a file from a questioner. If you want to give me access to some file somewhere, please post a link everybody can use without special measures.

(Editing as announced in the comment below:)
Reworked example attached; clickable here.

(Editing as announced in my recent comment:)
Newly reworked example attached.

edit flag offensive delete link more



Lupp, thank-you for your example file.

Apologies regarding the link - as a new user I was unable to attach the spreadsheet so I tried to share using Google. The link below should now work without asking for any sign-in:

I'd probably be asking too much to ask for a full formula to achieve the objective above, but could you point me in the right direction with the MATCH and OFFSET commands?

Adelie Penguin gravatar imageAdelie Penguin ( 2017-11-07 01:05:15 +0200 )edit

My original answer already had attached a demo targetting the question contained in your comment.
However, having your example at hand the task looks different. Now I would refrain from my first advice and rather suggest to rely on direct comparison.
I will soon attach a reworked version of your example, now containing suggestions and a solution, to my answer above by editing.

Lupp gravatar imageLupp ( 2017-11-07 13:04:04 +0200 )edit

Thank-you for helping Lupp - I very much look forward to seeing what you come up with!

Adelie Penguin gravatar imageAdelie Penguin ( 2017-11-07 21:11:52 +0200 )edit

Did you probably not notice that the examples I talked of are attached already and are accessilble by clicking the links behind the blue boöld "this" (old, first paragraph) and the blue last line?

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

Thanks I have just seen your link. This spreadsheet is very helpful, as are your notes!

I already have a special column for refunds, so no problem there. The bank already show the balance, so this isn’t something I need to manually calculate.

I’ll run some tests on a copy of the real spreadsheet and scale this up. Needless to say you will have saved me a lot of time moving forward - much appreciated.

One question re the drop-down arrow in column E – where can I access and add to the list?

Adelie Penguin gravatar imageAdelie Penguin ( 2017-11-07 22:12:22 +0200 )edit

For the current selection of cells you can apply 'Data' > 'Validity'.
In the given example this was done for a range in columns E. The range was later expanded simply by filling down. The validity wa defined by giving a cell range in which to look-up the valid entries. Unfortunately validity settings, though handled as if they are formatting properties, cannot be bound to a named cell style. If you want to edit a vvalidity, you need to choose the cells first.

Lupp gravatar imageLupp ( 2017-11-07 23:14:28 +0200 )edit

Fortunately 'Validity' accepts calculated ranges. Instead of a constant range like the $Sheet1.$G$1:$Sheet1.$J$1 e.g. you can set the lookup range to (e.g.) OFFSET($G$1;0;0;1;$Z$1) and put into Z1 the number of columns to include.
However 'Validity' and Conditional Formatting have some potential to create promblems for scaling / enhancement.

Lupp gravatar imageLupp ( 2017-11-07 23:23:28 +0200 )edit

Thanks Lupp

I am implementing the formula into the main spreadsheet, and it is working well.

I'm curious as to why the formula reads - =IF($E6="","",IF($E6=G$1,$C6,"")) - would it not be more concise shown as =IF($E6=G$1,$C6,) ?

It's hard for me to Google the meaning of "","", unfortunately.

Adelie Penguin gravatar imageAdelie Penguin ( 2017-11-12 02:27:49 +0200 )edit

Referencing a blank cell returns the numeric value 0 (zero). As I didn't want to get zeros in the target cells if a category already was selected but no amount entered, I tested for the case. You may use your abbreviated formula if you don't appreciate this pettiness.
Two consecutive doublequotes denote for the "empty constant text".

Lupp gravatar imageLupp ( 2017-11-12 03:06:07 +0200 )edit

I understand now, that the "","" is a very neat solution.

I do have one remaining question in order to complete the final spreadsheet, (which I have linked a very simplified example of).

I've adjusted your formula in some instances to assist with VAT calculations. Under the INCOME column this is easy, because all invoices charge VAT to the customer. However in some columns, the need to claim the VAT may vary.

Adelie Penguin gravatar imageAdelie Penguin ( 2017-11-12 17:20:23 +0200 )edit

Question Tools

1 follower


Asked: 2017-11-07 00:05:12 +0200

Seen: 144 times

Last updated: Nov 19 '17