Discussion
Hoping someone can help!
I created a quite complex Excel sheet, which was working fine, until someone else got their hands on it. It appears that they have sorted a sheet incorrectly, so a lot of formulae don't match up anymore.
In it's most basic form, a formula will look like:
=inFEED!T618
I know what row number should be used for each, but given we are talking about 700 rows, and up to column AM, I'm not minded to re-type it all. I cannot just correct one row and copy down, as the data isn't in the same order in the original data.
If I had the correct row number in cell AN23 for example, is there a way of getting the above formula to look at it, and intepret the correct column that it should look at? I.E.
=InFEED!T(AN23)
And, so that in every column, it adapts to the correct column?
I created a quite complex Excel sheet, which was working fine, until someone else got their hands on it. It appears that they have sorted a sheet incorrectly, so a lot of formulae don't match up anymore.
In it's most basic form, a formula will look like:
=inFEED!T618
I know what row number should be used for each, but given we are talking about 700 rows, and up to column AM, I'm not minded to re-type it all. I cannot just correct one row and copy down, as the data isn't in the same order in the original data.
If I had the correct row number in cell AN23 for example, is there a way of getting the above formula to look at it, and intepret the correct column that it should look at? I.E.
=InFEED!T(AN23)
And, so that in every column, it adapts to the correct column?
loosemarbles said:
Hoping someone can help!
I created a quite complex Excel sheet, which was working fine, until someone else got their hands on it. It appears that they have sorted a sheet incorrectly, so a lot of formulae don't match up anymore.
In it's most basic form, a formula will look like:
=inFEED!T618
I know what row number should be used for each, but given we are talking about 700 rows, and up to column AM, I'm not minded to re-type it all. I cannot just correct one row and copy down, as the data isn't in the same order in the original data.
If I had the correct row number in cell AN23 for example, is there a way of getting the above formula to look at it, and intepret the correct column that it should look at? I.E.
=InFEED!T(AN23)
And, so that in every column, it adapts to the correct column?
Mail it over - and I'll have a look this afternoon. Drop me a PM. I created a quite complex Excel sheet, which was working fine, until someone else got their hands on it. It appears that they have sorted a sheet incorrectly, so a lot of formulae don't match up anymore.
In it's most basic form, a formula will look like:
=inFEED!T618
I know what row number should be used for each, but given we are talking about 700 rows, and up to column AM, I'm not minded to re-type it all. I cannot just correct one row and copy down, as the data isn't in the same order in the original data.
If I had the correct row number in cell AN23 for example, is there a way of getting the above formula to look at it, and intepret the correct column that it should look at? I.E.
=InFEED!T(AN23)
And, so that in every column, it adapts to the correct column?
Also - have a look at the INDIRECT function.
I would send it on, but that would be frowned upon.
The challenge is that each row probably has 20 columns which are related to the row number on the original sheet. I know the row number, having extracted that, but the issue is getting each cell to use the correct number.
I.E. In Columns A-D, the formula should look like: =inFEED!A2, to =inFEED!D2, but they actually are =inFEED!A106, to =inFEED!D106
The challenge is that each row probably has 20 columns which are related to the row number on the original sheet. I know the row number, having extracted that, but the issue is getting each cell to use the correct number.
I.E. In Columns A-D, the formula should look like: =inFEED!A2, to =inFEED!D2, but they actually are =inFEED!A106, to =inFEED!D106
loosemarbles said:
I would send it on, but that would be frowned upon.
The challenge is that each row probably has 20 columns which are related to the row number on the original sheet. I know the row number, having extracted that, but the issue is getting each cell to use the correct number.
I.E. In Columns A-D, the formula should look like: =inFEED!A2, to =inFEED!D2, but they actually are =inFEED!A106, to =inFEED!D106
EDIT... Simplified answer below.The challenge is that each row probably has 20 columns which are related to the row number on the original sheet. I know the row number, having extracted that, but the issue is getting each cell to use the correct number.
I.E. In Columns A-D, the formula should look like: =inFEED!A2, to =inFEED!D2, but they actually are =inFEED!A106, to =inFEED!D106
In cell G2 ( in my example...) type in =INDIRECT("inFeed!A"&F2)
And the data on your sheet inFEED.
Edited by Troubleatmill on Friday 24th April 14:06
The other aspect of this is to reduce the chances of repetition due to somebody sorting the datasheet and thus messing up the output sheet. Several ways to address this depending on the operating environment, including:
explore protecting the data worksheet so that it can't be sorted
perhaps ensure it can only be opened as 'Read-Only' so any sorted versions are saved as something else
add a message box on opening, suggesting the operator opens the output workbook too
put the two together in the same workbook
etc etc
explore protecting the data worksheet so that it can't be sorted
perhaps ensure it can only be opened as 'Read-Only' so any sorted versions are saved as something else
add a message box on opening, suggesting the operator opens the output workbook too
put the two together in the same workbook
etc etc
loosemarbles said:
I would send it on, but that would be frowned upon.
The challenge is that each row probably has 20 columns which are related to the row number on the original sheet. I know the row number, having extracted that, but the issue is getting each cell to use the correct number.
I.E. In Columns A-D, the formula should look like: =inFEED!A2, to =inFEED!D2, but they actually are =inFEED!A106, to =inFEED!D106
Any chance you can copy the first 20 rows or so & sanitise the data so we can have a play with the problem?The challenge is that each row probably has 20 columns which are related to the row number on the original sheet. I know the row number, having extracted that, but the issue is getting each cell to use the correct number.
I.E. In Columns A-D, the formula should look like: =inFEED!A2, to =inFEED!D2, but they actually are =inFEED!A106, to =inFEED!D106
paul.deitch said:
Use a database instead!
Excel sorted this by adding PowerPivot. OP, load your data into the Data Model, create the relationships between your tables and then create whatever output you want with a Pivot Table connected to the Data Model.
If that's not enough, they also added PowerQuery which will let you turn your data into whatever tables you need to draw whatever Pivot Tables.
It's 2020 now folks, Formulas in Excel are now for emergencies only.
paulrockliffe said:
paul.deitch said:
Use a database instead!
Excel sorted this by adding PowerPivot. OP, load your data into the Data Model, create the relationships between your tables and then create whatever output you want with a Pivot Table connected to the Data Model.
If that's not enough, they also added PowerQuery which will let you turn your data into whatever tables you need to draw whatever Pivot Tables.
It's 2020 now folks, Formulas in Excel are now for emergencies only.
OlonMusky said:
paulrockliffe said:
paul.deitch said:
Use a database instead!
Excel sorted this by adding PowerPivot. OP, load your data into the Data Model, create the relationships between your tables and then create whatever output you want with a Pivot Table connected to the Data Model.
If that's not enough, they also added PowerQuery which will let you turn your data into whatever tables you need to draw whatever Pivot Tables.
It's 2020 now folks, Formulas in Excel are now for emergencies only.
The OP has some data in a table and is creating another table that also contains some data and there is a relationship between the two. If he treats the two tables as source data and then creates his combined table as a Pivot off a proper relational structure he won't need formulas at all in places where his users can break them by sorting the table. And sorting the table is the sort of thing that users should be allowed to do unless there's a really good reason for them not to as broadly, if you tell them not to sort the table they'll come back asking for another table that is how they wanted it.
Doing it properly will insulate the data from the users to a significant extent as well as bringing a load of other side benefits around the user experience and flexibility too. (It also lets you work with tables with more than a million rows, gives you access to a better analytical engine in Excel and gives you a fighting chance of doing proper analysis without gumming your spreadsheet up and creating an unsupportable mess)
Excel makes that all pretty trivial, though it doesn't make it very obvious that that's what all that stuff in that ribbon you've never looked at is for. Once you know what you're aiming at, a bit of Google around PowerPivot, PowerQuery and Relationships and it's much easier than messing around with formulas.
I say this as a data analyst that pulls together huge quantities of data in Excel that hasn't written a formula in a table in a couple of years. I think I only use them to create dynamic titles that follow slicer selections.
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff