Linking sheets - Basic Requirements

your desktop file should update if you are on the n/w
my concern is the emailed file. if it is not saved in exactly the same folder/path as your link, the link will not work with the "new/emailed" file, and will instead pull from the "old" file
if you have a concern that the auto populate file may have formulas messed with/over-written, then maybe consider protecting them

if the links to the other file/s contain the full path to where they are, it should not make a difference where the Master file is saved. and even tho the Master may not be saved on the N/W, if that PC is linked to the N/W, the linkls should still work

can you talk/walk me thru (in detail) what you do, to help me better understand your process?

here would be good, that way maybe others could help and/or learn as we go along

just so i understand you, which way does the linking go? is the Master fed by the file of your desktop, ie the file on your D/T has info that the Master uses, or is it the other way round?

Master is shared and updated via Email, so will the file sitting on my system get updated when someone else updates it on theirs?
How do I ensure my requirement is met flawlessly, given the Master file name changes daily?
i think you said somewhere that the master file gets sent to you with a different name every time? if this is the case, your desk top file will have no way of knowing where to find its data because the links will point to a different file name.
2 ways around that - change the emailed file name when you download it to the same name as what your links point to (easiest) or change the links in your desk top file every time you get a new update, altho the linking formula can be modified such that you enter the new file name in a specified cell in the desktop file and formula adjusts accordingly (more complex, but do-able)

not sure how you did your linking (vlookup? =cell?), but if you intend to add columsn, it might be better to use index/match to find the column heading/name instead, that way, it shouldnt matter if you add columns

This is the formula I'm using to link sheets: =SUMIF('[Product File.xlsx]Sheet1'!$B:$B,$A5,'[Product File.xlsx]Sheet1'!L:L).

Say if the column is moved to D instead of B and the second one to N instead of L. What modification would be required to ensure that the formula doesn't get affected?

Where / How should exactly should Index/Match be added to the existing linking formula above? Please advise!

And a BIG thank you for your immense help and patience with this request of mine!

You're a genius by all means!

Cheers!!!

happy to help

note that sumif() to a linked file only works if the linked file is open too.
this formula should eliminate the need to change your formula if you add/delete extra columns. i dont think you uploaded a sample file, so i made it based on some of my own files. change the file names, ranges and headings (in the formula) to suite

=SUMIF([desktop.xlsx]sheet1!B$3:B$12,A14,OFFSET([desktop.xlsx]sheet1!$B$3,0,MATCH("services",[desktop.xlsx]sheet1!$B$3:$F$3,0)-1))

(its not as bad as it looks) let me know how you make out

hehe thanks for the kind words, but many on here are FAR more advanced than i am. as far as the offset() is concerned, it is "moving" the column reference 1 way or the other, dependant on which column number (not letter) "services" is found in. so this allows you to add/delete columns, and the formula will adjust accordingly. and yes, you are correct, "services" is the column header

as a further refinement, IF your file name will be changing from time to time, that formula can be further modified to allow for the file name to be manually added to a specified cell, and the formula will use that (instead of "desktop.xlsx". but try that 1 1st, and if needed, we can take the next step

can you upload 2 sample work books for me, that way i can see your data and what you are trying to do where?

Here are the sheets attached.

Project Report is the Master file from which the data is to be linked on to the Project Summary. I've applied the formulas I'm currently using.

I need to ensure that If any columns are added my formula doesn't get affected, because the columns and rows are bound to increase by each passing day. I'm not sure how to use the Match and OffSet function along with my current formual in column C.

Secondly, may be I'm asking this again, but since you have the data files now, is it possible to auto add rows and their corresponding data on the Project Summary sheet everytime such an action is taken on the Project Report? I would mainly like the Project name updated on that...

Please advise.

ok try the attached, i have modified the formulas somewhat, check to make sure this is what you want?

LOL im really sorry, i uploaded the wrong file for you.

try this 1 instead. i didnt make anyt changes to your data file, just your summary file.