We will be migrating from Ask to Discourse on the first week of August, read the details here

# Calc Question - Referencing Cells [closed]

Hello

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

Sort by » oldest newest most voted

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.

more

1

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?

( 2017-11-07 01:05:15 +0200 )edit

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.

( 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!

( 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?

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

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?

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

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

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

( 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".

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