0

I have an external database populating a spreadsheet in my workbook. In a separate worksheet in the same workbook, I use formulas to filter out a smaller subset of the query. The issue is any time I refresh the data, the cell references within the formulas for the smaller subset change. This happens any time a new element is added in the database.

I've tried using the INDEX and INDIRECT functions, but it won't allow me to use the $ symbol to prevent me from needing to individually change the formula 50,000 times (It is a very large database).

For further clarification on the problem:

My second spreadsheet is mainly entire columns of formulas like

  • =FIRSTSPREADSHEET!$A2
  • If (ISBLANK(DATABASE!$B2),DATABASE!$C2,DATABASE!$D2)

Nothing complicated just that sort of stuff. But any time an element is added to the original database, and the data is refreshed in excel, the cell references are changed in the new sheet around the point where the new entries should be.

  • You can do a Ctrl+C, Paste Special, Values, and then you will have an immutable list of your data – fernando.reyes Jul 4 '17 at 13:32
  • The data is subject to change, wouldn't that just leave me with a one-time copy paste of the values at that current time? – robert chen Jul 4 '17 at 15:21
0

You can do it with INDIRECT, however to get auto-increment, you need to use also ROW or COLUMN.

E.g. you can use this formula instead of your first example:
=INDIRECT("FirstSpreadSheet!A"&ROW())

enter image description here

  • Thanks, I've been playing with variations of this, I'll check it out thanks. – robert chen Jul 5 '17 at 18:07

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.