VLOOKUP/ INDIRECT Date range isssue

[reddevilanalyst]

Hi All,

I have been working on a small invoice report. I have multiple sheets of invoices and to ease them up - I wanted to autofill whatever I type in the sheets to the INDEX page. I used the formula =VLOOKUP($B$2,INDIRECT("'"&$A3&"'!$A$15:$C$15"),3,FALSE) function to match cells from other sheets to the INDEX page. So basically I have created different headings for Company, created by, date and amount. The company and created list works fine with the formula but when I try the date range and amount - it shows as a number. I tried using another VLOOKUP(INDIRECT ddmmyyyy method too - but it is not working.

The dates are filled in the sheets between the range G3:H3 and similarly for the amount it is J43:L43. Please suggest any help/ any different formulas which can help me out. I have nearly 50 sheets and it will be easy to auto-fill the INDEX page when I type in the individual sheets

 

[Bosquedeguate]

do you have the target worksheet's cells formatted as a date? (like mm/dd/yyyy)? you might just be seeing the serial number that represents a date.

 

[reddevilanalyst]

do you have the target worksheet's cells formatted as a date? (like mm/dd/yyyy)? you might just be seeing the serial number that represents a date.

Thanks for that - I figured it out now. I have trouble doing the same for amount - I can apply the formula when I do it adjacent column-wise (yellow highlight)- =VLOOKUP($E$2,INDIRECT("'"&$A3&"'!$J$60:$K$60"),2,FALSE) and it gives out the correct answer but for adjacent row-wise (highlighted in green) - INDIRECT function does not come up (attached). Any idea, what changes need to be made to the formula?

 

[Bosquedeguate]

What kind of data do you have in E2? String or numeric value?

 

[reddevilanalyst]

E2 - is the index page - header for Total Amount as you can see.

 

[Bosquedeguate]

if the data you need is coming from the same table, then you want the Total Amount column
I think you want to use the same formula, just change the 2 (third) argument, to a 5. Right?

How do the amounts differ (Yellow Total and Green Total)? Where should these values be coming from?

 

[reddevilanalyst]

I tried but doesn't help. I just want the total amount to be auto-filled into the index page when I type in each sheets.

Here you can see when I use the same formula for Company name, and date it works as it is adjacent columns. for the Total, the amount 200 is beneath it - that never comes. If I try =VLOOKUP($E$2,INDIRECT("'"&$A3&"'!$J$60:$K$60"),1,FALSE) - it shows Total in E3 but when I try 2,False - it gives me error.

 

[Bosquedeguate]

I am missing something ...
the value of $E$2 in your VLOOKUP function is a Tab No. value to do a lookup in the INDEX table? Right?
Are you trying to pull a value from your index table or put a value into the table in Column E3 (for example).

If you are getting data from the table (again for the example of Tab No.:4186) the VLOOKUP function for getting the total would be:
=VLOOKUP(VLOOKUP($E$2,INDIRECT("'"&$A3&"'!$J$60:$K$60"),1,FALSE) like you explained and said it works

What I am not understanding is what value do you want for the Total (Cell L59) in your right-hand screenshot.
Do you want to pull this value from somewhere? (If so, from where - table, row, and column). Or
Do you want to put this value into another cell? (If so, what is going to receive it - sheet, row, column).

I am working blind by not having a full understanding of your data.

 

[reddevilanalyst]

Hi, Sorry for the confusion. Just like I did for company name, created by, date - I want to do the same. Basically, pull the data from L59 i.e. £200 and fill that in E3 under Total. That is all I need. L59 cell will be constant over every sheet.

For eg: The company name for 4186 tab - I used this formula: =VLOOKUP($B$2,INDIRECT("'"&$A3&"'!$A$15:$C$15"),3,FALSE) and it pulled data from 4186 - from cells A15:C15 where A15 - company name and C15: Swift. Similarly I want it to try for Total.

 

[Bosquedeguate]

Hi, Sorry for the confusion. Just like I did for company name, created by, date - I want to do the same. Basically, pull the data from L59 i.e. £200 and fill that in E3 under Total. That is all I need. L59 cell will be constant over every sheet.

For eg: The company name for 4186 tab - I used this formula: =VLOOKUP($B$2,INDIRECT("'"&$A3&"'!$A$15:$C$15"),3,FALSE) and it pulled data from 4186 - from cells A15:C15 where A15 - company name and C15: Swift. Similarly I want it to try for Total.

View attachment 96956

Right, so your totals are in Column E - the 5th Column, Right?
Using your example I would expect the total you want to be in Row 15, Column E
If that is true, then your formula would be the same as above, but change the 3rd argument from 3 to 5 (i.e. Column 5 the column that has your total value)

=VLOOKUP($B$2,INDIRECT("'"&$A3&"'!$A$15:$C$15"),5,FALSE)

Where is the total that you want to put onto another sheet?