Sharing Portions of a spreadsheet

**scottma** · 02-26-2009, 07:32 PM

I have a spreadsheet that needs to have certain fields updated by competing vendors. The spreadsheet is too complex to sort by vendor cut and paste into a new workbook and send to them for updating. I would like to be able to password protect or hide groups of cells. I am open to ideas. Please.

**JBeaucaire** · 02-26-2009, 07:34 PM

Connect the portions of your sheet that has their information to a second workbook using simple = formulas or even INDEX/MATCH formulas. Then give them their own workbook to update.

When they update it and send it back to you, as long as you store it on your computer(s) in the same place, your main workbook will always be able to access this exterior book and keep the values updated.

**scottma** · 02-26-2009, 07:48 PM

Originally Posted by JBeaucaire

Connect the portions of your sheet that has their information to a second workbook using simple = formulas or even INDEX/MATCH formulas. Then give them their own workbook to update.

When they update it and send it back to you, as long as you store it on your computer(s) in the same place, your main workbook will always be able to access this exterior book and keep the values updated.

OK Let me see if I understand. If I place = formula that says a cell in my original spreadhseet equals a cell in the worksheet I give them to update ,it will update the original spreadsheet automatically. I know how to do that within a spreadsheet ie if in cell A2 I placed the formula =C3, A2 would then display the value in C3. How do I reference the cell on want to pull the value from in the separate worksheet I give them to update? Hope I am making sense. I appreciate your help.

**mudraker** · 02-27-2009, 05:45 AM

scottma

Edit - Thread Title hass been changed

Please take a couple of minutes and read ALL the Forum Rules

Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.

You duplicate thread has been closed

**JBeaucaire** · 02-27-2009, 11:39 AM

Referencing other cells

Cell to Cell:

=A1

Cell to Sheet:
=Sheet1!A1

Cell to workbook:
=[VendorSheet.xls]Sheet1!A1

If you want to mimic the exact layout of the VendorSheet in your sheet somewhere, this is a way to do it.

Not having seen what you're talking about letting them update, it doesn't have to be so mundane. You could give them a list with Product Number, Product Names and Product prices to keep updated. Then if you already have the Product Numbers in your sheet, you can use INDEX(MATCH() formulas to pull over the product desc and prices regardless of where they are in the vendor sheet.

You sheet:

Please Login or Register  to view this content.

Give him his sheet with product codes in the first column. Now, he can sort his sheet any way that makes sense to him, and you can use this formula B2 of your sheet to bring over the Product Desc for the first code found in A2:

=INDEX([VendorList.xls]Sheet1!$B$2:$B$1000,MATCH(A2,[VendorList.xls]Sheet1!$A$2:$A$1000,0)

And the price in C2 is brought over the same way:
=INDEX([VendorList.xls]Sheet1!$C$2:$C$1000,MATCH(A2,[VendorList.xls]Sheet1!$A$2:$A$1000,0)

No matter where AA-12345 is found in the vendorlist.xls column A, the MATCH will find it, and the INDEX will bring over the associated indexed column value.