Forum Discussion

Mr_Raj_C's avatar
Mr_Raj_C
Brass Contributor
Apr 20, 2023
Solved

Help with a process / formula(s) to look up variable values and return the data

Dear Excel Community,   I was wondering if you could advise or give some guidance with a problem i have and what the best solution would be ?   Please see attached Workbook. In the Data sheet, we...
excel
Mr_Raj_C's avatar
Mr_Raj_C
Brass Contributor
Jun 21, 2023

durendal Sorry to come back to you so late on this. I've been on leave !.

 

I've managed to overcome the formatting issue but i now have a Spill error :(. When i copy the formula from the spreadsheet you attached, the formula points to that file rather than the workbook i'm using. Even when i try and remove the previous file name, it doesn't work and gives me the spill error. 

 

Attached is the spreadsheet i want to use and also shows the "Spill" error. I would be grateful if you could take a final look for me and help sort it out 😞

 

Thanks Raj

durendal's avatar
durendal
Brass Contributor
Jun 21, 2023
The formula in the "Requester" columns is a spill formula, it needs to be entered only once. If any data is entered under it, where it should spread, it will return a spill error. To correct it, enter the formula only once, in the E8 cell for exemple, and delete the formulas under it
  • durendal's avatar
    durendal
    Brass Contributor
    Jun 22, 2023

    Mr_Raj_C 

    Hello again. The formulas are the same in the cells F9:F19 than in F8. Drag down again from F8 and it will be fine. Check the formulas differencies before.

    The one that works includes:

    VSTACK(April:December!$I$2:$I$100000)

    and the one that doesn't is

    VSTACK('C:\Users':December!$I$2:$I$100000)

     

    So something happend with the tabs names

     

    Bye

  • Mr_Raj_C's avatar
    Mr_Raj_C
    Brass Contributor
    Jun 21, 2023

    durendal Thank you, I've sorted that out now.

     

    There is one more thing which is not working as expected which is the total number of authorised requests in cell F9. It seems to have counted for the first person but not for the other 2 underneath. I've tried to look at the formula logically and can't figure out why it's not working.

     

    Apologies for bugging you like this. I promise this will be the last question ! 

     

    Thanking you for your help so far and in advance.

Resources