Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Hi! I work with an excel document with 7 different sheets. its kinda a budget, and I am stuck. I have copied all the information from one sheet to another with the formula ='Total investment'!$A$2:$N$133, and they are moving perfectly together as I want them to do. However, in two of the columns - which are different in all the sheets because its different countries - are not moving when I add a new column in the total investment sheet. If I add one new line - the line shows up in the other sheets but the numbers are not following. So if it says "1" in D5, it should move to D6 when I add a line...
Solved by V. A. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 15/08/2018 - 01:13
hello
User 15/08/2018 - 01:13
hi!
Excelchat Expert 15/08/2018 - 01:14
Can you please share the file you are working on?
User 15/08/2018 - 01:16
Do I have to ?
User 15/08/2018 - 01:17
Its a bugdet for a company an I am not allowed to share it..
User 15/08/2018 - 01:17
I guess it would make it easier but yeh..
Excelchat Expert 15/08/2018 - 01:17
You can send me a demo file, as it would be better to have a look into the problme
Excelchat Expert 15/08/2018 - 01:17
*problem
User 15/08/2018 - 01:18
I dont know what a demo file is ?
Excelchat Expert 15/08/2018 - 01:19
I mean the part of the file you are facing problem with
User 15/08/2018 - 01:20
ah okey, I just deleted some of the columns it should be fine now
Excelchat Expert 15/08/2018 - 01:21
Sure, thanks, attach it please
User 15/08/2018 - 01:22
How do I do that
Excelchat Expert 15/08/2018 - 01:22
There is a clip symbol on your side
Excelchat Expert 15/08/2018 - 01:22
Press that to attach the file
User 15/08/2018 - 01:23
It doesnt allow me to add any excel files
User 15/08/2018 - 01:24
found it !
Excelchat Expert 15/08/2018 - 01:24
Great!
User 15/08/2018 - 01:25
Now it says - Attachment has an invalid file extension
User 15/08/2018 - 01:25
so I cannot upload it
Excelchat Expert 15/08/2018 - 01:25
does your file has extension xlsm?
Excelchat Expert 15/08/2018 - 01:26
or xlsx?
User 15/08/2018 - 01:26
I dont know ? How do I find out
Excelchat Expert 15/08/2018 - 01:26
https://docs.google.com/spreadsheets/d/1DM6Agm0PIQAEHvScvZCyBNg4GK1gfzJ1530sIL3TyPE/edit?usp=sharing
Excelchat Expert 15/08/2018 - 01:27
copy-paste your data in the link
Excelchat Expert 15/08/2018 - 01:29
you there?
User 15/08/2018 - 01:29
yeah !
User 15/08/2018 - 01:29
Im copying it in right now
Excelchat Expert 15/08/2018 - 01:29
Sure thanks
Excelchat Expert 15/08/2018 - 01:31
now tell me
User 15/08/2018 - 01:31
So I need to get the numbers under quantity needed to move with the rest when I add another line in sheet 7
User 15/08/2018 - 01:32
Its formulas in most of the cells, exept that column since its different for each country - but It still has to move with the rest if I add a new line
Excelchat Expert 15/08/2018 - 01:34
So the line will be added on top?
User 15/08/2018 - 01:34
or bottom - it moves all the time
User 15/08/2018 - 01:36
so in sheet number 6 in B8 which is the number 3 - always has to follow the brand name which is ESKO - even though I add a line in sheet 7
User 15/08/2018 - 01:37
But I dont think any of my formulas followed when I copied it
User 15/08/2018 - 01:37
='Total investment'!$A$2:$K$130
User 15/08/2018 - 01:38
This is the formula I have used to copy all my information - so it is always updated whateve rI do in the sheet total investment .. but I removed the forumla from quantity needed since its going to be different from each country
User 15/08/2018 - 01:39
and when I add a new line in total investment - everything follows - exept the line Quantity needed.
User 15/08/2018 - 01:40
I just need a method where I can connect a cell to another so it always follow it or something
Excelchat Expert 15/08/2018 - 01:40
Sheet 7 here is the TotalInvestment...sheet?
User 15/08/2018 - 01:40
yes
Excelchat Expert 15/08/2018 - 01:41
In that case adding new data at top of Sheet 7 might create problem
Excelchat Expert 15/08/2018 - 01:41
Otherwise it should not
Excelchat Expert 15/08/2018 - 01:42
You are facing the problem whenever you add data at top of Sheet 7?
User 15/08/2018 - 01:43
or wherever in sheet 7 - the number under quatity needed does not move
Excelchat Expert 15/08/2018 - 01:44
Does other sheets will replicate sheet 7 as it is?
User 15/08/2018 - 01:44
Is ther any way I can connect a cell to another so it always follows this one including the number inside of the cell ?
User 15/08/2018 - 01:47
do you understand ?
Excelchat Expert 15/08/2018 - 01:48
Yes, let me see
Excelchat Expert 15/08/2018 - 01:49
So as you said, your Sheet 6 D8 should always say 3 and A8 say ESKO?
User 15/08/2018 - 01:50
Well yes, unless I add a line it has to move to D9 and A9 - however, right now, its only A8 tha is moving - not D8
Excelchat Expert 15/08/2018 - 01:53
CHECK THIS
[Uploaded an Excel file]
Excelchat Expert 15/08/2018 - 01:54
Check Sheet 6 column A
Excelchat Expert 15/08/2018 - 01:55
Now try adding a column in Sheet 7 and check column A of Sheet 6
User 15/08/2018 - 01:57
Well yes, it doesnt move - but the line has to come in to sheet 6 as well - so it has to move
User 15/08/2018 - 01:57
not only in sheet 7
Excelchat Expert 15/08/2018 - 01:58
let me see
Excelchat Expert 15/08/2018 - 02:03
now check
[Uploaded an Excel file]
Excelchat Expert 15/08/2018 - 02:04
now ok?
Excelchat Expert 15/08/2018 - 02:06
You there?
User 15/08/2018 - 02:07
yeah
User 15/08/2018 - 02:07
stil only column A that moves
Excelchat Expert 15/08/2018 - 02:08
Check Sheet 6....add new lines in Sheet 7 and check...I have not touched column B as you said that will vary,except B column A through E of Sheet 6 now follows Sheet 7
[Uploaded an Excel file]
User 15/08/2018 - 02:10
I know - that my problem though. I got all the lines to follow - except B - which also has to follow, but the value is going to change so it cannot be an formula in it so it just has to be attached to another cell or something so that moves to
Excelchat Expert 15/08/2018 - 02:11
So apart from B you have other problems gone? I will focus on B then
User 15/08/2018 - 02:12
Yes, that is and was my only problem.
Excelchat Expert 15/08/2018 - 02:13
You have 6 countries? You will add different data for B for each countries and they need t have those data as per their name?
User 15/08/2018 - 02:13
yes

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.