Formulas

Question

I have 3 workbooks in Excel.&nbsp; I have a Master PO Number Spreadsheet and a spreadsheet for each of my project managers.&nbsp; I was originally working on these and had them saved to my flash drive.&nbsp; Now I have them saved on my computer in a folder on our dropbox.&nbsp; Now my formula is not working because it's looking for my flash drive.&nbsp; How do I adjust the formula to look on the dropbox and where to look?&nbsp; Spreadsheets are attach for reference.&nbsp; You'll notice in the individual spreadsheet for Kyle, each column the formula is looking for E:\Marie - that was my flashdrive.&nbsp; How do I change that to the dropbox file?&nbsp; I'm not sure what all to type in.&nbsp; I know where the file is.

hansvogelaar · Answer

marietuttle1973&nbsp;
You'll have to replace the path E:\Marie with the path of your local Dropbox folder. On my computer it looks like
&nbsp;
C:\Users\&lt;username&gt;\Dropbox

marietuttle1973 · Answer

This is the path directly to the file. When I replace it with this, I get an error
Path to location of files
C:\Users\Maura\J&M Commercial Dropbox\Sales and Bid Documents\TEST\

Error:
Excel found a problem with one or more formula references in this worksheet.

hansvogelaar · Answer

marietuttle1973&nbsp;
Can you post an example of a complete formula?

marietuttle1973 · Answer

=IFERROR(INDEX('C:\Users\Maura\J&amp;M Commercial Dropbox\Sales and Bid Documents\TEST\PO Number System.xlsx]PO'!D$2:D$50, SMALL(IF('C:\Users\Maura\J&amp;M Commercial Dropbox\Sales and Bid Documents\TEST\[PO Number System.xlsx]PO'!B$2:B$50="Marc", ROW('C:\Users\Maura\J&amp;M Commercial Dropbox\Sales and Bid Documents\TEST\[PO Number System.xlsx]PO'!A$2:A$50)-ROW('C:\Users\Maura\J&amp;M Commercial Dropbox\Sales and Bid Documents\TEST\[PO Number System.xlsx]PO'!D$2)+1), ROWS($D$2:$D2))), "")

hansvogelaar · Answer

marietuttle1973&nbsp;
You missed the [ before the first occurrence of the workbook name:
=IFERROR(INDEX('C:\Users\Maura\J&amp;M Commercial Dropbox\Sales and Bid Documents\TEST\[PO Number System.xlsx]PO'!D$2:D$50, SMALL(IF('C:\Users\Maura\J&amp;M Commercial Dropbox\Sales and Bid Documents\TEST\[PO Number System.xlsx]PO'!B$2:B$50="Marc", ROW('C:\Users\Maura\J&amp;M Commercial Dropbox\Sales and Bid Documents\TEST\[PO Number System.xlsx]PO'!A$2:A$50)-ROW('C:\Users\Maura\J&amp;M Commercial Dropbox\Sales and Bid Documents\TEST\[PO Number System.xlsx]PO'!D$2)+1), ROWS($D$2:$D2))), "")
&nbsp;

Forum Discussion

Formulas

Share

Resources