Calc Question - Referencing Cells

English
,
#1

Hello

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

https://drive.google.com/open?id=1scIN_cCEN5Rvzvt5ULtFfL3UIfcK8eGy

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…

#2

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.

#3

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:

https://drive.google.com/file/d/1oG2o7w4Sc1bxbMvaIWpCdFSJNfTY0b3v/view?usp=sharing

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?

#4

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.

#5

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

#6

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?

#7

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?

#8

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.

#9

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.

#10

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.

#11

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

#12

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

https://drive.google.com/file/d/1GW7XwiF-qpjPX8knkgGC2QuAGlO8OaL4/view?usp=sharing

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.

#13

In other words, with the hotel listed in C5, no VAT was charged. However, VAT was charged for the hotel listed in C6. So I’d like to simply click on E6 and select the ‘VAT’ option from the Validity menu.

The result would be that I6 would now display ‘£91.67’, and J6 would display ‘£18.33’, (based on a 20% VAT rate). If I reverted E6 back to a blank cell, I6 would then revert back to ‘£110’.

I’ve spent half the day trying to do this using the OR Logical Function, but everything ends in an error

#14

Quoting myself: “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.”

#15

Lupp

Apologies once again, I created this Google account recently and now it’s blocking me. The mail.com link below should work without asking for sign-in.

#16

I actually think I am close now to achieving the correct outcome. Sheet 1 shows the document I tried to link in my previous question above. Sheet 2, in ‘G7’ shows a formula I have created that (almost) works. What is broken however is if ‘D7’ shows ‘CAR’, it overrides the entry in ‘E7’.

One working solution might be to name ‘D7’ as ‘CAR - VAT’, however this is not as elegant as simply having E7 override D7, as it would require inputs into both D7 and E7, instead of just E7.

#17

I did not remeber the details of my first contribution and I surely again did not understand everything.
I will attach now a reworked version of your recent example to my answer above.

#18

Thanks Lupp, your help again is much appreciated.

In the UK we need to determine the total input and output, hence the need for the J1 and K1 columns on the spreadsheet. Without this info, it would be impossible to fill in the VAT return – and similarly, the figures exc VAT are needed for the end of year tax return.

I had probably caused confusion with columns L and M, for the sake of the demo I should have deleted these.

#19

Ultimately going back to the spreadsheet I uploaded, I’m trying to write the formula for say G7, that reads like:

=IF($D7="","",IF($D7=G$1,$B7,"") - however IF($D7=G$1 AND E7=“VAT - STANDARD RATE”, B7/1.2,"")

With this approach, the spreadsheet would work exactly as I need it to

#20

You surely studied my second example in return. There you have already (in G7 and in respective cells) on Sheet2 the formulae you need. The adequate lookup table in S1:T4 may be moved to elsewhere, but surely it should be used instead of producing an inflation of nested IF() expressions everywhere in G3:N7.
Please check that example again, and you will see that it’s the adequate way to do such things.

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