Indirect Function to reference another page

[crburke92]

I'm having issues with the last part of my formula. I have two sheets, a results page (where the formula is written) and the Database page where all results will be stored. When I write this formula out on the database page, it works exactly how I want it to, just need it to work on my results page with reference to the Database sheet

{=INDEX(INDIRECT("'Database'!$F$2"),SMALL(IF(Database!$A$3:$A$999=$G$5,ROW(Database!$A$3:$A$999)-ROW(Database!$A$3)+1),1))}

The return should be INDEX(DataBase!$F$3:$F$999,1) but I cant seem to get (INDIRECT("'Database'!$F$2") to work properly and I feel dumb

Row 2 on the database page is my helper column which returns the array I am searching, which works fine:

​

=SUBSTITUTE(ADDRESS(1,MATCH(F1,1:1,0),4),1,"")&"3"&":"&SUBSTITUTE(ADDRESS(1,MATCH(F1,1:1,0),4),1,"")&"999"

Thanks in advance!

 

[steve the fish]

You either need to include the sheet name of the range within your indirect or you need it in the F2 cell of database or you will be returning an index based on the sheet the formula is housed in not the database sheet.

 

[crburke92]

I understand that aspect. If I remove all " and ' it returns what I have in that cell on my database page, being my array, but is now referencing my results page for the INDEX function.

My issue is getting the returned value to have Database! in front of it. If tried adding it to the front of my array formula but keep getting REF errors and invalid formula errors, same with trying to add it in my INDEX/INDIRECT formula. I had an issue with this last week on another formula, got help and got it working. Tried to reference this the same way but it doesn't work....

Things like this are what I've been trying for hours. nothing seems to work..
=CONCATENATE('Database'!, SUBSTITUTE(ADDRESS(1,MATCH(AD1,1:1,0),4),1,"")&"3"&":"&SUBSTITUTE(ADDRESS(1,MATCH(AD1,1:1,0),4),1,"")&"999")

 

[steve the fish]

You put it in inverted commas "'Database'!"

 

[steve the fish]

Im struggling to see the point of the formula though? That always returns a fixed range? So why not just use that range in the formula?

 

[crburke92]

My initial project was to have a way to take data from CSV files we receive. the top row of every file is all the data headers, and the first row is the unit serial number. The issue I ran in to was depending on what data they receive on our end, or the tests they performed, the header locations would change by a few columns from file to file. This was my solution to select the range based on a return lookup of the header..

=SUBSTITUTE(ADDRESS(1,MATCH("labreportnum",1:1,0),4),1,"")&"3"&":"&SUBSTITUTE(ADDRESS(1,MATCH("labreportnum",1:1,0),4),1,"")&"9999"

This started from me trying create something that would extract the data from each individual file we were sent and transpose it to a table, which I was able to do using VLOOKUP and HLOOKUP

=VLOOKUP($F$13,DataDump!$A$3:$CR$52,HLOOKUP("labreportnum",DataDump!$B$1:$CV$2,2,),)

Where F13 was on my results page, and you'd just type in the serial number and boom, about 30 data points based on each header (labreportnum, container_id, etc etc....) would return

My new project has been trying to create a database of all samples over time. Type in the serial number and it returns every year of that units results. You just would have to copy the csv file to the top of the database page so that INDEX,1 returns the first and most recent value of that serial....INDEX,2 returns the previous years, 3,4,5,6,7..... and so on.

An issue I'm realizing now is that I'll somehow have to always make sure columns align or the data it returns won't be right. Maybe by making a custom sort list, but then I'd still have an issue when the header in my list doesn't exist. There may be a way to copy the header line from each group of samples and create an array of just those few lines?

My other issue is inserting rows on top changes the cell reference from lets say F3 to F9...Looks like INDIRECT() can solve that issue pretty quick however?

To give you a better idea of what these sheets are like, the top row is always the data headers. Row 2 contains all of Sample 1's data, Row 3 contains all of Sample 2's data and so on. The columns generally extend out to CC-CV area.

It's been a fun learning experience figuring out how these different functions work with eachother haha..

I also meant to note that we should be doing about 500-1000 of these/year, with hopes of that going up.

Thanks!
​

 

[steve the fish]

Ok instead of the indirect you can use this:

=INDEX(Database!$A$3:$AZ$999,,MATCH("labreportnum",Database!$A$1:$AZ$1,0))

You may have to change the columns (i chose AZ here not knowing your sheet).

 

[crburke92]

Well lookie there! Got it to work. And always, thanks a million