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.

2
  • You can do a Ctrl+C, Paste Special, Values, and then you will have an immutable list of your data Jul 4, 2017 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? Jul 4, 2017 at 15:21

1 Answer 1

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .