Cross referencing multiple forms using a formula?

tgnockles

Hello there,

I have a destination sheet, where I need to reference 3 types of data from 2 other support sheets that I have made. Those are (cost of airfare, date travel was booked, and cost on non-airfare). One of the support sheets has a form that will record airfare cost, and travel date booked from recipients. The other sheet will record cost on non-airfare from its recipients. I was wondering if someone could please help me figure out a cross-reference formula for each of the 3 columns that I can reference back to my main destination sheet? As future information is entered and auto populated on those support sheets, then it will be referenced/pulled into the corresponding/matching row and column from where the other matching information already is. Is this possible? I cannot figure out if it supposed to be a VLOOKUP, INDEX, COLLECT, or some other type of formula that would bring that information back to my destination sheet. Thanks so much for your help on this!!

Paul Newcome

Are you able to provide screenshots for reference?

tgnockles

@Paul Newcome Sure thing. Please see below:

Support sheet 1: This contains a form that will auto populate the following columns below. The only columns that I want referenced are the ones shown below (cost of airfare, and the date airfare was booked). I am hoping to have some sort of cross-reference column formula that will reference these 2 columns into the master columns on my destination sheet.

Screenshot for the 3rd piece of data from other support sheet. This is that 3rd piece of data that I am also hoping someone could come up with a column cross-reference sheet formula that will take the auto populated data from this column and reference it to my destination sheet.

I would take a screenshot of the destination sheet, but it just has 3 columns of blank data that are the exact same title names as the others from the screenshots above. The only difference is that the destination sheet would start populating the cross-reference data starting at row 265 vs the top row of the sheet.

Paul Newcome

My apologies. I meant to ask for screenshots with sample data.

tgnockles

@Paul Newcome ,

Whoops, sorry about that.

Here is support sheet #1 with random data I just threw in there:

Support Sheet 2: Here is support sheet 2 with just the non-airfare cost corresponding to the other ones:

I am hoping to get a column cross-reference formula that can reference both columns from support sheet 1, and the single column from support sheet 2, and input them into my destination master sheet with the same heading/matching column names, so that any future data points that are input from the forms attached to support sheets 1 & 2 will just auto populate into their respective matching columns on my master sheet. They will need to be input starting around row 250 though. Do you know if this is possible? Thanks!

tgnockles

@Paul Newcome Hey Paul, any updates on this? I have been doing a little more research on my end but haven't found anything that works yet. I think that I am just going to create an automation reminder on both support Smartsheet's for when any new added is added (via a form response submission), and then manually enter that data into the matching row of the destination sheet.