2 way cell linking

[dsmart88]

Hi all,

I am trying to create a spreadsheet that has several sheets and a master sheet. I need to be able to link the data from the master sheet so users can edit specific cells in the corresponding sheet but then also pulls back the data they have entered into the master.

For simplicity, let’s say I have the following sheets;

Master
Apples
Oranges
Bananas

Data from Master A1:D5 is to be copied but only to the sheet I add in A1 i.e. apples in A1 will only link to apples sheet, A2 bananas to bananas sheet.

If I was also to change the sheet name in the cell it would link to the corresponding sheet.

I’ve been trying to find a way of doing this without VBA but I don’t think it’s possible as what I’ve done isn’t working right so I guess VBA would be my only option.

Thanks all for reading and hopefully someone can guide me in the right direction.

 

[etaf]

indirect() may help , using the cell as a sheet name

Data from Master A1:D5 is to be copied but only to the sheet I add in A1 i.e. apples in A1 will only link to apples sheet, A2 bananas to bananas sheet.

not sure how you are copying ?



Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.

 

[dsmart88]

Not sure if I have done the sample correct.

Basically...

Master - I add data in B-D. Q is pulled from the corresponding sheet based on B cell
Other sheets - B-D is pulling from master. Q is what the user can enter

However, using the formula's I have, it partially works where the data will go to the relevant sheet. However column Q doesn't change. You can see this on the Oranges sheet, Q8.

Hope that makes more sense.

dsmart88.xlsx
	B	C	D													Q
6	SHEET	PRODUCT	PRICE													NOTES
7	Apples	Product Jan Res.	£9.99													Reserved Jan
8	Bananas	Product Feb Sold	£9.99													0
9	Bananas	Product Feb Res.	£9.99													0
10	Oranges	Product Jan Sold	£9.99													Sold Feb
11																#REF!
12																#REF!
13																#REF!
14																#REF!
Master

Cell Formulas
Range	Formula
Q7	Q7	=VLOOKUP(H7,INDIRECT("'"&B7&"'!H7:U33"),10,FALSE)
Q8:Q14	Q8	=VLOOKUP(H8,INDIRECT("'"&B8&"'!H7:U999"),10,FALSE)

dsmart88.xlsx
	B	C	D													Q
6	SHEET	PRODUCT	PRICE													NOTES
7	Apples	Product Jan Res.	9.99													Reserved Jan
8
9
10
11
12
13
14
Apples

Cell Formulas
Range	Formula
B7:P7	B7	=FILTER(Master!B:P,Master!B:B="Apples")
Dynamic array formulas.

dsmart88.xlsx
	B	C	D													Q
6	SHEET	PRODUCT	PRICE													NOTES
7	Oranges	Product Jan Sold	9.99													Sold Feb
8																Resevered Feb
Oranges

Cell Formulas
Range	Formula
B7:P7	B7	=FILTER(Master!B:P,Master!B:B="Oranges")
Dynamic array formulas.

dsmart88.xlsx
	B	C	D													Q
6	SHEET	PRODUCT	PRICE													NOTES
7	Bananas	Product Feb Sold	9.99
8	Bananas	Product Feb Res.	9.99
Bananas

Cell Formulas
Range	Formula
B7:P8	B7	=FILTER(Master!B:P,Master!B:B="Bananas")
Dynamic array formulas.